title: CAKEPHP Group by problem in paginate
body: <p>CAKEPHP Group by problem in paginate..</p>
  
  <p>This is my table structure</p>
  
  <pre><code> Friends Table
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user1_id` int(11) NOT NULL DEFAULT '0',--&gt; UserFrom
    `user2_id` int(11) NOT NULL DEFAULT '0',---&gt; UserTo
  </code></pre>
  
  <p>I need to get all the unique records of one user and his user1_id = 100 </p>
  
  <p>There are lot of duplicate values in user2_id. I need to get the unique values 
  While i trying this code it returns only first 12 values(according to limit).
  If i commented the group by line then all records are displaying (including duplicate values)</p>
  
  <pre><code>$this-&gt;paginate = array(
                      'conditions' =&gt; $conditions,
          'contain'=&gt;array(
              'UserFrom'=&gt;array(
                  'fields'=&gt;array(
                     'UserFrom.id',
                     'UserFrom.first_name',
                     'UserFrom.last_name',
                  ),
               ),
               'UserTo'=&gt;array(
                  'fields'=&gt;array(
                      'UserTo.id',
                      'UserTo.first_name',
                      'UserTo.last_name',
                      )
               )
              ),'limit' =&gt; 12,
              'order' =&gt; array('Friend.id' =&gt; 'desc'),
              'recursive'=&gt;0,
              'fields'=&gt;array('DISTINCT Friend.user2_id','Friend.*'),
              'group'=&gt;array('UserTo.id'),
              );
  </code></pre>
  
  <p>This is my sql query on that page</p>
  
  <p>SELECT COUNT(*) AS <code>count</code> FROM <code>friends</code> AS <code>Friend</code> LEFT JOIN <code>users</code> AS <code>UserTo</code> ON (<code>Friend</code>.<code>user2_id</code> = <code>UserTo</code>.<code>id</code>) LEFT JOIN <code>users</code> AS <code>UserFrom</code> ON (<code>Friend</code>.<code>user1_id</code> = <code>UserFrom</code>.<code>id</code>) WHERE <code>UserFrom</code>.<code>nick_name</code> = 'shyam' AND <code>Friend</code>.<code>status</code> = 'friend' GROUP BY <code>UserTo</code>.<code>id</code>  </p>
  
  <p>SELECT DISTINCT <code>Friend</code>.<code>user2_id</code>, <code>Friend</code>.*, <code>UserTo</code>.<code>id</code>, <code>UserTo</code>.<code>first_name</code>, <code>UserTo</code>.<code>last_name</code>, <code>UserTo</code>.<code>nick_name</code>, <code>UserTo</code>.<code>name</code>, <code>UserTo</code>.<code>icon_medium</code>, <code>UserTo</code>.<code>icon_big</code>, <code>UserFrom</code>.<code>id</code>, <code>UserFrom</code>.<code>first_name</code>, <code>UserFrom</code>.<code>last_name</code>, <code>UserFrom</code>.<code>nick_name</code>, <code>UserFrom</code>.<code>name</code> FROM <code>friends</code> AS <code>Friend</code> LEFT JOIN <code>users</code> AS <code>UserTo</code> ON (<code>Friend</code>.<code>user2_id</code> = <code>UserTo</code>.<code>id</code>) LEFT JOIN <code>users</code> AS <code>UserFrom</code> ON (<code>Friend</code>.<code>user1_id</code> = <code>UserFrom</code>.<code>id</code>) WHERE <code>UserFrom</code>.<code>nick_name</code> = 'shyam' AND <code>Friend</code>.<code>status</code> = 'friend' GROUP BY <code>UserTo</code>.<code>id</code> ORDER BY <code>Friend</code>.<code>id</code> desc LIMIT 12</p>
  
  <p>1.First  count query returning count properly 
  2.Second query if i put limit it not working properly. i.e there are 144 records in my table i need to display 12 per page.. only first page coming if i use group by and limit</p>