Zend_db 连接表连接查询

发布于 2024-08-21 03:42:54 字数 552 浏览 12 评论 0原文

不知道为什么我无法弄清楚这一点。基本上，我有两个具有多对多关系的表，因此它们之间有一个连接表。

例如，考虑以下数据库模式：

Restaurant (id, restaurant_name, suburb)

RestaurantCuisine (restaurant_id, cuisine_id)

Cuisine (id, cuisine_name)

因此，许多餐馆可以有多种菜系。

我试图构建的查询将返回郊区中存在的所有美食。 SQL 示例如下：

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

这对我来说似乎很有意义。

我如何使用 Zend_Db 来实现这个？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

王权女流氓 2024-08-28 03:42:55

这是 Zend_Db_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%');

结果：

选择“RestaurantCuisine”。“cuisine_name”
来自“餐厅美食”
内连接“Cuisine”ON Cuisine.id = RestaurantCuisine.cuisine_id
INNER JOIN“餐厅”ON Restaurant.id = RestaurantCuisine.restaurant_id
WHERE（郊区，如“%x%”）

您说查询运行缓慢。您是否正确配置了主键和索引？

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?

回复收藏 0 原文

淡淡の花香 2024-08-28 03:42:55

我遇到了同样的问题，像这样的查询运行速度非常慢，我认为问题在于 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%');

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

回复收藏 0 原文

~没有更多了~