My Circumstance: php 5.1.6 cakephp 1.3
I have a Restaurant table whose structure is like bellow.
id name
1 Italian_resto
2 French_resto
3 Japanese_resto
I also have a RestaurantsTag table whose structure is like bellow.
id restaurant_id type tag
1 1 area Firenze
2 1 genre Italian
3 2 area Paris
4 2 genre French
5 3 area Tokyo
6 3 genre Japanese
These two table can be joined by Restaurant.id and RestaurantsTag.restaurant_id. Then, I'd like to get all the tags which belong to Italian_resto when I search tags by "Italian". In other words, when I search tag by 'Italian', I'd like to get 'Italian' and 'Firenze' whose restaurant_ids are both 1 . The result I want is bellow.
array(1) {
[0]=>
array(2) {
["Restaurant"]=>
array(4) {
["id"]=>
string(1) "1"
["name"]=>
string(27) "Italian_resto"
}
["RestaurantsTag"]=>
array(2) {
[0]=>
array(2) {
["type"]=>
string(5) "genre"
["tag"]=>
string(12) "Italian"
}
[1]=>
array(2) {
["type"]=>
string(4) "area"
["tag"]=>
string(6) "Firenze"
}
}
}
}
The result bellow is also welcome.
array(1) {
[0]=>
array(2) {
["Restaurant"]=>
array(4) {
["id"]=>
string(1) "1"
["name"]=>
string(27) "Italian_resto"
}
["RestaurantsTag"]=>
array(2) {
[0]=>
array(2) {
["type"]=>
string(5) "genre"
["tag"]=>
string(12) "Italian"
}
}
}
[1]=>
array(2) {
["Restaurant"]=>
array(4) {
["id"]=>
string(1) "1"
["name"]=>
string(27) "Italian_resto"
}
["RestaurantsTag"]=>
array(2) {
[0]=>
array(2) {
["type"]=>
string(5) "area"
["tag"]=>
string(12) "Firenze"
}
}
}
}
How can I achieve that by one query in cakephp 1.3? If It is not possible, how can I achieve that by the simplest queries?
Based on the required input and output something like this is possibly what you want.
SELECT rt.res_id, rt.`type`, rt.tag, r.res_name FROM
restaurants_tag rt
JOIN
(SELECT res_id FROM restaurants_tag WHERE tag="Italian") d
ON d.res_id = rt.res_id
JOIN restaurant r ON d.res_id = r.id;
|res_id |type |tag |res_name
|-------|-------|-----------|------------
|1 |area |Firenze |Italian_resto
|1 |genre |Italian |Italian_resto
Feel free to revert if this is not what you want.
One way is to start with something like this:
SELECT a.restaurant_id
, b.name
, MAX(CASE WHEN a.type = 'area' THEN tag END) area
, MAX(CASE WHEN a.type = 'genre' THEN tag END) genre
FROM restaurant_tags a
JOIN restaurants b
ON b.id = a.restaurant_id
GROUP
BY a.restaurant_id;
or
SELECT x.restaurant_id
, x.tag area
, y.tag genre
FROM restaurant_tags x
LEFT
JOIN restaurant_tags y
ON y.restaurant_id = x.restaurant_id
AND y.type = 'genre'
JOIN restaurants r
ON r.id = x.restaurant_id
WHERE x.type = 'area';
This particular version of this query assumes that area will always be present, but it can be rewritten if that's not the case