3

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.

5

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.

4

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

2

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)

  • +1 for ->from($select, ...) and the comment – jacek.ciach Sep 27 '17 at 12:09
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.