Cakephp DISTINCT

Question

How do I use DISTINCT to get unique user id with highest value for total_time_driven_at_this_trip and also pull user_name from another table which has belongsto relations based on user_id?

I tried this...

$this->set('tripNsws', $this->TripNsw->find('all',array('limit' => 20,'fields' => array('DISTINCT(TripNsw.user_id)','TripNsw.total_time_driven_at_this_trip'),'group' => array('TripNsw.user_id') ,'order' => array('TripNsw.total_time_driven_at_this_trip desc'))));

but it's not working.

I suppose you need to get below....

SELECT DISTINCT(user_id),`total_time_driven_at_this_trip` FROM `trip_nsws` order by `total_time_driven_at_this_trip` desc

user_1308163 · Answer

Correct Syntax for DISTINCT keywork in cakephp

$this->set('banners', $this->Banner->find('all',array('fields'=>'DISTINCT Banner.id')));

Make sure DISTINCT uses in fields array.

user_1256062 · Answer

You can consider setting it as a Virtual Field ( http://book.cakephp.org/1.3/view/1608/Virtual-fields )

$this->Model->virtualFields['distinct_user'] = 'DISTINCT Model.user_id';

Cakephp DISTINCT

6 Answers