1. You should use or GROUP BY or DISTINCT; 2. DISTINCT works for whole row, not particular field there. Your whole row includes ID which is unique; 3. When you have LIMIT you get not more then this number of values. So "only first 12 values" is absolutely correct behavior as you noted LIMIT 12 explicitly.
So by remove DISTINCT and LIMIT you'll get what you want. On Jul 23, 4:23 pm, Gmail Support <[email protected]> wrote: > CAKEPHP Group by problem in paginate.. > > This is my table structure > > Friends Table > `id` int(11) NOT NULL AUTO_INCREMENT, > `user1_id` int(11) NOT NULL DEFAULT '0',--> UserFrom > `user2_id` int(11) NOT NULL DEFAULT '0',---> UserTo > > I need to get all the unique records of one user and his user1_id = 100 > > 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) > > $this->paginate = array( > 'conditions' => $conditions, > 'contain'=>array( > 'UserFrom'=>array( > 'fields'=>array( > 'UserFrom.id', > 'UserFrom.first_name', > 'UserFrom.last_name', > ), > ), > 'UserTo'=>array( > 'fields'=>array( > 'UserTo.id', > 'UserTo.first_name', > 'UserTo.last_name', > ) > ) > ),'limit' => 12, > 'order' => array('Friend.id' => 'desc'), > 'recursive'=>0, > 'fields'=>array('DISTINCT Friend.user2_id','Friend.*'), > 'group'=>array('UserTo.id'), > ); -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
