Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
MainelyDesign.com Blog
Forcing A Single Join in CakePHP Pagination
Posted on 10/14/2011 at 09:25 am by Kevin Wentworth
Viewed 22,534 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:
- The table wasn't being aliased using the AS keyword, it was missing.
- 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
- $this->paginate['User']['joins'] =
- 'table' => 'some_join_table',
- 'alias' => 'SomeJoin',
- 'type' => 'inner',
- 'conditions' =>
- 'SomeJoin.ref_id = Ref.id'
- )
- )
- );
Notice the 'joins' => array(array());
The wrong way
- $this->paginate['User']['joins'] =
- 'table' => 'some_join_table',
- 'alias' => 'SomeJoin',
- 'type' => 'inner',
- 'conditions' =>
- 'SomeJoin.ref_id = Ref.id'
- )
- )
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
Tags for Forcing A Single Join in CakePHP Pagination
Cakephp | Cakephp 13 | Database | Errors | Habtm | Mysql | Mssql | Web Programming | Usage
Comments for this Posting
No comments. Be the first to post a reply.
Sorry, comments are closed for this posting.
Please Email Kevin if you have any questions. Thanks!