Subquery in a collection

Question

I am trying to do a query of the form:

SELECT TableA.id, 

    (
    SELECT MAX(TableB.qty) FROM TableB WHERE TableB.parent_id=TableA.id
    )

FROM TableA

the important thing being that the select clause has a subquery.

My question is, how would you use joinAttribute() or other ZendDB functions to implement the subquery? And this is important: I CANT DO A JOIN ON THE OUTER TABLE!!! It must be a subquery like that. This is a simplified version.

I am trying to use a collection's getSelect(), column(), addAttributeToSelect() etc functions to do this. Is this possible? This is for use within an _initSelect() call.

score 6 · Answer 1 · answered Aug 10 '13 at 17:18

6

You can use Zend_Db_Exp, and input your query in there like this:

$subquery = new Zend_Db_Expr('SELECT MAX(TableB.qty) FROM TableB WHERE TableB.parent_id=TableA.id');
$query = Mage::getSingleton('core/resource')->getConnection('core_read')
    ->from('TableA', array('id', $subquery));

For sure it's not best practice, and not very flexible, but you can give it a try.

answered Aug 10 '13 at 17:18

fmrng

3,398
19
19

Magento themselves use this method - I wouldn't say it's not best practice. https://github.com/blissmedia/magento-mirror-1.7.0.2/blob/master/app/code/core/Mage/Reports/Model/Resource/Event.php#L108 – philwinkle Aug 10 '13 at 18:42
1

I know they use it extensively, what I meant there is that there might be a better way to do it than just writing plain SQL – fmrng Aug 10 '13 at 18:55
blissmedia-github link is dead. This reference is better https://github.com/OpenMage/magento-mirror/blob/magento-1.9/app/code/core/Mage/Reports/Model/Resource/Event.php#L108 – ahe_borriglione Aug 16 '18 at 14:27

score 4 · Answer 2 · edited May 23 '17 at 12:37

joinAttribute is no Zend_Db method, it is a magento method, therefore you can not use it here.

If you can not do a join on the outer table, there might be a problem with your data design.

To make a subquery you need a second select object. Then you can get the select from the collection and put the one in the other. Zend_Db_Select has a __toString method, so the subquery will just be converted to string and concatenated into the outer query.

$subquery = Mage::getSingleton('core/resource')->getConnection('core_write');->select()

$collection->getSelect()->where("myAttribute = $subquery") // example

more: https://stackoverflow.com/questions/3221285/zend-db-subquery

score 2 · Answer 3 · answered Nov 03 '14 at 15:27

This is what I used, it worked for me.

First make the query for your max :

$collection = Mage::getModel('key/model')->getCollection();         
$collection->getSelect()
           ->reset(Zend_Db_Select::COLUMNS)
           ->columns(array('id' => 'MAX(`main_table`.id)'))
           ->group('customer_id');
$select = $collection->getSelect();

Then create the main query and update the "from" part :

$coll = Mage::getModel('key/model')->getCollection();
$coll->getSelect()
     ->reset(Zend_Db_Select::COLUMNS)
     ->reset(Zend_Db_Select::FROM)
     ->from($select, 'id') // Using from with Zend_Db_Select as name auto uses "t" as table alias
     ->join(
          array('main_table' => $collection->getTable('key/model')),
          '`t`.id = `main_table`.id', 
          array('*')
     );

This generates a query like this :

SELECT `t`.`id`, `alias`.* FROM (SELECT MAX(`main_table`.id) AS `id` FROM `tablename` AS `main_table` GROUP BY `customer_id`) AS `t` INNER JOIN `tablename` AS `main_table` ON `t`.id = `main_table`.id

This way the collection you get is from your collection classes :

get_class($coll); // (your collection class) -> Mage_Core_Model_Mysql4_Collection_Abstract
get_class($coll->getFirstItem()); // (your model class) -> Mage_Core_Model_Abstract

You can then use that collection for a Grid in the backend (I used 'main_table' as an alias for the joined table for my grid because all needed information are in there).

(in your case just stop at the from part)

score 1 · Answer 4 · answered Aug 31 '17 at 22:20

while I was working on a project I just came to this solution. Hope this will help

$collection = Mage::getModel('module/tableone')->getCollection();
$subquery = Mage::getModel('module/tabletwo')->getCollection();
$subquery->addFieldToSelect('tb2col1');

$collection->addFieldToFilter('main_table.tb1col2', 
        array('nin' => $subquery->getSelect())
        );

This will result following query

SELECT `main_table`.* FROM `tableone` AS `main_table` WHERE (`main_table`.`tb1col2` NOT IN((SELECT `main_table`.`tb2col1` FROM `tabletwo` AS `main_table`)))

Subquery in a collection

4 Answers4

Linked