4

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
1

You can try something like:

$subQuery = new Zend_Db_Select();

...

$collection->getSelect()->from($subQuery);

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.