Product Configuration – Product Image URL as Part of Collection Load

You can't do that in Magento! What were you thinking!?!

Just kidding. It helps to think about this outside of the bounds of Magento and instead in terms of Magento's underlying ORM, Zend_Db.

Because Zend_Db only supports the orWhere method, and has no means of grouping those OR statements, the only way I can think of doing this is by performing a UNION of 3 queries all with the varying clauses.

So, thinking outside of Zend even, you would then rewrite your query as separate SELECT statements rather than OR clauses:

Statement 1

...WHERE size = '10' and color = 'red';

Statement 2

...WHERE size = '12' and color = 'blue';

Statement 3

...WHERE size = '14' and color = 'green';

And assuming we had the raw SQL from each of these we could probably do a UNION ALL on them:

join(' UNION ALL ', $statements);

If we execute this query against the DB natively we'll get what we're looking for. So that's how we'll do it - utilizing getSelect:

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('size','10')
->addAttributeToFilter('color','red')
->getSelect();

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('size','12')
->addAttributeToFilter('color','blue')
->getSelect();

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeTofilter('size','14')
->addAttributeTofilter('color','green')
->getSelect();


$query = join(' UNION ALL ',$collection);

$result = Mage::getSingleton('core/resource')->getConnection('core_read')->fetchAll($query);

Which yields the query we're looking for:

SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '10') AND (at_color.value = 'red')
UNION ALL
SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '12') AND (at_color.value = 'blue')
UNION ALL
SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '14') AND (at_color.value = 'green')

Hope that helps!!

Without context, I cannot say if this is a good idea, but if you have a loaded collection and want to remove the last item from the collection (not delete it), you can do this:

$lastId = $collection->getLastItem()->getId();
$collection->removeItemByKey($lastId);

This works because internally the items are stored in an array with their id as key.