ActiveRecord relations + named scopes, "limit" issue

Yii 1.1 General Discussion
1

I have Gallery and Photo model.

For Gallery preview, I want Gallery->photos to be random 5 Photos belonging to this given gallery. In other cases, Gallery->photos should be a list of all the photos belonging to this Gallery.

when calling




'data' => Gallery::model()->with('photos:preview')->findAll(),

it returns list of Galleries, and each Gallery->photos contain list of it’s photos.

The ‘order’ is proper (random), but the list contains ALL the photos belonging to the gallery, not only 5 of them. Simply, the limit is not applied

Here’s the Gallery::relations:




public function relations() {

	return array(

		'photos' => array(self::HAS_MANY, 'Photo', 'gallery_id'),

	);

}

and Photo::scopes


public function scopes(){

	return array(

		'preview' => array('limit' => 5, 'order' => 'RAND()')

	);

}
2

I did some further investigation.

Modified my models to define "preview" relation in Gallery instead of Photo:

As a result, Photo has no relations, while Gallery::relations returns following:




'preview' => array(self::HAS_MANY, 'Photo', 'gallery_id', 'limit' => 5, 'order' => 'RAND()'),

It seems using with() results in ignoring limit condition.

Following code does NOT apply limit (order however is applied):


$g = Gallery::model()->with(array('preview' => array('limit' => 5)))->findByPk(2);

foreach ($g->preview as $photo)

while this code does apply the limit:


$g = Gallery::model()->findByPk(2);

foreach ($g->preview as $photo)

Any suggestions?

Is that a bug?

SQL generated for each case:

first (incorrect):




SELECT `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c1`, `t`.`status` AS `t0_c2`, `t`.`author_id` AS `t0_c3`, `t`.`date_created` AS `t0_c4`, `t`.`description` AS `t0_c5`, `preview`.`id` AS `t1_c0`, `preview`.`filename` AS `t1_c1`, `preview`.`filename_original` AS `t1_c2`, `preview`.`gallery_id` AS `t1_c3`, `preview`.`user_id` AS `t1_c4`, `preview`.`date_added` AS `t1_c5`, `preview`.`order` AS `t1_c6` FROM `galleries` `t` LEFT OUTER JOIN `photos` `preview` ON (`preview`.`gallery_id`=`t`.`id`) WHERE (`t`.`id`=2) ORDER BY RAND()

second (correct)




SELECT `preview`.`id` AS `t1_c0`, `preview`.`filename` AS `t1_c1`, `preview`.`filename_original` AS `t1_c2`, `preview`.`gallery_id` AS `t1_c3`, `preview`.`user_id` AS `t1_c4`, `preview`.`date_added` AS `t1_c5`, `preview`.`order` AS `t1_c6` FROM `photos` `preview` WHERE (`preview`.`gallery_id`=:ypl0) ORDER BY RAND() LIMIT 5. Bound with :ypl0='2')


SELECT * FROM `galleries` `t` WHERE `t`.`id`=2 LIMIT 1
3

I am not sure you can apply a limit to the joined table…

What is the exact sql you’d like to obtain? I mean, a query that retrives all Gallery and at most 5 Photos for each gallery?

4

exactly.

it seems to be impossible so I’ve removed ‘with’ call which however produces extra query for each gallery…

5

This is a SQL limitation, not Yii: You can’t limit the number of joined items for a JOIN. So using a separate query is the only option you have here.

6

Well, I believe there should be some warning in the docs, or even runtime… :)

Thanks!

7

Mike, that’s totally true. It does not make any sense to limit number of selected records when there are some ONE-2-MANY joined tables, but I believe it does makes sense to take into consideration the case when, ‘select’ parameter in every joined relations is “false” and there is “GROUP BY” clause.

For instance:




SELECT 

   company.* 

FROM 

   company

LEFT JOIN user ON (user.company_id = company.id)

WHERE

    user.email like '%gmail%'

GROUP BY

    company.id

LIMIT 5

which corresponds to the following PHP code:




$companies = Company::model()->with(array(

   'users' => array(

      'select'    => false,

      'condition' => "user.email like '%gmail%'"

   )

))->findAll(array(

   'group' => 'company.id'

   'limit' => 5

));

In the example above we need to select 5 companies only having users (ONE-2-MANY relation) with GMail accounts. So we don’t select anything from “user” table but we need to JOIN this table in order to apply extra condition on email field.

In fact CActiveFinder does not analyze SQL statement so deep and just does not JOIN ONE-2-MANY relations if there is LIMIT or OFFSET clauses specified.

BUT after Yii code debugging (and reading documentation :)) I have found the following workaround - parameter ‘together’ must be set to “true”. Thanks to this parameter CActiveFinder is forced to joined relation tables in any case:




$companies = Company::model()->with(array(

   'users' => array(

      'select'    => false,

      'condition' => "user.email like '%gmail%'"

   )

))->findAll(array(

   'together' => true,

   'group'    => 'company.id'

   'limit'    => 5

));