title: CakePHP - find conditions with associations
body: <p>I have an issue with a cakePHP find conditions. I have a Club model, a User model and a Post model :</p>
  
  <p>Club hasMany Post</p>
  
  <p>Club HABTM User</p>
  
  <p>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</p>
  
  <pre><code>        $clubs = $this-&gt;Club-&gt;User-&gt;find('first', array(
          'conditions' =&gt; array('User.id' =&gt; $id),
          'contain' =&gt; array(
              'Club' =&gt; array(
                  'fields' =&gt; array(
                      'id',
                      'name'
                  ),
                  'Post' =&gt; array(
                      'fields' =&gt; array(
                          'id',
                          'title',
                          'description',
                          'created',
                      ),
                      'order' =&gt; array('Post.created' =&gt; 'DESC'),
                      'conditions' =&gt; array(
                          'DATEDIFF(NOW(), `Post.created`) &lt;= /* 1 */',
                      ),
                      'limit' =&gt; '/* 2 */'
                  )
              )
          )
      ));
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help would be welcome.</p>
  
  <p>Thanks for reading.</p>
  
  <p><strong>edit</strong></p>
  
  <p>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)</p>
  
  <pre><code>$overview = $this-&gt;Club-&gt;Follower-&gt;find('first', array(
          'conditions' =&gt; array('Follower.id' =&gt; $this-&gt;Auth-&gt;user('id')),
          'contain' =&gt; array(
              'Club' =&gt; array(
                  'fields' =&gt; array(
                      'id',
                      'name'
                  ),
                  'Post' =&gt; array(
                      'fields' =&gt; array(
                          'id',
                          'title',
                          'description',
                          'created',
                      ),
                      'order' =&gt; array('Post.created' =&gt; 'DESC'),
                      'conditions' =&gt; array(
                          'DATEDIFF(NOW(), `Post.created`) &lt;= ClubsUser.max_time_posts',
                      ),
                      'limit' =&gt; 10
                  )
              )
          )
      ));
  </code></pre>
  
  <p>It generates the three following SQL queries (i replaced the fields name by * for clarity reasons) :</p>
  
  <pre><code>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`) &lt;= `ClubsUser`.`max_time_posts` AND `Post`.`club_id` = (1)
  ORDER BY `Post`.`created` DESC
  LIMIT 10
  </code></pre>
  
  <p>The last query returns the error : <strong>field 'ClubsUser.max_time_posts' unknown in where clause</strong></p>
  
  <p>Ideally, i would like to get a query close to the one below instead of the last two queries above :</p>
  
  <pre><code>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`) &lt;= `ClubsUser`.`max_time_posts`) 
  ORDER BY `ClubsUser`.`role_id` DESC, `Post`.`created` DESC
  LIMIT 10
  </code></pre>
  
  <p>Any ideas ?</p>