CakePHP containable and paginate - deep associations not appearing

Question

I'm trying to use containable in a paginated query. The code is below, in full.

    $this->paginate = array(
        'conditions'=>array(
            'Track.pending'=>0,
            'Track.status'=>1
        ),
        'contain'=>array(
            'Vote'=>array(
                'conditions'=>array(
                    'Vote.created >'=>$start,
                    'Vote.created <='=>$end
                ),
                'fields'=>array(
                    'Vote.vote_type_id',
                    'Vote.id',
                ),
            ),
            'Artist'=>array(
                'Image'=>array(
                    'fields'=>array(
                        'Image.name'
                    )
                ),
                'Genre'=>array(
                    'fields'=>array(
                        'Genre.name'
                    )
                ),
                'fields'=>array(
                    'Artist.name',
                    'Artist.id',
                    'Artist.slug',
                    'Artist.city',
                    'Artist.genre_id'
                )
            )
        ),
        'fields'=>array(
            'Track.id',
            'Track.slug',
            'Track.name',
            'Track.featured',
            'Track.plays',
            'Track.vote_score',
            'Track.vote_week_score',
            'Track.video',
            'Track.status',
            'Track.created'
        ),
        'order'=>$order
    );

The direct associations (Vote and Artist) are picked up, but Image and Genre are not. Here's the SQL that is being generated:

SELECT COUNT(*) AS `count` FROM `tracks` AS `Track` LEFT JOIN `artists` AS `Artist` ON (`Track`.`artist_id` = `Artist`.`id`) WHERE `Track`.`pending` = 0 AND `Track`.`status` = 1

SELECT `Track`.`id`, `Track`.`slug`, `Track`.`name`, `Track`.`featured`, `Track`.`plays`, `Track`.`vote_score`, `Track`.`vote_week_score`, `Track`.`video`, `Track`.`status`, `Track`.`created`, `Artist`.`name`, `Artist`.`id`, `Artist`.`slug`, `Artist`.`city`, `Artist`.`genre_id` FROM `tracks` AS `Track` LEFT JOIN `artists` AS `Artist` ON (`Track`.`artist_id` = `Artist`.`id`) WHERE `Track`.`pending` = 0 AND `Track`.`status` = 1 ORDER BY `Track`.`vote_week_score` DESC LIMIT 20

SELECT `Vote`.`vote_type_id`, `Vote`.`id`, `Vote`.`track_id` FROM `votes` AS `Vote` WHERE `Vote`.`created` > '2011-09-26' AND `Vote`.`created` <= '2011-10-03' AND `Vote`.`track_id` IN (24, 35, 31, 25, 27, 34, 56, 58)

Cake is not picking up the deeper associations. Is this a limitation of Containable + paginate or am I missing something?

Thanks!

Have you tried getting the data without the paginate? My guess is, you wouldn't get it there either. I'm not sure this is helpful, but I often found JOINs to work better than containable - not sure why though. — Dave, Oct 03 '11 at 14:28
@Dave, thanks, yes I have code elsewhere that gets the same data out without paginating, and it works as I would like it to. — Will, Oct 04 '11 at 08:30

score 1 · Answer 1 · answered Dec 31 '13 at 15:26

1

Check if you have zero value for recursion property of the model.

$this->Post->recursive = 0;

After hours of headbanging removing the above line fixed it for me

answered Dec 31 '13 at 15:26

shturm

857
7
18

score 0 · Answer 2 · answered Feb 12 '14 at 20:58

0

Make sure you attach the behavior:

$this->ModelName->Behaviors->attach('Containable');

answered Feb 12 '14 at 20:58

Choma

708
12
24

score -2 · Answer 3 · answered Sep 26 '12 at 20:08

Probably the problem is $uses in your controller, in case you have it set in AppController.

Example:
Let's say your controller is ProductsController, your model is Product and your database table is products. Also you probably have some sort of associations set in your Model.

If you don't set $uses in the Controller, but set it in AppController, Cake presumes the database table correctly and workes fine. But he doesn't look for your Model file as it doesn't appear in $uses (the one defined in AppController).

So it will work if you write in your controller:

public $uses = array('Yourmodel');

Also:

You don't have to set 'recursive', this is why contain exists
Don't forget to fetch the foreignKeys in contain ex: Product 'fields'=>array('category_id'); Category 'fields'=>'product_id'

You shouldn't be using 'uses', you should be using 'loadModel'. Also, no need to explicitly load an associated model as cake is supposed to take care of that. And here loading the extra model is useless. Contain is known not to work for 'hasMany' relationships, so here using the much-despised 'recursive' is the only solution (be sure to use at least level 2 or you also won't get all the data). — George M Reinstate Monica, Jun 09 '17 at 16:42

CakePHP containable and paginate - deep associations not appearing

3 Answers3