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

1 Answer 1

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.

8
  • @VijayS. Did it work?
    – Marius
    Jul 17, 2014 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, 2014 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, 2014 at 7:40
  • I have tried that above code, But i didn't return any value.
    – VijayS91
    Jul 17, 2014 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, 2014 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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