When working with CakePHP, one of the hardest things for me has been getting used to formulating SQL queries with the CakePHP find method syntax. Today I needed to run a SQL query where I limited the amount of records returned from the database, and after a little trial and error, I found the correct CakePHP SQL LIMIT syntax, and thought I'd share that here today.
In my case, given a database table named logfile_records, here's how I created a CakePHP find function call to limit the results returned from the database to 1,000 records:
$results = $this->LogfileRecord->find('all', array('limit'=>1000));
As you can see this isn't too hard, but as a CakePHP newbie, my two biggest questions were whether I should use the CakePHP find method with the 'all' option for this query, along with the proper syntax for the limit clause.
CakePHP, find, limit, and order by
In the real world you'll probably want to combine that SQL LIMIT clause with other options. Here's a quick example of how to combine that SQL LIMIT clause with a SQL ORDER BY clause, using the CakePHP find function:
$results = $this->LogfileRecord->find('all', array('limit'=>1000, 'order'=>array('date DESC')) );
In that ORDER BY clause I'm specifying that I wanted to order the SQL query results by the
date field, in descending order.
I hope these CakePHP SQL query examples are helpful. I'll try to keep adding interesting (and more complicated) CakePHP find examples out here as I find them.