CakePHP - find conditions with associations

Question

I have an issue with a cakePHP find conditions. I have a Club model, a User model and a Post model :

Club hasMany Post

Club HABTM User

Basically, my clubs_users table also contains additional fields such as let's say 'limit' and 'diff' that respectively indicate the maximum number of posts a user want to display and how old those posts are allowed to be. I'd like to select the appropriate posts for every club related to a given user. I'm doing someting like this

        $clubs = $this->Club->User->find('first', array(
        'conditions' => array('User.id' => $id),
        'contain' => array(
            'Club' => array(
                'fields' => array(
                    'id',
                    'name'
                ),
                'Post' => array(
                    'fields' => array(
                        'id',
                        'title',
                        'description',
                        'created',
                    ),
                    'order' => array('Post.created' => 'DESC'),
                    'conditions' => array(
                        'DATEDIFF(NOW(), `Post.created`) <= /* 1 */',
                    ),
                    'limit' => '/* 2 */'
                )
            )
        )
    ));

What should i put instead of 1 and 2 for this to work ? I tried ClubsUser.diff and ClubsUser.limit but i got an error stating that those fields were unknown in the where clause.

Any help would be welcome.

Thanks for reading.

edit

After bancer's comment, i looked deeper into the MySQL doc and it appeared that LIMIT expects only numeric arguments. So I now just want to return the posts that are not too old. My new find is (with the actual fields name)

$overview = $this->Club->Follower->find('first', array(
        'conditions' => array('Follower.id' => $this->Auth->user('id')),
        'contain' => array(
            'Club' => array(
                'fields' => array(
                    'id',
                    'name'
                ),
                'Post' => array(
                    'fields' => array(
                        'id',
                        'title',
                        'description',
                        'created',
                    ),
                    'order' => array('Post.created' => 'DESC'),
                    'conditions' => array(
                        'DATEDIFF(NOW(), `Post.created`) <= ClubsUser.max_time_posts',
                    ),
                    'limit' => 10
                )
            )
        )
    ));

It generates the three following SQL queries (i replaced the fields name by * for clarity reasons) :

SELECT * FROM `users` AS `Follower`
WHERE `Follower`.`id` = 1
LIMIT 1

SELECT * FROM `clubs` AS `Club`
JOIN `clubs_users` AS `ClubsUser`
ON (`ClubsUser`.`user_id` = 1 AND `ClubsUser`.`club_id` = `Club`.`id`)
ORDER BY `ClubsUser`.`role_id` DESC 

SELECT * FROM `posts` AS `Post`
WHERE DATEDIFF(NOW(), `Post`.`created`) <= `ClubsUser`.`max_time_posts` AND `Post`.`club_id` = (1)
ORDER BY `Post`.`created` DESC
LIMIT 10

The last query returns the error : field 'ClubsUser.max_time_posts' unknown in where clause

Ideally, i would like to get a query close to the one below instead of the last two queries above :

SELECT * FROM `clubs` AS `Club`
JOIN `clubs_users` AS `ClubsUser`
ON (`ClubsUser`.`user_id` = 1 AND `ClubsUser`.`club_id` = `Club`.`id`)
LEFT JOIN `posts` AS `Post`
ON (`Post`.`club_id` = `Club`.`id` AND DATEDIFF(NOW(), `Post`.`created`) <= `ClubsUser`.`max_time_posts`) 
ORDER BY `ClubsUser`.`role_id` DESC, `Post`.`created` DESC
LIMIT 10

Any ideas ?

Answer 1

You should not use HABTM if you have extra fields in the join table, because Cake actually deletes and recreates those joins. You should use a "hasMany through" association:

Club hasMany ClubUser
User hasMany ClubUser
ClubUser belongsTo Club
ClubUser belongsTo User

When you do your find on User, you just contain ClubUser then contain Club.

$this->User->find('all', array(
    'contain' => array(
        'ClubUser' => array(
            'fields' => array(
                'diff', 
                'limit'),
            'Club'))));

More details here:

http://book.cakephp.org/1.3/view/1650/hasMany-through-The-Join-Model

and here:

http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany-through