Magento aggregate and between collections?

Question

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

Answer 1

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.