使用 Zend Framework 进行 LIMIT 和 JOIN

发布于 2024-10-30 09:51:36 字数 737 浏览 1 评论 0原文

我有 2 个表parent、children，关系为 1:M。我需要一个分页。我将 SELECT 与 JOIN 一起使用。对于一位家长，我有一些孩子。如果我尝试设置LIMIT 10，此查询将总共获得 10 行。但我只需要从表 parent 中获取所有关系的 10 行。我该怎么做？
抱歉我的英语不好。预先感谢您。

Mysql查询：

SELECT `o`.`id` AS `order_id`, `od`.`id` AS `order_destination_id` FROM `order` AS `o`
 LEFT JOIN `order_destination` AS `od` ON o.id = od.order_id LIMIT 5

Zend框架：

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('o' => 'order'), array('order_id' => 'id'))
    ->joinLeft(array('od' => 'order_destination'), 'o.id = od.order_id', array('order_destination_id' => 'id'))
    ->limit(5);

原文

I have 2 tables parent, children with relation 1:M. I need a pagination. I use SELECT with JOIN. For 1 parent i have some children. If I try to make LIMIT 10 this query get total 10 rows. But i need to get only 10 rows from table parent with all relations. How can I do it?
Sorry for my english. Thank you in advance.

Mysql query:

SELECT `o`.`id` AS `order_id`, `od`.`id` AS `order_destination_id` FROM `order` AS `o`
 LEFT JOIN `order_destination` AS `od` ON o.id = od.order_id LIMIT 5

Zend framework:

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('o' => 'order'), array('order_id' => 'id'))
    ->joinLeft(array('od' => 'order_destination'), 'o.id = od.order_id', array('order_destination_id' => 'id'))
    ->limit(5);

分享到QQ

分享到微博

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

发布评论

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

故事未完 2024-11-06 09:51:36

我不知道 Zend 框架。这是一个简单的 SQL 解决方案：

SELECT `o`.`id` AS `order_id`, `od`.`id` AS `order_destination_id` FROM `order` AS `o`
LEFT JOIN `order_destination` AS `od` ON o.id = od.order_id 
where (select count(*) from order o2 where o2.id > o.id) < 10

I don't know about the Zend Framework. Here a plain SQL solution:

SELECT `o`.`id` AS `order_id`, `od`.`id` AS `order_destination_id` FROM `order` AS `o`
LEFT JOIN `order_destination` AS `od` ON o.id = od.order_id 
where (select count(*) from order o2 where o2.id > o.id) < 10

回复收藏 0 原文

冷清清 2024-11-06 09:51:36

最好使用 Zend_Paginator 对一个表进行分页，然后为每一项请求子项：

$paginator = Zend_Paginator::factory($this->select(), 'DbTableSelect');
$paginator->setItemsCountPerPage(5);
foreach ($paginator as $row) {
    $children = $row->getDependentRowset('ChildrenRule');
}

It's better to use Zend_Paginator to paginate through one table and then request children for every item:

$paginator = Zend_Paginator::factory($this->select(), 'DbTableSelect');
$paginator->setItemsCountPerPage(5);
foreach ($paginator as $row) {
    $children = $row->getDependentRowset('ChildrenRule');
}

回复收藏 0 原文

~没有更多了~