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'
    )
);

Werra

Answer 1

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.

Sound

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! – Werra

@Werra - 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? – Sound

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? – Werra

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. – Sound

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 – Werra

(as you can see - for some reason it has "cuisines Cuisine Array..." in the actual query...seems very odd) – Werra

hmm. Yeah that is kind of weird. I'll double check my syntax. – Sound

could you post the full query that it's generating? – Sound

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 – Werra

the manual page also says you need to set recursive to -1. did you try that? – Sound

If I set recursive to -1, I'll loose all the rest of my data, won't I? ie "Features", "Users", "Images"...etc – Werra

or maybe try giving your manual JOIN table a different alias - Cuisine might be conflicting with the regular Cuisine table. – Sound

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 – Werra

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. – Sound

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 – Werra

Changing alias also had no change. – Werra

it looks like I may have forgotten an array - it should be something like $join = array(array(....));. I'll edit my answer. – Sound

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! – Werra

Hey, glad to hear it. I added Cuisine.name = "italian" - is that the way you have it? – Sound

You got it! Thanks again for all help and super-fast responses! – Werra

Answer 2

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'));

Testosterone

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! – Peek

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! – Werra

It doesn't seem to be working - it's not even adding "italian" anywhere in the queries :( – Werra

I'll be away for the weekend, but I can take a look for you on monday. I'll keep you posted. – Testosterone

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? – Werra

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. – Testosterone

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 ;-) – Testosterone

Answer 3

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!

Morisco

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. – Werra

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 – Werra

try adding more info to the habtm relation: information such as 'joinTable','foreignKey' and 'associationForeignKey' – Morisco

Is there a reason to do that? I was under the impression that was excessive/unnecessary if you name everything correctly. – Werra

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? – Werra

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. – Werra

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 – Morisco

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. – Werra

Answer 4

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

Lange

Right... I think that's the obvious part - the question is how to add it or get it to work via CakePHP. – Werra

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? – Lange

Thank you very much for your attempt at helping. This question has now been answered. – Werra

Answer 5

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.

Ipoh

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. – Werra