AR should apply LIMIT 1 when lazy loading HAS_ONE relations

[samdark]

Post relation:

'lastComment' => array(self::HAS_ONE, 'Comment', 'post_id', 'order' => 'lastComment.id DESC')

Controller:

$post = Post::model()->find();
$lastComment = $post->lastComment;

SQL generated:

SELECT `lastComment`.`id` AS `t1_c0`, `lastComment`.`content` AS `t1_c1`, `lastComment`.`post_id` AS `t1_c2`
FROM `tbl_comment` `lastComment`
WHERE (`lastComment`.`post_id`=:ypl0)
ORDER BY lastComment.id DESC)

Since we need only one record LIMIT 1 should be applied.

Very minimal test application:

http://ds.rmcreative.ru/ar_has_one_limit_issue.zip

[klimov-paul]

On my opinion, the issue is inconsistent.

The "HAS_ONE" relation is the one, which supports value extraction from related table by the single query.

If you run:

Item::model()->with(‘category’)->find();

Active Record will fetch the data for “Item” and “Category” by the single query, like following:

SELECT i.*,c.*
FROM item AS i
INNER JOIN category AS c ON i.category_id = c.id

If we satisfy the demand of this issue and allow to specify order and limit for the “HAS_ONE” relation, how we will resolve the “with” option?

@tomtomsen, if you want to find latest comment for the post, you should declare the relation “posts” as “HAS_MANY” and then use the method notation on its lazy load:

$post = Post::model()->findByPk($id);
$lastComment = $post->comments(array(‘order’=>’id DESC’, ‘limit’=>1));

[qiangxue]

Closed for the reasons explained.

[magefad]

http://www.yiiframework.ru/forum/viewtopic.php?f=3&t=12859

array(‘order’=>’id DESC’, ‘limit’=>1)

not working in model search() (not lazy load). Any solutions?

[klimov-paul]

There is not way to find “users” with their last “order” using eager loading. There is not SQL query which can perform such selection.
Last “order": of the user can be found only via lazy loading.

[magefad]

In CGridView will be hundreds of SQL queries..

[klimov-paul]

In your particular case you may attempt to solve the situation using “denormalize” approach.
You can add an extra field “is_last” to your “order” table and manually set it to “1” on every new user order, while settting “0” for existing one.
Then you can join the table “order” with the “user” as “HAS_ONE” using condition “order.user_id = user.id AND order.is_last = 1”.

[magefad]

I thought about this solution, but it is not very good: (

[klimov-paul]

Sorry, but there is nothing else I could advise you.
Extracting last record from the slave table for each record from master table is impossible.
This has nothing to do with Yii or ActiveRecord – it is a limitation of SQL, or to be more precise the limitation of the relational database.
You should either refactor your DB structure and logic to “hack” the problem or switch to some not relational data storage.