CakePHP Pagination with a model which has a HABTM model

Question

I have 3 models (artist, media item, accent)

Artist hasMany media items, Media items hasAndBelongsToMany accents,

Now what I am having issues with is how to search with pagination, I need to get pages of 8 artists and the media_items which match the accent entered. Hopefully that makes sense.

This is what I have currently

$this->Paginator->settings = array(
    'limit' => 8,
    'fields' => array("Artist.first_name",
                        "Artist.last_name", "Artist.image",
                        "Artist.image", "Artist.slug"),
    'order' => "Artist.last_name",
    'url' => $this->passedArgs,
    'contain' => array(
                    'MediaItem'=> array(
                        "fields" => array("MediaItem.file",
                                            "MediaItem.title",
                                            "MediaItem.accent",)
                ),
    'joins' => array(
                    array(
                        'table' => 'media_items',
                        'type' => 'INNER',
                        'alias' => 'MediaItem',
                        'conditions' => array(
                                "AND" => array('Artist.id = MediaItem.artist_id')
                            )
                    ),
                    array(
                        'table' => 'media_accents_media_items',
                        'type' => 'RIGHT',
                        'alias' => 'MediaAccentsMediaItems',
                        'conditions' => array(
                                "AND" => array('MediaAccentsMediaItems.media_item_id = MediaItem.id')
                            )
                    ),
                    array(
                        'table' => 'media_accents',
                        'type' => 'RIGHT',
                        'alias' => 'MediaAccent',
                        'conditions' => array(
                                "AND" => array('MediaAccent.id = MediaAccentsMediaItems.media_accent_id'),
                                "OR" => array('MediaAccent.name LIKE' => "%".$searchFor ."%")
                            )
                    )
                ),
    'group' => 'Artist.id',
    'update' => '.results-container',
    'evalScripts' => true
);

it gets the right artists but returns all their tracks rather than the ones I need. Is there a better way rather than looping through the results pruning them? Which is my current backup plan.

Answer 1

Remove joins and just use Containable Behavior, here is docs for deeper associations.