zen of coding

Forcing an SQL JOIN in CakePHP

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()

  • Chris Reddy

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

    Thanks a bunch!

  • teknoid

    @Chris Reddy

    Thanks, glad to hear it helped.

  • Your blog is interesting!

    Keep up the good work!

  • teknoid

    @AlexM

    Thanks ;)

  • Awesome, cheers for this. Exactly what I needed to limit my find query using a containable behaviour.

  • teknoid

    @Frank

    Good to hear it worked for you.

  • 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.

  • teknoid

    @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 :)

  • 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.

  • teknoid

    @josoroma

    Alright, good luck ;)

  • Thanks. That solved my problem :)

  • @Jacob Friis Saxberg

    Nice, glad it helped.

  • hai
    I want total sum of products as by count in query? or any other method. is it possible.
    piyush

  • @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…

  • Thanks a lot for this piece of info.

  • @Jeff

    No problemo ;)

  • 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.

  • @miccio

    This is exactly what this whole post is about, please read it again ;)

    • miccio

      @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.

  • @miccio

    No problem.

    There are other techniques to archive the same result. For example: http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find

    … but you are right, we are still “hacking” around the problem in a way. Although, given all the other benefits of cake’s ORM, I won’t complain about it too much :)

  • Thanx for your help,

    but I can’t belive that do a simple inner join is so complicated in cake…
    hahahah
    cake is fun =]

  • @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.

  • 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 !

  • @Sébastien G.

    Cool. I’m glad it helped.

  • Ceeram

    An other note on using this solution, updateAll won’t respect the condition, it will just join ON(1=1)

  • @Ceeram

    updateAll() is very different, you are correct. Please check the api, to see how that particular method works.

  • Ponch

    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!

  • Ceeram

    As of version 1.2.5 updateAll respects the condition from the association.

  • @Ceeram

    Thanks for the update… Good to know.

  • jayarjo

    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.

  • @jayarjo

    It was never intended to work that way. At least not for HABTM, try the behavior linked above and let me know if it solves your dilemma…

  • jayarjo

    @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.

  • jayarjo

    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?

  • @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)…

  • 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

  • @adnan

    Good to hear ;)

  • Mayank

    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

  • Mayank

    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

  • amit

    $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

  • amit

    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

  • @amit

    Check your sql debug. Does the query produced makes sense? Have you tried to run it directly against the DB?

  • amit

    @teknoid
    thanks for replying

    its not working thats why i am here to seek help

  • @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 ;)

  • amit

    @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

  • Hugo

    Dude, this stuff saved my life, lov u! S2

  • @Hugo

    Thanks :)

  • Chisan

    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…

  • @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.

  • Hugo

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

    • Hugo

      lol now i noticed that this same post saved my life TWICE! you’re my hero!!

  • @ Hugo

    ;)

    Cheers

  • Pingback: Unit testing in CakePHP — the missing manual and a step-by-step tutorial | Code for Concinnity()

  • 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.

  • @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…

    • 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

  • Vince

    Exactly what I was looking for, thanks!

  • Harris

    I have a model Publisher which hasMany Content. I want to find all Publishers that have at least one content.
    I did the trick with hasOne but then data are returned as:
    [0]=>array(
    ‘Publisher’ => array(‘name’ => ‘name1’),
    ‘Content’ => array (‘Content.id’ => id1)),
    [1]=>array(
    ‘Publisher’ => array(‘name’ => ‘name1’),
    ‘Content’ => array (‘Content.id’ => id2)),
    [2]=>array(
    ‘Publisher’ => array(‘name’ => ‘name2’),
    ‘Content’ => array (‘Content.id’ => id3))
    )

    Is there a way to combine those data (that is combine element [0] and [1] which have the same publisher)??

  • teknoid

    @Harris

    You need to use the SQL “HAVING” clause, a little googling should give you an idea on how to handle this.

    I also having post about “habtm and join trickery”, which kind of shows what you might need.

  • mlay

    @teknoid:

    Why just dont make a database-view!?
    Its an easy way to get all the attributes you want to print out in a CakePHP-View. ContainingBehaviour and binding-trickery ist just inconvenient in my oppinion.

    What do you think about that, teknoid?

  • teknoid

    @mlay

    There is an easier way to handle this now by using the “joins” key.
    I don’t see how making a view would work, first of all I would have to make the view ahead of time (based on the query).

    How would that work in real-time?

  • mlay

    @teknoid

    I posted my example here
    http://groups.google.com/group/cake-php/browse_thread/thread/9953a8af917d8587#

    I would be very happy, if you could read it and maybe tell me, if it would work with “joins”. And if it works, than how?

    Thanks ;)

  • mlay

    PS: I am Paul Bricks

  • mlay

    ???

  • teknoid

    @mlay

    Your example can easily work in many cake ways. “joins” is one example, the on this post as well.
    Figure out what SQL you need exactly, and work backwards.

    If you must (and I mean must) write straight SQL, escape everything properly and place it in your model.

  • Pingback: Re: How to paginate a table with a condition from second association field | DeveloperQuestion.com()

  • Junaid

    Exactly what I was looking for, thanks! everyone,
    i achieved what i was trying to do.

  • panter4

    i ve been working on a complex query for the last 8 hours with no progress untill i found this trick

    THANK YOU!!!

  • Thanks a lot man! You really saved my day! Keep bloggin’ bro!

%d bloggers like this: