CakePHP and HABTM Model Limit Error

Question

I've got a series of Post models that hasAndBelongsToMany Media models. In certain function calls inside of the Post model, I don't need to retrieve the entire list of Media models. However, when I use the following code:

$this->unbindModel( array('hasAndBelongsToMany' => array('Media')) );

// Rebind to get only the fields we need:
$this->bindModel(
        array('hasAndBelongsToMany' => array(
            'Media' => array(
                'className' => 'Media',
                'joinTable' => 'media_posts',
                'foreignKey' => 'post_id',
                'associationForeignKey' => 'media_id',
                'limit' => 1, 
                'fields' => array('Media.type', 'Media.path', 'Media.title')
            )
        )
    )
);
$this->find('all', $params);

This limit only works on one of the first retrieved Post model and all following Post models have no associated Media:

Array
(
    [0] => Array
        (
            [Profile] => Array
                (
                )

            [Media] => Array
                (
                    [0] => Array
                        (
                            [type] => photo
                            [path] => ''
                            [title] => ''
                        )

                )
        )

    [1] => Array
        (
            [Profile] => Array
                (
                )

            [Media] => Array
                (
                )

        )

)

Any suggestions would be great. Thanks!

Answer 1

Try this:

$this->yourModel->hasAndBelongsToMany['Media'] = false; // or null

And then set your HABTM association manually

$this->yourModel->hasAndBelongsToMany['Media'] = array(........);

Or simply modify the association without nulling it:

$this->yourModel->HABTM['Media']['fields'] = array(....)

Answer 2

Cake fetches all the Habtm-related records in one batch query and then assembles them into the results array afterwards. Any additional conditions you specify in the association will be used as is in the query, so it'll look something like this:

SELECT … FROM Media WHERE Media.id in (1, 2, 3, …) LIMIT 1

So it'll only retrieve a single HABTM model.

There's no apparently easy solution for this. Maybe you could think about the original premise again and why the "first" (LIMIT 1) record is supposedly the right one, maybe you can find a different condition to query on.

Failing that, you could rebind your models so Media has a hasMany relationship to medias_posts, the pivot table. For hasMany and belongsTo queries, Cake automatically does JOIN queries. You could use a GROUP BY clause then, which would give you the desired result:

SELECT … FROM Media JOIN medias_posts … GROUP BY medias_posts.post_id

You might also want to experiment with passing the 'join' parameter with the query, to achieve that effect without extensive rebinding.

$this->Media->find('all', array('join' => array(…), …));

Answer 3

why not use the containable behaviour

// you would probably want the next line in the app_model ot be able to use it with all models
$this->Post->actsAs = array('Containable')
$params['conditions'] = array(
);
$params['contain'] = array(
    'Media' => array(
        'fields' => array(
            'type', 'path', 'title'
        ),
        'limit' => 1
    )
);
$this->Post->find('all', $params);

---

EDIT:

Just tried that and got this sql (Module <-> Tag):

SELECT `Module`.`id` FROM `modules` AS `Module` WHERE 1 = 1 

and

SELECT `Tag`.`id`, `ModulesTag`.`module_id`, `ModulesTag`.`tag_id` 
FROM `tags` AS `Tag` 
JOIN `modules_tags` AS `ModulesTag` 
  ON (`ModulesTag`.`module_id` IN (1, 2, 3, 4) AND `ModulesTag`.`tag_id` = `Tag`.`id`) 
WHERE `Tag`.`belongs_to` = 'Module' 
ORDER BY `Tag`.`name` ASC 
LIMIT 1

obviously that cannot return the wanted result, as you would have to do a query for each Module result (which then again would result in way too many queries).

As a conclusion I would return all Tags (in my example) as the overhead in too many result rows is better than the overhead of too many queries..

Answer 4

CakePHP has a very powerful tool for this containable behaviour

Answer 5

After some speed research which I should have done at first, I came to realize that the difference was negligible for the overall amount of results I was retrieving, regardless of the number of associated models. Due to that, I suppose this questions has been become null, though there were some great suggestions and I have learned to love the containable behavior. Thanks guys!