I'm trying to do a query with subselect in magento collection.
Example
The original query:
$collection = Mage::getModel('salesrule/rule') ->getCollection() ->addFieldToFilter('rule_type', 2) ->addWebsiteFilter($customer->getWebsiteId()) ; $collection->getSelect() ->joinInner( array('special' => $collection->getTable('company_salesrule/special')), 'special.salesrule_id=main_table.rule_id' ) ->where('special.from where('special.to > ?', $weight) //->order('main_table.sort_order ASC') ->group('special.group') ;
With the result;
SELECT `main_table` . *, `rule_coupons`.`code`, `special` . * FROM `salesrule` AS `main_table` LEFT JOIN `salesrule_coupon` AS `rule_coupons` ON main_table.rule_id = rule_coupons.rule_id AND rule_coupons.is_primary = 1 INNER JOIN `company_salesrule_special` AS `special` ON special.salesrule_id = main_table.rule_id WHERE (rule_type = '2') AND (EXISTS( SELECT 1 FROM `salesrule_website` AS `website` WHERE (website.website_id IN ('6')) AND (main_table.rule_id = website.rule_id))) AND (special.from 100) GROUP BY special.group
But, I need this result (Line 4):
SELECT `main_table` . *, `rule_coupons`.`code`, `special` . * FROM (SELECT * FROM salesrule ORDER BY sort_order ASC) AS `main_table` LEFT JOIN `salesrule_coupon` AS `rule_coupons` ON main_table.rule_id = rule_coupons.rule_id AND rule_coupons.is_primary = 1 INNER JOIN `company_salesrule_special` AS `special` ON special.salesrule_id = main_table.rule_id WHERE (rule_type = '2') AND (EXISTS( SELECT 1 FROM `salesrule_website` AS `website` WHERE (website.website_id IN ('6')) AND (main_table.rule_id = website.rule_id))) AND (special.from 100) GROUP BY special.group