如何通过join table的单词进行搜索，并通过cakephp从连接表中获取所有相关数据

Question

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?

Answer 1

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.