Subselect as main_table on magento collection

Question

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

Answer 1

You can try something like:

$subQuery = new Zend_Db_Select();
...
$collection->getSelect()->from($subQuery);