6

1) How to write the below SQL query in Magento collection :

SELECT * FROM 'table1' WHERE active=0 AND '2014-07-08' between 'not_available_from_date' and 'not_available_to_date'

2) Also how to write this SQL query in Magento Collection :

Select sum(qty + product_id - any_other_columns) as total from table2  GROUP BY product_id; 

Without using ->columns(' ') function.

Thanks for your help

5

Assuming you have a model mapped to your table1 and one mapped to table2 you can try something like this.

$date = '2014-07-08';
$collection = Mage::getModel('[module]/table1')->getCollection()
    ->addFieldToFilter('active', 0)
    ->addFieldToFilter('not_available_from_date', array('date' => true, 'to' => $date))
    ->addFieldToFilter('not_available_to_date', array('date' => true, 'from' => $date));

And for the second one.

$collection = Mage::getModel('[module]/table2')->getCollection()
    ->addExpressionFieldToSelect('total', 'SUM({{qty}} + {{product_id}} - {{any_other_columns}})', array('{{qty}}'=>'qty', '{{product_id}}'=>'product_id', '{{any_other_columns}}'=>'any_other_columns'));
//since SUM requires a GROUP BY...group by some column
$collection->getSelect()->group('product_id');

The examples above are for flat entities.

If you have an EAV entity similar to products or categories use

addAttributeToFilter instead of addFieldToFilter

addExpressionAttributeToSelect instead of addExpressionFieldToSelect

EDIT

After an other investigation I found that for flat entities you can use directly the column names. The attribute placeholders {{...}} are needed only for EAV entities.

So your second collection should look like this:

$collection = Mage::getModel('[module]/table2')->getCollection()
    ->addExpressionFieldToSelect('total', 'SUM(qty + product_id - any_other_columns)', array('qty', 'product_id', 'any_other_columns'));
$collection->getSelect()->group('product_id');

The third parameter for addExpressionFieldToSelect is required but it can be an empty array. for addExpressionAttributeToSelect that last parameter should contain the mapping between the placeholders and the attribute codes. In this case seams that they are useless. I don't know if this is a bug or a feature.

  • @VijayS. Did it work? – Marius Jul 17 '14 at 6:35
  • First Query working fine. The second one when i execute as query working fine.But i use getData() in this collection its not return a value. – VijayS91 Jul 17 '14 at 7:39
  • @VijayS What happens if instead of getData you loop through the collection like this: foreach ($collection as $item){Mage::log($item->getData())} – Marius Jul 17 '14 at 7:40
  • I have tried that above code, But i didn't return any value. – VijayS91 Jul 17 '14 at 7:49
  • @VijayS. You can try to see how the query looks like echo $collection->getSelect(). That should give you an idea if something is wrong. You may even run it manually to see the result. – Marius Jul 17 '14 at 7:50

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.