Zend_db junction table join query

Question

Hi guys,

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?

Answer 1

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?