MainelyDesign.com Blog

Forcing A Single Join in CakePHP Pagination

Posted on 10/14/2011 at 09:25 am by Kevin Wentworth
Viewed 22,723 times | 0 comments

The devil's in the details... I was trying to make a really simple, dreadfully easy, database join in my CakePHP web application.  I've forced joins in Cake before, using the 'joins' key in the options array for find calls and paginate calls with no issue.  It was late and for the first time I only wanted to use a single join.  I copied the join code from a much more complex web app and pasted it into my new 'joins' conditions.  And then... I got SQL errors. 

That query doesn't look right...

Looking at the query I could see 2 things that weren't right:

1. The table wasn't being aliased using the AS keyword, it was missing.
2. The join was happening ON Array!

Joins have to be specified in an array

Even if there is a single Cakephp join!  Remember this.  It looks weird, but if you don't encapsulate your join in an array, your SQL query will look even weirder.

The right way to use 'joins' options in CakePHP

Notice the 'joins' => array(array());

The wrong way

Notice only 'joins' => array(); is specified.  This is not the right way- you need to use 'joins' => array(array()); as above.

Hopefully, this helps someone.  If nothing else, I won't forget about this little feature!

You also need to see this post about the right paginateCount function for these joins to work:
http://www.mainelydesign.com/blog/view/best-paginatecount-cakephp-with-group-by-support

Cheers,
-Kevin Wentworth