As a CakePHP newbie, I'm spending a lot of time learning how to formulate SQL queries with the CakePHP find function syntax. This morning I need to create a "select distinct" SQL query in CakePHP. Fortunately this wasn't too hard once I figured out how to piece it all together.
In my case, I have a database table named
logfile_records, and I wanted to run a SQL "select distinct" query against that table. If I was going to write the query in plain SQL I'd write it like this:
select distinct(date) from logfile_records order by date DESC;
After piecing various CakePHP find function hints together, I was able to create the equivalent query in CakePHP like this:
$results = $this->LogfileRecord->find('all', array('fields'=>array('DISTINCT date'), 'order'=>array('date DESC')) );
I'm still trying to figure out how to format all these these arrays in CakePHP to make my code easier to read, but regardless of my formatting, the CakePHP find function shown produces the same SELECT DISTINCT query that I showed as plain SQL above.
Cake find handles the GROUP BY concept directly, which is equivalent in many cases to DISTINCT:
$results = $this->LogfileRecord->find('all', array( 'fields'=>array('date'), 'group'=>array('date'), 'order'=>array('date DESC'), ));
Another somewhat related hint... If your LogfileRecord model associates with other models that have a field called date, then you must make sure you specify the model name whenever you specify a field.
So, instead of:
You should use:
I've simply gotten in the habit of including the model name every time, so I don't think about when I need to and when I don't.