CakePHP - problem with HABTM paginate query

Question

Tables

restaurants
cuisines
cuisines_restaurants

Both restaurant and cuisine model are set up to HABTM each other.

I'm trying to get a paginated list of restaurants where Cuisine.name = 'italian' (example), but keep getting this error:

1054: Unknown column 'Cuisine.name' in 'where clause'

Actual query it's building:

SELECT `Restaurant`.`id`, `Restaurant`.`type` ..... 
`Restaurant`.`modified`, `Restaurant`.`user_id`, `User`.`display_name`,
`User`.`username`, `User`.`id`, `City`.`id`,`City`.`lat`  ..... 
FROM `restaurants` AS `Restaurant` LEFT JOIN `users` AS `User` ON 
(`Restaurant`.`user_id` = `User`.`id`) LEFT JOIN `cities` AS `City` ON 
(`Restaurant`.`city_id` = `City`.`id`) WHERE `Cuisine`.`name` = 'italian' 
LIMIT 10

The "....." parts are just additional fields I removed to shorten the query to show you.

I'm no CakePHP pro, so hopefully there's some glaring error. I'm calling the paginate like this:

$this->paginate = array(
    'conditions' => $opts,
    'limit' => 10,
);
$data = $this->paginate('Restaurant');
$this->set('data', $data);

$opts is an array of options, one of which is 'Cuisine.name' => 'italian'

I also tried setting $this->Restaurant->recursive = 2; but that didn't seem to do anything (and I assume I shouldn't have to do that?)

Any help or direction is greatly appreciated.

EDIT

models/cuisine.php
    var $hasAndBelongsToMany = array('Restaurant');

models/restaurant.php
    var $hasAndBelongsToMany = array(
    'Cuisine' => array(
        'order' => 'Cuisine.name ASC'
    ),
    'Feature' => array(
        'order' => 'Feature.name ASC'
    ),
    'Event' => array(
        'order' => 'Event.start_date ASC'
    )
);

score 6 · Answer 1 · edited Apr 19 '11 at 21:25

6

As explained in this blogpost by me you have to put the condition of the related model in the contain option of your pagination array.

So something like this should work

# in your restaurant_controller.php
var $paginate = array(
    'contain' => array(
        'Cuisine' => array(
            'conditions' => array('Cuisine.name' => 'italian')
        )
    ),
    'limit' => 10
);

# then, in your method (ie. index.php)
$this->set('restaurants', $this->paginate('Restaurant'));

edited Apr 19 '11 at 21:25

Chuck Burgess

11,600
5
41
74

answered Apr 19 '11 at 21:02

vindia

1,678
10
14

Excellent solution. I updated your post to include the array on your `var $paginate`. I have confirmed this works with some test code. Very well done! – Chuck Burgess Apr 19 '11 at 21:26
It sounds like this is exactly what I want - I'll try it out today or tomorrow and mark it as the answer when I get it to work - thanks! – Dave Apr 21 '11 at 14:22
It doesn't seem to be working - it's not even adding "italian" anywhere in the queries :( – Dave Apr 21 '11 at 20:29
I'll be away for the weekend, but I can take a look for you on monday. I'll keep you posted. – vindia Apr 22 '11 at 08:00
According to declan (answer below) the contain doesn't work with paginate. I hope he's wrong, but - your blog doesn't mention paginate, and I can't get it to work, so I'm inclined to think he's right - any thoughts? – Dave Apr 25 '11 at 17:08
I think he's right yeah, but you should be able to do it with a cleaner join than the one declan proposes. My blogpost is not about paginate no, but about using Containable in HABTM relations to get related data. I was under the assumption that it would work the same way for paginate, but apparently it doesn't. – vindia Apr 25 '11 at 21:27
I've set up a little test app and filled it with some data and when I use my method, Cake (1.3.8) will generate the right query `SELECT * FROM 'cuisines' AS 'Cuisine' JOIN 'restaurants_cuisines' AS 'RestaurantsCuisine' ON ('RestaurantsCuisine'.'restaurant_id' IN (1, 2, 3, 4, 5) AND 'RestaurantsCuisine'.'cuisine_id' = 'Cuisine'.'id') WHERE 'Cuisine'.'name' = 'Italian'`. This query does give the right result when entered in mysql, but for some reason Cake adds the restaurants that shouldn't be there anyway. But I see you've already figured out another way with declan's answer ;-) – vindia Apr 26 '11 at 10:55

declan · Accepted Answer · 2011-04-25T20:09:42.320

5

This fails because Cake is actually using 2 different queries to generate your result set. As you've noticed, the first query doesn't even contain a reference to Cuisine.

As @vindia explained here, using the Containable behavior will usually fix this problem, but it doesn't work with Paginate.

Basically, you need a way to force Cake to look at Cuisine during the first query. This is not the way the framework usually does things, so it does, unfortunately, require constructing the join manually . paginate takes the same options as Model->find('all'). Here, we need to use the joins option.

var $joins = array(
    array(
        'table' => '(SELECT cuisines.id, cuisines.name, cuisines_restaurants.restaurant_id
                 FROM cuisines_restaurants 
                 JOIN cuisines ON cuisines_restaurants.cuisines_id = cuisines.id)',
        'alias' => 'Cuisine',
        'conditions' => array(
            'Cuisine.restaurant_id = Restaurant.id',
            'Cuisine.name = "italian"'
        )
    )
);

$this->paginate = array(
    'conditions' => $opts,
    'limit' => 10,
    'joins' => $joins
);

This solution is a lot clunkier than the others, but has the advantage of working.

edited Apr 25 '11 at 20:09

answered Apr 25 '11 at 07:39

declan

5,605
3
39
43

I can already get the cuisine name to come up in my pagination results without using any hand-written JOINs - the problem is in trying to order by a cuisine field. Does your solution work for this? If so - any chance you could edit your answer / explain how? Thanks ahead of time! – Dave Apr 25 '11 at 17:10
@Dave - In your question it still says that the whole query is failing with error `Unknown Column`. Is the problem that Cuisine shows up but isn't ordered correctly, or that the whole query breaks when you try to order by Cuisine? – declan Apr 25 '11 at 18:09
CakePHP creates a lot of queries when paginate() is run based on table relationships. One of those queries pulls the necessary cuisine data (so the list of restaurants shows their respective cuisine next to their name). The problem is, I need to retrieve restaurants based on their cuisine - the current query that actually pulls the restaurants that I attempted to do that gives the error because Cuisine is not included in THAT query. The generic Cuisine data IS pulled just fine in another query. I need something like "WHERE Cuisine.name='italian'" Does that clarify, or just confuse more? – Dave Apr 25 '11 at 18:14
Ok, that makes sense. So yes, the solution above will help you out, I think. The `joins` option will force whatever tables you specify to be joined to the _first_ query that paginate runs - i.e. the one that currently doesn't contain Cuisine. – declan Apr 25 '11 at 18:28
I cannot get your answer to work without SQL errors - one of which is: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cuisines Cuisine Array LEFT JOIN `users` AS `User` ON (`Restaurant`.`user_id` = ' at line 1 – Dave Apr 25 '11 at 19:23
(as you can see - for some reason it has "cuisines Cuisine Array..." in the actual query...seems very odd) – Dave Apr 25 '11 at 19:24
hmm. Yeah that is kind of weird. I'll double check my syntax. – declan Apr 25 '11 at 19:26
could you post the full query that it's generating? – declan Apr 25 '11 at 19:27
One thing I noticed (and tried changing) was - the "Cuisines" table does not have a restaurant_id field, which you're referencing in the 'conditions' - I'll get the query and post in 30 sec – Dave Apr 25 '11 at 19:30
the [manual page](http://book.cakephp.org/view/1323/Containable#!/view/1047/Joining-tables) also says you need to set recursive to -1. did you try that? – declan Apr 25 '11 at 19:32
If I set recursive to -1, I'll loose all the rest of my data, won't I? ie "Features", "Users", "Images"...etc – Dave Apr 25 '11 at 19:33
or maybe try giving your manual JOIN table a different alias - Cuisine might be conflicting with the regular Cuisine table. – declan Apr 25 '11 at 19:34
The first query: SELECT COUNT(*) AS `count` FROM `restaurants` AS `Restaurant` (SELECT cuisines.id, cuisines.name, cuisines_restaurants.restaurant_id FROM cuisines_restaurants JOIN cuisines ON cuisines_restaurants.cuisine_id = cuisines.id) Cuisine cuisines_restaurants.restaurant_id = Restaurant.id LEFT JOIN `users` AS `User` ON (`Restaurant`.`user_id` = `User`.`id`) LEFT JOIN `cities` AS `City` ON (`Restaurant`.`city_id` = `City`.`id`) WHERE 1 = 1 – Dave Apr 25 '11 at 19:35
ah, well, if you set recursive to -1 and that makes it work, you should be able to include the fields you want with Containable. – declan Apr 25 '11 at 19:35
2nd: SELECT `Restaurant`.`id`, ... `user_id`, `User`.`display_name`, `User`.`username`, `User`.`id`, `City`.`id`, `City`.`name`, ... `City`.`lat` FROM `restaurants` AS `Restaurant` (SELECT cuisines.id, cuisines.name, cuisines_restaurants.restaurant_id FROM cuisines_restaurants JOIN cuisines ON cuisines_restaurants.cuisine_id = cuisines.id) Cuisine cuisines_restaurants.restaurant_id = Restaurant.id LEFT JOIN `users` AS `User` ON (`Restaurant`.`user_id` = `User`.`id`) LEFT JOIN `cities` AS `City` ON (`Restaurant`.`city_id` = `City`.`id`) WHERE 1 = 1 ORDER BY `Restaurant`.`name` ASC LIMIT 10 – Dave Apr 25 '11 at 19:36
Changing alias also had no change. – Dave Apr 25 '11 at 19:41
it looks like I may have forgotten an array - it should be something like `$join = array(array(....));`. I'll edit my answer. – declan Apr 25 '11 at 19:45
That worked!!! If you want to add the: 'Cuisine.name = "italian"' part to the conditions in $joins, I'll mark it as the answer! Thank you SO much! – Dave Apr 25 '11 at 19:53
Hey, glad to hear it. I added `Cuisine.name = "italian"` - is that the way you have it? – declan Apr 25 '11 at 20:10
You got it! Thanks again for all help and super-fast responses! – Dave Apr 25 '11 at 20:31

score 0 · Answer 3 · answered Apr 13 '11 at 21:43

0

a few ideas on the top of my mind:

have you checked the model to see if the HABTM is well declared?
try using the containable behavior
in none of those work.. then you could always construct the joins for the paginator manually

good luck!

answered Apr 13 '11 at 21:43

pleasedontbelong

19,542
12
53
77

I assume my HABTM are correct (added them to the original post). As for the rest, I'd rather not have to settle on a work-around - I'd like to get it working correctly the way it's supposed to. – Dave Apr 13 '11 at 21:59
When I do a normal query, without the "Cuisine.name" => "italian", it works fine - my Cuisines show up on the page by referencing $restaurant['Cuisine'] in a foreach($data as $restaurant) loop – Dave Apr 13 '11 at 22:05
try adding more info to the habtm relation: information such as 'joinTable','foreignKey' and 'associationForeignKey' – pleasedontbelong Apr 13 '11 at 22:06
Is there a reason to do that? I was under the impression that was excessive/unnecessary if you name everything correctly. – Dave Apr 13 '11 at 22:10
Tried - same problem. I can SEE the issue in the query - it's not joining on Cuisine at all, so how can it get Cuisine.name? – Dave Apr 13 '11 at 22:15
I find it hard to believe that this is an issue in Cake - it's a pretty simple task - I have to be doing something wrong. – Dave Apr 13 '11 at 22:16
well... as you can see, Cake is not perfect :P i suggest you to create the joins in the ´$this->paginate´ variable or use the containable behavior – pleasedontbelong Apr 13 '11 at 22:17
I appreciate your help. Just because it doesn't work for me, does not prove that it's a Cake error - I'm not ruling it out, but I find it a lot more likely that it's an error on my part. I'll try the joins while I wait for another answer :) I appreciate your help. – Dave Apr 13 '11 at 22:20

score 0 · Answer 4 · answered Apr 20 '11 at 18:05

0

Cuisine must be a table (or alias) on the FROM clausule of your SELECT. so the error:
1054: Unknown column 'Cuisine.name' in 'where clause'
Is just because it isn't referenced on the FROM clausule

answered Apr 20 '11 at 18:05

Saic Siquot

6,513
5
34
56

Right... I think that's the obvious part - the question is how to add it or get it to work via CakePHP. – Dave Apr 20 '11 at 18:51
I will give you only workarounds and questions: has CakePHP a way to accept a raw sql statment? can you refer a view? why does it generate "left joins" to 'cities' and 'users'? where are them refered? – Saic Siquot Apr 25 '11 at 20:40
Thank you very much for your attempt at helping. This question has now been answered. – Dave Apr 25 '11 at 21:13

score 0 · Answer 5 · answered Apr 23 '11 at 22:40

0

If you remove the Feature and Event part of your HABTM link in the Restaurant model, does it work then? Sounds to me like you've failed to define the right primary and foreing keys for the Cuisine model, as the HABTM model is not even including the Cuisine tabel in the query you posted here.

answered Apr 23 '11 at 22:40

Karel

179
5

I'm 95% sure my relationships are correct - I am getting the Cuisine name in my pagination results just fine - it's being run in a separate query (created by Cake). The problem is, the way Cake is doing it, (running separate queries to get the related info) doesn't allow me to retrieve restaurants by a given cuisine. – Dave Apr 25 '11 at 17:14

CakePHP - problem with HABTM paginate query

5 Answers5

Linked