CakePHP nested query: select from another select statement

Question

I am trying to do the following nested select statement with CakePHP buildStatement function but to no avail.

select * from 
(
    select i.id, i.name Item, sum(t.qty) Total, l.name Location 
    from transactions t 
    left join locations l on (l.id = t.location_id) 
    left join items i on (i.id = t.item_id) 
    where  t.item_id = 855  
    group by location_id ) 
filter where total <> 0

I hope some expert can help with this!

My try ashamed:

$subqueryOptions = array(
    'fields' => array('Item.id', 'Item.name', 'SUM(Transaction.qty) total', 'Location.name'), 
    'conditions' => array(
        'Transaction.item_id'=>855,
    ),
    'joins'=>array(
        'Location', 
        'Item'
    ),
    'group'=>array(
        'Transaction.location_id'
    ),
    'table'=>'transactions Transaction'
);
$db = $this->Transaction->getDataSource();
$subQuery = $db->buildStatement($subqueryOptions, $this->Transaction);
$res = $this->Transaction->find('all', array(
    'fields'=>$subQuery,
    'conditions' => array('total !='=>0)
));`

resulting statement:

SELECT `SELECT Item.id`, `Item`.`name`, SUM(`Transaction`.`qty`) total, `Location.name
FROM transactions Transaction AS Location Item
WHERE `Transaction`.`item_id` = 855
GROUP BY `Transaction`.`location_id``
FROM `biruni-inventory`.`transactions` AS `Transaction`
LEFT JOIN `biruni-inventory`.`transaction_types` AS `TransactionType`
    ON (`Transaction`.`transaction_type_id` = `TransactionType`.`id`)
LEFT JOIN `biruni-inventory`.`item_conditions` AS `ItemCondition`
    ON (`Transaction`.`item_condition_id` = `ItemCondition`.`id`)
LEFT JOIN `biruni-inventory`.`locations` AS `Location`
    ON (`Transaction`.`location_id`= `Location`.`id`)
WHERE `total` != 0

which is totally irrelevant!

man i am even ashamed of putting my try! but will edit and post it — mmahgoub
– mmahgoub, Commented Sep 14, 2014 at 11:03
OK! i think i did bunch of things wrong! first i forgot the alias! — mmahgoub
– mmahgoub, Commented Sep 14, 2014 at 11:14

mmahgoub · Accepted Answer · 2014-09-14 12:09:54Z

This is the best i could do to get cake to build my subquery here is it:

$this->loadModel('MyDBSchema.Item');
        $db = $this->Transaction->getDataSource();
        $db2 = $this->Item->getDataSource();
       $subqueryOptions = array(
           'fields' => array('`Item`.`id` Item_id', '`Item`.`name` item_name', 'SUM(`Transaction`.`qty`) total_count', '`Location`.`name` location_name'), 
           'conditions' => array(
               'Transaction.item_id'=>array(855, 901),
           ),
           'joins'=>array(
               'left join '.$db->fullTableName($this->Transaction->Location) .' `Location` on (`Location`. `id` = `Transaction`.`location_id`)', 
               'left join ' .$db2->fullTableName($this->Item). ' `Item` on (`Item`.`id` = `Transaction`.`item_id`)',
           ),
           'group'=>array(
               'Transaction.location_id'
           ),
           'table'=>$db->fullTableName($this->Transaction),
           'alias'=>'Transaction'
           );

        $subQuery = $db->buildStatement($subqueryOptions, $this->Transaction);

        $subQueryExpression = $db->expression($subQuery);

        $mainQueryOptions = array(
            'fields'=>array('item_name','total_count','location_name'),
           'conditions' => array(
               'total_count <>'=>0,
           ),
           'table'=>'('.$subQueryExpression->value.')',
           'alias'=>'Item'
           );

        $mainQuery = $db->buildStatement($mainQueryOptions, $this->Transaction);
        $res = $this->Transaction->query($mainQuery);
        debug($res); die;

Which results in something like:

array( (int) 0 => array( 'Item' => array( 'item_name' => 'الجزيرة.. نظرة رمادية إلى أفريقيا', 'total_count' => '4', 'location_name' => '003-D' ) ), (int) 1 => array( 'Item' => array( 'item_name' => 'أصحاب الحق', 'total_count' => '2', 'location_name' => '003-H' ) ) )

This works great for me.. I hope if someone has a better solution please let us know!

Thanks

Collectives™ on Stack Overflow

CakePHP nested query: select from another select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related