ActiveRecord + Relations + CDbCriteria behaviour

hello!

task:

1. i need to select active records. only special fields (for example, only primary keys)

2. i need to apply filter(CDbcriteria) on related active records, but i don’t need related active records data.

In other words, i need to select special fields only for active records that i want to filter by condition for related data.

For example, i have 2 tables. Posts and Users. I need to select only ID of posts from Posts, but for users that where created after some date (creation date for users, not posts!).

The next thing is not working for me:

$criteria = new CDbCriteria;


....setup my criteria to filter needed info, including all needed 'with'(dynamic/static not matter), 'scopes' and etc...


$criteria->select = 'post_id';

$criteria->index = 'post_id';


$rows = Post::model()->findAll($criteria);

As result i want to get array of active records with only post_id attribute. i don’t want to load full data for active records. And that’s not working, i always getting full data for all records, including related data.

For example, i need that SQL to be executed:

ideal:

SELECT `t`.`id` AS `t0_c0` FROM `post` `t` LEFT JOIN `user` `manager` ON (`t`.`uid`=`manager`.`id`) WHERE ((manager.tags_manager LIKE '%#tag#%') AND ((t.created>=:start) AND (t.created<=:stop))) ORDER BY t.created DESC LIMIT 50

fine too:

SELECT `t`.`id` AS `t0_c0`, `manager`.`id` AS `t1_c0` FROM `post` `t` LEFT JOIN `user` `manager` ON (`t`.`uid`=`manager`.`id`) WHERE ((manager.tags_manager LIKE '%#tag#%') AND ((t.created>=:start) AND (t.created<=:stop))) ORDER BY t.created DESC LIMIT 50

So is it bug(getting full data, but not specified fields via CDbCriteria) or everything is ok?

Yes, i could use DAO, but CDbCriteria is tooo deep integrated into application and there is no way to get SQL command from criteria with all these ‘scopes’, ‘with’ and etc.

so no reply from core team?

i repeat my question: is it correct that ActiveRecord ignores ‘select’ property at CDbCriteria and loads FULL data for AR including FULL data for related records?!

If it’s correct behaviour, then fix ur documentation.

Need time to check it.

Well, according to documentation it’s not correct. Please add a ticket here http://code.google.com/p/yii/ so we’ll not forget to discuss/fix it. Meanwhile the only solution I see is to use JOIN part of criteria with a value of “LEFT JOIN user manager ON (t.uid=manager.id)”.

to Andrey G.

If you don’t need data for related active records just use ‘with’ with ‘select’=>false. For example:

$criteria->with=array(

    'user'=>array(

        'select'=>false,

        'condition'=>'user.is_active=1',

    ),

);

Thanks, creocoder. I forgot about select => false

Very funny. Thanks for ur ‘help’, but next time try to read information about problem more deeply.

There are TWO cases. FULL info for AR and FULL info for related. Dynamic ‘with’ can fix only FULL info for related, but FULL info(i.e. totally ignorance of specified ‘select’ via criteria) for AR can’t be fixed.

And, btw, i’m still sure that this flow(fix for related info via dynamic ‘with’) must be discussed at least, coz it’s not a clear way. In documentation we have only ‘via select u can set fields to get’ - no any information about case with related info. As for me, i think that criteria must have max priority. But in current flow - nope.

Alexandr, problem is still here. Please read again my start topic. Dynamic ‘with’ fixes only ONE case of TWO.

to Andrey G.

Your problem can not be reproduced. So I would not have been so persistent in your case.

Example:

$criteria=new CDbCriteria;

$criteria->select='id';

Adv::model()->with(array(

	'locality'=>array('select'=>false),

))->findAll($criteria);

Generate SQL:

SELECT `t`.`id` AS `t0_c0` FROM `adv` `t` LEFT OUTER JOIN `locality` `locality` ON (`t`.`locality_id`=`locality`.`id`)

I would have said: ‘sod off’.

Do you want ‘help’ or not??

Do you really think people spend their time here for being ‘funny’?

When they can just ignore your post?

Come on, dude.