Filter Collection with result of previous collection

Question

I have doubts about what is more optimal or if there are better ways to filter collections with the result of a previous collection.

I can do this:

$orders_id = Mage::getModel("custom/custom_order")->getCollection()
             ->addFieldToSelect("order_id")
             ->addFieldToFilter("attribute_id",array("eq"=>"some"));

$orders = array();
foreach ($orders_id as $order) {
    array_push($orders, $order->getOrderId());
}

$orders_filtered = Mage::getModel("sales/order")->getCollection()
                    ->addFieldToFilter($orders);

Or I can also do this:

$orders_filtered = Mage::getModel("sales/order")->getCollection();
$orders_filtered->getSelect()
                ->join(
                    array('custom_order' => $this->getTable("custom/custom_order")),
                    "main_table.entity_id = custom_order.order_id and custom_order.attribute_id = 'some'",
                    "array('custom_order.*')"
                );

Are there other ways to make it optimal?

Greetings!

Answer 1

I personally like the second option, it involves one SQL query to get to the same results. Additionally the processing of the data (to the end result) is done purely by MYSQL. It also eliminates the need for instantiating the custom/custom_order objects, just to get a list of ids.

In regards to your first option, this would be more optimized, not sure how much faster, not done any testing ;)

$orders_id = Mage::getModel("custom/custom_order")->getCollection()
             ->addFieldToSelect("order_id")
             ->addFieldToFilter("attribute_id",array("eq"=>"some"));

$limitIds = implode(',',$orders_id->getAllIds());

$orders_filtered = Mage::getModel("sales/order")->getCollection();
$orders_filtered->getSelect()->where(new Zend_Db_Expr("`order_id` IN ({$limitIds})"));

The following is another alternative, but it bypasses the object models, but can result in a faster result. I use this type of stuff in dataflow imports, or external shell scripts, when and where I don;t want the overhead of the object models. Just adding it for completeness

$resource = Mage::getSingleton('core/resource');
$readAdapter = $resource->getConnection('core_write');
$subTable = $resource->getTableName('custom/custom_order');
$mainTable = $resource->getTableName('sales/order');
$subSelect = $readAdapter->select()->from($subTable)->reset(Zend_Db_Select::COLUMNS)->columns('order_id');
$select = $readAdapter->select()->from($mainTable)->reset(Zend_Db_Select::COLUMNS)->columns('order_id')->where("`order_id` IN ($subSelect)")->where("`foo` = 'bar'");
$stmt = $readAdapter->query($select);
$data = $stmt->fetchAll();
// get the data cleaned.
$flattenData = (object) array('data' => array());
array_walk_recursive($data, create_function('&$v, $k, &$t', '$t->data[] = $v;'), $flattenData);

and of course you can combine the two ways:

$resource = Mage::getSingleton('core/resource');
$readAdapter = $resource->getConnection('core_write');
$subTable = $resource->getTableName('custom/custom_order');
$subSelect = $readAdapter->select()->from($subTable)->reset(Zend_Db_Select::COLUMNS)->columns('order_id');
$stmt = $readAdapter->query($subSelect);
$data = $stmt->fetchAll();
// get the data cleaned.
$flattenData = (object) array('data' => array());
array_walk_recursive($data, create_function('&$v, $k, &$t', '$t->data[] = $v;'), $flattenData);
$orders_filtered = Mage::getResourceModel("sales/order_collection")
                    ->addFieldToFilter('order_id', array('in' => $flattenData->data));