Forcing an SQL JOIN in CakePHP

Update (23/7/2010) The improved way to handle this now-a-days: http://book.cakephp.org/view/872/Joining-tables
Update (10/7/2009)… this functionality has been rolled into Habtamable behavior

You’ve probably noticed that by default CakePHP will only create a JOIN query if you have a hasOne or belongsTo associations between your models. There are cases, however, when a JOIN is necessary to get just the right data.

So how do you tell cake to create a JOIN?

Well, you have to rely on some crafty methods, but all in all it’s not very hard, once you get the hang of it…

I will summarize here a few tricks I’ve encountered on the google group, so big thanks to the original authors of these ideas.

Let’s take our favorite sample models:

User hasMany Post
and
Post hasAndBelongsToMany Tag

If we were to do: $this->User->find(‘all’);
CakePHP will run a few selects and return you all the User data. The problem, however, is that if a User doesn’t have any posts CakePHP will still return you the User and an empty array for Post (I’m sure you’ve noticed that before)…

So let’s try to force cake to do a JOIN…

First things first, we have to make cake forget about our previous bindings between the models:


$this->User->unbindModel(array('hasMany'=>array('Post')));
$this->User->Post->unbindModel(array('hasAndBelongsToMany'=>array('Tag')));

Now, we need to trick CakePHP into thinking that we’ve got a hasOne relationship between our models, so that it will build a JOIN query…

We can use bindModel() to achieve that:

$this->User->bindModel(array('hasOne'=>array('Post'=>array(),
                    'PostsTag'=>array(
                        'foreignKey'=>false,
                        'conditions'=>array('PostsTag.post_id = Post.id')),
                    'Tag'=>array(
                        'foreignKey'=>false,
                        'conditions'=> array('PostsTag.tag_id = Tag.id')
))));

So what’s going on here?

We are telling cake to bind the User model using a hasOne relationship to the Post model, which forces cake to build the JOIN query like:

LEFT JOIN `posts` AS `Post` ON (`Post`.`user_id` = `User`.`id`)

Next, we need to ensure that we get all related information from our joinTable (i.e. posts_tags), so that we can eventually get the relevant Tags. Just as before we tell cake to bind PostsTag using a hasOne relationship. Note, we need to tell CakePHP how to do the JOIN condition by specifying the ‘conditions’ key. Therefore, cake will do:

LEFT JOIN `posts_tags` AS `PostsTag` ON (`PostsTag`.`post_id` = `Post`.`id`)

Last, but not least, we have to grab all the Tags. Again, we tell cake to join our Tag model using hasOne (of course here we also have to specify the conditions).

The complete query looks like:

SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`name`, `User`.`created`, `Post`.`id`, `Post`.`title`, `Post`.`post`, `Post`.`created`, `Post`.`modified`, `Post`.`user_id`, `PostsTag`.`id`, `PostsTag`.`post_id`, `PostsTag`.`tag_id`, `PostsTag`.`status`, `Tag`.`id`, `Tag`.`tag`, `Tag`.`status` FROM `users` AS `User` LEFT JOIN `posts` AS `Post` ON (`Post`.`user_id` = `User`.`id`) LEFT JOIN `posts_tags` AS `PostsTag` ON (`PostsTag`.`post_id` = `Post`.`id`) LEFT JOIN `tags` AS `Tag` ON (`PostsTag`.`tag_id` = `Tag`.`id`) WHERE 1 = 1

OK, this is much better and pretty much what we need. However, we are still getting records with empty values, yet we need only the records where the User has a Post and a Post has a Tag.
Well, this is actually due to the fact that we are using a LEFT JOIN, what we really need is an INNER JOIN. You can look up the difference, if you are not sure, but basically INNER JOIN ensures that matching records must exist in all tables.

Remember that for a hasOne (or belongsTo) relationship you can specify a ‘type’ key, so go ahead and modify the above bindModel() call to include ‘type’ => ‘INNER’ for both PostsTag and Tag association.
How? Here’s a hint: ‘foreignKey’=>false, ‘type’=>’INNER’, ‘conditions’=>array(‘PostsTag.post_id=Post.id’)).

Now, we’ve got just the records we were looking for.

A quick note on ‘foreignKey’=>false…
It’s necessary to specify that, so that CakePHP does not attempt to automagically establish a relationship between the models, instead it forces cake to use our conditions for the JOIN
(i.e. ‘conditions’=>array(‘PostsTag.post_id = Post.id’)).

P.S. as josoroma pointed out, if you use this method with paginate() be sure to supply a ‘false’ param, to your bindModel() so that the binding persists for all subsequent methods, i.e. paginate() and paginateCount()

55 thoughts on “Forcing an SQL JOIN in CakePHP

  1. Awesome!! Just what I was (and everyone is) looking for and just what is *not* out there anywhere else.

    Thanks a bunch!

  2. Thanks to this article:
    http://cakebaker.42dh.com/2007/10/17/pagination-of-data-from-a-habtm-relationship/

    Form categories controller Im displaying all the Listings name and
    description from a given category($current_id)

    class CategoriesController extends AppController {

    function index($current_id) {

    $data = $this->paginate(‘CategoriesListing’,
    array(‘Category.id’ => $current_id));
    debug($data);
    exit;

    }

    }

    Listing belongsTo Country:
    My problem now is, from the above index action from Categories
    Controller, how do i get also the Country.name of a Listing from a
    given category?

    Any help is welcome, thanks in advance.

  3. @josoroma

    I suggest you ask this at the google group or the irc channel. This is not really the fastest/best place to get help :)

  4. Thank you Teknoid, i thik i doit with:

    $this->Listing->unbindModel(array(
    ‘hasAndBelongsToMany’ => array(‘Listingcategory’)
    ),
    false
    );

    $this->Listing->bindModel(array(‘hasOne’=>array(
    ‘ListingcategoriesListing’=>array(
    ‘foreignKey’=>false,
    ‘conditions’=>array(‘ListingcategoriesListing.listing_id = Listing.id’)),
    ‘Listingcategory’=>array(
    ‘foreignKey’=>false,
    ‘conditions’=> array(‘ListingcategoriesListing.listingcategory_id = Listingcategory.id’)
    ))), false);

    $listings = $this->paginate(‘Listing’, array(‘ListingcategoriesListing.listingcategory_id’ => $id));

    Now im going to see if i can do that with deep containable behavior.

  5. @piyush

    Anything is possible ;)
    What is the exact query you are trying to make work? Also try to post your question at the google group, you’ll get better/faster help there…

  6. Question about your sentence “by default CakePHP will only create a JOIN query if you have a hasOne or belongsTo associations between your models”.

    let’s take the “User hasMany Post” & “Post belongsTo User”. When i list all users with their posts with something like “$this->User->find(‘all’)” cakePhp will not generate a JOIN Sql command as wished, but 2 Sql commands : 1 select from users + 1 select from posts where user_id IN(1,2,3…).

    Is there a way to tell cake to create only 1 select only with a JOIN ?

    Many thanks for sharing your experience.

    1. @teknoid

      Thank you! I’m surprised we have to trick one application basic foundation (table relations) to achieve a very simple & common Sql Join. Writing “User hasOne Post” when in reality “User hasMany Posts” is not very natural/elegant/readable. hum.. mabye i’m not seeing a bigger picture, i’ll dig more before complaining :-) thanks for your help.

  7. @Lucas Renan

    No problemo ;)

    … and as I’ve mentioned a few times, it’s really not that hard. Just different and there are many different approaches to solve the same problem.

    Either way, once you get used to it, writing SQL JOIN’s (or any queries) by hand all while keeping track of all your ORM becomes a fading memory, rather than a recent nightmare.

  8. Wow… thanks a lot… it resolve my problem

    I had a search engine… users can search companies (by name, by country,etc… )

    The problem was that Companies hasMany Offices …

    When I searched by countries it was fine but when I putted just a company name, it gets me companies 3 times in the results if the company has 3 offices…

    in my controller I just write this…
    if($this->data[‘Company’][‘country_id’]){
    …. (the bind method trick)…
    }

    thanks a lot !

  9. Hey teknoid. First of all, thanks for all your great posts.

    I was wondering if you could publish how is the resulting array supposed to look like (a print_r) because I’m trying to do something similar and I’m not getting the desired results.

    Thanks a lot!

  10. Hmm… I never knew that this was a problem actually. But why?… I still don’t quite get what’s going on. I came to your post through Google after I suddenly found out that $hasAndBelongsToMany association doesn’t do all necessary joins to “automagically” retrieve data from one table that matches conditions in another. Why?… why?.. And maybe I’m wrong but it looks like it is not documented anywhere. At least like a thing that needs additional steps to work properly.

  11. @teknoid

    I’m totally new on cake platform, I could not get what I needed using HABTM and I read “You’ve probably noticed that by default CakePHP will only create a JOIN query if you have a hasOne or belongsTo associations between your models” sentence just in the beginning of this article, and I thought that HABTM doesn’t do any joins by default at all. You know, doesn’t even try and is not even meant to. But then, after hours of fighting with models, I figured out that it does, but breaks as soon as you insert some condition into the find from the remote table (not sure still if that’s totally true). After realizing that I unbinded HABTM association and binded couple hasOnes. And it finally worked. I should say that was not obvious, at least…

    In pure php it would have been matter of minutes to write proper sql and ran it. It is four or so days on cake and I feel bit disoriented and disappointed. Hope that won’t last too long.

  12. You said “It was never intended to work that way”. But what is intention behind HABTM then? Why I need it if I unbind it as soon as I need to use it?

  13. @jayarjo

    Well, it’s impolite to answer a question with a question, but how many days did it take you to learn pure php and sql? :) … and cake does a few other things besides sql queries…

    All joking aside, the way cake’s ORM was developed over the years possibly left a few “holes” to things otherwise seemingly simple.
    The simplest answer to “why not?” (and this is from what I’ve gathered, so may not be conclusive) is that it would require an extensive modification of the core, which is not a significant benefit at this point. There are many solutions to this problem and first and foremost that HABTM is simply a convenience for a hasOne/belongsTo chain…
    In the manual it mentions that you can easily rebuild what you have done via HABTM using more simple associations. (Or use some trickery, like explained here… or provide a patch to make it work for all)…

  14. Hey teknoid,

    You rock man! I’ve been so fade-up with cakePHP for this LEFT JOIN issue with multiple tables..
    force hasOne fixed it.
    Thanks a trillion buddy :)
    I’ve started loving cake again :D

    – Adnan

  15. hi
    i am new to cake . i want to fetch data from 2 tables only . Didnt get it going..

    Help will be appreciated

    Thanks in Advance

  16. SELECT d.vps_name, d.machine_type, d.ipAddress, d.rdp_user,d.rdp_pass, d.dc_user, d.dc_pass, d.status, d.user_id, d.order_id,u.email, u.fname, u.lname FROM details as d
    LEFT JOIN users as u
    ON d.user_id = u.id
    WHERE d.user_id = u.id

    this is the query that i want to make through cake pattern

    Thanks
    Mayank

  17. $this->Detail->find(‘first’, array(‘joins’ => array(
    array(
    ‘table’ => ‘users’,
    ‘alias’ => ‘u’,
    ‘type’ => ‘inner’,
    ‘foreignKey’ => false,
    ‘conditions’=> array(‘Detail.id = “‘.$id.'”‘,’u.id = Detail.user_id’)
    )
    )));
    Is there anything which can make this query better

  18. hello teknoid
    the query above is showing only the contents of one table ie details and not for users..
    any help will be appreciated..

    Thanks
    Amit

  19. @amit

    Well, “not working” doesn’t really tell me much.
    At any rate, it’s best to ask this question with more details at the google group or the IRC channel, since I have limited time to help out ;)

  20. @teknoid

    i have 2 tables details and users. i am getting Detail.id and getting the whole row for this id. and for this id i have user_id also . For that user_id i want to fetch data from users table .

    Thanks

  21. Hi….

    I want to use Left Outer Join in Cakephp…for example I have two tables in name Sysmenus and Syspermissions…common field is FuncCD
    This is the query in PHP:
    select sm.FuncCD, sm.TitleNM, sm.GroupNM, sp.Ins_dt from sysmenus as sm left outer join syspermissions as sp on sm.FuncCD = sp.FuncCD

    I dont know how to change this in Cake PHP…Pls reply as soon as possible…

  22. @Chisan

    What’s the difference if you have “OUTER”? I believe it is optional, and has no significant role, at least not in the example you gave.

  23. Man, this ‘false’ parameter at the end of bindModel() saved my paginator’s life. Thxs a lot.

  24. Very helpful read. It got me to look closer at the Cake docs, and I found this: http://book.cakephp.org/view/872/Joining-tables

    This seems to accomplish the same without having to use the unbind hack. I never really noticed the “join” option in Model::find before, but it worked in my case with a HasMany scenario. I assume it will work with HABTM as well, as that’s the example the page gives.

  25. @Brade

    Good point, but this post was written more than 2 years ago. I am almost positive that back then we didn’t have another choice.

    That being said, I should update the article…

    1. No prob. I’m sure this page brought awareness to the issue, given the number of comments and the fact that it turned up near the top when I googled: cakephp containable empty

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s