Zend_db junction table join query

Question

Not sure why I can't figure this one out. Basically, I have two tables with a many-to-many relationship so I have a junction table inbetween them.

For an example, consider the following database schema:

Restaurant (id, restaurant_name, suburb)

RestaurantCuisine (restaurant_id, cuisine_id)

Cuisine (id, cuisine_name)

So, many restaurants can have many cuisines.

The query I am trying to construct will return all the cuisines that exist in a suburb. A SQL example is as follows:

SELECT cuisine_name
FROM CuisineRestaurant
JOIN Cuisine ON Cuisine.id = CuisineRestaurant.cuisine_id
JOIN Restaurant ON Restaurant.id = CuisineRestaurant.restaurant_id
WHERE suburb LIKE '%x%';

This seems to make sense to me.

How do I do implement this using Zend_Db?

Hi Gordon, After much trial-and-error, I came up with this. It returns the correct data, but appears to run quite slowly: $select = $this->select() ->setIntegrityCheck(false) ->distinct() ->from('CuisineRestaurant', array()) ->join('Cuisine', 'Cuisine.id = CuisineRestaurant.cuisine_id', array('id', 'cuisine')) ->join('Restaurant', 'Restaurant.id = CuisineRestaurant.restaurant_id', array()) ->where('Restaurant.suburb = ?', $suburb) ->order('Cuisine.cuisine'); — , Feb 08 '10 at 23:07

score 3 · Accepted Answer · answered Feb 09 '10 at 11:12

Here's the Zend_Db_Select query version:

$select = Zend_Db_Table::getDefaultAdapter()->select();

$select->from('RestaurantCuisine', 'cuisine_name')
    ->join('Cuisine', 'Cuisine.id = RestaurantCuisine.cuisine_id', array())
    ->join('Restaurant', 'Restaurant.id = RestaurantCuisine.restaurant_id', array())
    ->where('suburb LIKE ?', '%x%');

The result:

SELECT "RestaurantCuisine"."cuisine_name"
FROM "RestaurantCuisine"
INNER JOIN "Cuisine" ON Cuisine.id = RestaurantCuisine.cuisine_id
INNER JOIN "Restaurant" ON Restaurant.id = RestaurantCuisine.restaurant_id
WHERE (suburb LIKE '%x%')

You said that the query runs slow. Do you have primary keys and indexes configured correctly?

Thank you very much for that Luiz. I will try this soon. I have indexes on all of my searchable and joined fields. — , Feb 10 '10 at 06:18
Your solution worked. The slow query was due to the server that I was developing on. I have moved to a new server and it is blazingly fast. Thanks Luiz. — , Feb 11 '10 at 10:35

score 0 · Answer 2 · answered Oct 25 '13 at 05:04

I got same problem the query like this run very slow, and I think problem is with how is made by zend.

$select = Zend_Db_Table::getDefaultAdapter()->select();

      $select = Zend_Db_Table::getDefaultAdapter()->select();

 $select->from('RestaurantCuisine', 'cuisine_name')
->join('Cuisine', 'Cuisine.id = RestaurantCuisine.cuisine_id', array())
->join('Restaurant', 'Restaurant.id = RestaurantCuisine.restaurant_id', array())
->where('suburb LIKE ?', '%x%');

Zend_db junction table join query

2 Answers2