CDbCriteria: LIMIT does not apply on trouth relation #2015

Closed
TheHett opened this Issue Jan 20, 2013 · 6 comments

Comments

3 participants

TheHett commented Jan 20, 2013

Hi,
I have relations in model Chinchilla:

public function relations()
{
        'chinchillaPhotoAlbums'=>array(self::HAS_MANY, 'ChinchillaPhotoAlbum', 'chinchilla_id'),
        'photos'=>array(self::HAS_MANY, 'Photo', array('photo_album_id'=>'photo_album_id'), 'through'=>'chinchillaPhotoAlbums'),
}

If I am in obtaining related objects with LIMIT and TOGETHER

    $criteria = new CDbCriteria();
    $criteria->with = array(
        'photos'=>array(
            'limit'=>10,
            'together'=>false,
        ),
    );
    $this->model = Chinchilla::model()->findByPk($id, $criteria);

SQL:

SELECT ...
FROM `a2_chinchilla` `t` 
LEFT OUTER JOIN `a2_chinchilla_photo_album` `chinchillaPhotoAlbums` 
ON (`chinchillaPhotoAlbums`.`chinchilla_id`=`t`.`id`) 
LEFT OUTER JOIN `a2_photo` `photos` 
ON (`chinchillaPhotoAlbums`.`photo_album_id`=`photos`.`photo_album_id`) 
WHERE (`t`.`id`=24)

limit and together has been ignored

When I do not specify with in criteria, Yii using lazy loading for photos relation

SELECT ...
FROM `a2_photo` `photos` 
LEFT OUTER JOIN `a2_chinchilla_photo_album` `chinchillaPhotoAlbums` 
ON (`chinchillaPhotoAlbums`.`photo_album_id`=`photos`.`photo_album_id`) 
WHERE (`chinchillaPhotoAlbums`.`chinchilla_id`='24')

But then how do I specify a limit?

Member

klimov-paul commented Mar 6, 2013

I can not see any trouble here.
@TheHett, lets review your example.
At the first stage you pass criteria with "together" and limit to the method "CActiveRecord::findByPk()". This method always returns SINGLE row, no wonder your limit was not applied. Also the SQL you provided clearly shows "together" option has been applied:

SELECT ...
FROM `a2_chinchilla` `t` 
/* through 'chinchillaPhotoAlbums' */
LEFT OUTER JOIN `a2_chinchilla_photo_album` `chinchillaPhotoAlbums` 
ON (`chinchillaPhotoAlbums`.`chinchilla_id`=`t`.`id`) 
/* together with 'photos' through 'chinchillaPhotoAlbums' */
LEFT OUTER JOIN `a2_photo` `photos` 
ON (`chinchillaPhotoAlbums`.`photo_album_id`=`photos`.`photo_album_id`) 
WHERE (`t`.`id`=24)
/*No limit, because of 'findByPk'*/

If you want to apply limit on lazy load, you should use method signature instead of property:

$photos = Chinchilla::model()->findByPk($id)->photos(array('limit'=>10));

I can not see any bug here, just missunderstanding.

samdark closed this Mar 6, 2013

Owner

samdark commented Mar 13, 2013

@klimov-paul can you contact me via email? sam@rmcreative.ru. Thanks.

Sorry for using github comments for it.

TheHett commented Apr 2, 2013

Hi, thanks for your comments

$photos = Chinchilla::model()->findByPk($id)->photos(array('limit'=>10));

When needed loading to many objects is generates many queryes.

I still do not fully understand: relation "photos" is HAS_MANY relation and this using as any HAS_MANY relation, but joined on not primary table, why I can not use "trouth" for this?

Sorry for my english :-\

Member

klimov-paul commented Apr 2, 2013

@TheHett, in order to use eager loading and through benefit in the AR query, you should start to build it from the other side.
Not "Chinchilla" with "Photos", but "Photos" with "Chinchilla":
Photos::model()->with('chinchilla')->findAll(array('limit'=>10));

TheHett commented Apr 2, 2013

This does not apply to my situation.
I need displaying Chinchillas with latest N-photos.

I`m using lazzy loaing, when on page displaying 50 chinchillas, this creates an additional 50 queryes, and could be in one

Member

klimov-paul commented Apr 2, 2013

Since you want to apply limit while getting each Chinchilla photo set, I can not imagine an SQL syntax, which could do that by single query. You need to select all records from Chinchilla and a 10 records from Photo per each found, I am afraid no SQL mechanism could resolve this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment