CakePHP containable and paginate - deep associations not appearing

1
votes

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!

3
cakephpcakephp-1.3containable
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
@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

3 Answers

1
votes

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

0
votes

Make sure you attach the behavior:

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

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:

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