CODEWITHSUNDEEP
phpmysqlzend-frameworkselectjoin
Alex Pliutau
Alex Pliutau

Reputation: 21947

LIMIT with JOIN using Zend Framework


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

Upvotes: 1

Views: 3439

Answers (2)

Ololo
Ololo

Reputation: 1097

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

Upvotes: 1

nang
nang

Reputation: 431

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

Upvotes: 1

Related Questions