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.

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 4

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