CakePHP SQL query - How to run a SQL database query in CakePHP

By Alvin Alexander. Last updated: June 4, 2016

CakePHP SQL query FAQ: How can I run a standard SQL query in CakePHP?

I've been using CakePHP over the last two weeks for a few side projects, and I have to say, I really like it. One of my favorite features is that while the CakePHP creators have made simple queries super-easy, they also let you run more complex SQL queries using standard SQL syntax. Yes, it may be "frowned upon", but a few days ago, when I couldn't figure out how to use the CakePHP find method to run a complicated query, I just said "forget it", and ran a standard SQL query.

So, if you ever need to run a standard SQL query in CakePHP, here's an example of how I just ran my SQL database query manually.

Running a CakePHP SQL query manually - the short answer

If you need to run a SQL query in CakePHP, all you have to do is call the query method on your CakePHP model class, and give it the SQL query you want to run. For instance, if I have a database table named orders, and I want to retrieve all the orders from that table and order them by a field named date, I can just run a query like this from my OrdersController:

$results = $this->Order->query("select * from orders order by date");

Of course with a simple query like this there's no reason to write the SQL manually -- you should just use the CakePHP find method -- but in the more-complicated example that I'm about to show, I couldn't figure out how to formulate the query with the find method, so I just wrote the SQL.

A more complicated CakePHP SQL query

In my case I was working on a program for a customer who wanted a user interface to query their Apache log file records. When I got to a certain part where they wanted to query for the number of page views for a given URL (technically a URI) and a date range, I couldn't figure out how to make the CakePHP find method work, so, under a huge time constraint, I gave up, and wrote the SQL manually.

Here's the method I created in my LogfileRecordsController class to perform my SQL query manually:

function getPageViews()
{
  $uri = $this->params['uri'];
  $start_date = $this->params['start_date'];
  $end_date = $this->params['end_date'];
  if (($end_date == '')) $end_date = $start_date;

  $foo = $this->LogfileRecord->query("select sum(page_views) as num_page_views from logfile_records"
  . " where uri = '" . $uri . "'"
  . " and `date` >= '" . $start_date 
  . "' and `date` <= '" . $end_date . "'");
  return $foo[0][0]['num_page_views'];
}

Please note that I'm not sanitizing the data here at all. I've written this application as an intranet application, and on a low budget, so we sacrificed some application "robustness" in order to get more overall functionality.

If anyone knows how to write this SQL query using the CakePHP find method, I welcome your comments, but again, the point of this CakePHP tutorial is this: If you can't figure out how to formulate a SQL query using one of the powerful CakePHP database query methods, you can run standard SQL queries like this using the query method, and I think this is a terrific feature.

My other CakePHP controller class

One other thing I should note about the method shown above is that it's actually called by a method from another controller class. That's why I do a little extra work on that last line of code with the array syntax, and very specifically, that's why there's a return call there.

I actually call that getPageViews method from my other controller class using the CakePHP requestAction method, as shown in this line of code from my other CakePHP controller class:

$page_views = $this->requestAction('/logfile_records/getPageViews', 
                     array('uri' => $xuri, 
                           'start_date' => $start_date, 
                           'end_date' => $end_date));

In this line of code, I'm saying "Give me the total number of page views for the given URI and date range". I just wanted to deal with that number here as a simple scalar value, so I did the array work in the other method (the getPageViews method).

CakePHP SQL database query - summary

Again, I highly recommend using the CakePHP find method whenever you can, but when you need to run more complicated SQL queries and you can't figure out how to run them using the find method, the CakePHP model query method is a great "back door" feature that lets you get your job done the old-fashioned way.

php

database