Subscribe Feed

Unirgy News

Bug in Magento Product Collections (UPDATE: it’s a feature)

If you ever had issues with products disappearing from collections when they shouldn’t be, here’s one of the possible reasons:

There’s a


method, which calls


on EVERY product collection, which has some nasty consequences.

For example, if you have configured System > Configuration > Inventory > Stock Options > Display Out of Stock Products to No, then all the out of stock products will not show up in ANY product collection, because $collection->addPriceData() inner joins with price index table, which doesn’t have entries for products not shown on the frontend (Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection::_productLimitationJoinPrice)

Anyways, I hope this saved you few minutes (hours) of debugging… I will update this post when or if I’ll find an acceptable solution.

UPDATE: Got a clarification from senior Magento developer: This is an intended behavior.
All the product collections, when not in admin, will be forced to hide products out of stock if this is configured.
I don’t like it, and don’t think that should be the case, but I guess that’s life.

Rapid Prototyping for Custom Magento Extensions

I’ve finally had enough of dreading to write all the configuration for each custom module, and invested a couple of hours into this:

Feedback is welcome as always!

Dangerous bug in MySQL 5.0.90

Yesterday encountered a weird bug in MySQL 5.0.90, not sure about other 5.0.x versions, but 5.1.x seems to be OK.

The issue presented itself here:

// $skus = array('A','B','123');
$_conn->fetchAll("select entity_id, sku from catalog_product_entity where sku in (?)", $skus);

Normally this would select only the records which has `sku` one of provided in the $skus array. But it seemed like it returned ALL records in the table, which was very curious.

The reason was that some of $skus were numeric, and PDO automatically converted them to numbers instead of keeping them as strings, and it confused this specific version of MySQL to have both numbers and strings in the “IN” condition, and it returned ALL records that started with alphanumeric character.

Isolated test case:

-- create table
create table test (sku varchar(255) primary key);
-- create test data
insert into test values ('a'),('b'),('c');

-- this will show ALL records with SKU starting with a letter
select * from test where sku in ('',1);

This is a very dangerous issue, which can lead not only to huge memory consumption and performance degradation, but also unwarranted updates of records that should not be updated.

My workaround was to generate the SQL condition manually:

// $skus contains all the SKUs to be selected
// $conn is the DB connection
// $table is 'catalog_product_entity' with correct prefix
$skus1 = array();
foreach ($skus as $sku) {
    $skus1[] = is_numeric($sku) ? "'".$sku."'" : $conn->quote($sku);
$select = $conn->select()->from($table)
    ->where('sku in ('.join(',', $skus1).')');

As MySQL 5.0.90 is still installed on many servers, make sure to account for this bug in your development.

How to use the same Magento copy with multiple DBs and configurations

If you ever need to run multiple Magento configurations from the same copy of core files, this wiki article will help you with basic configuration.

I’ve used it to create QA and demo copies, for easy upgrade of both Magento and tested/demoed extensions.

Let me know if it was useful for you!

Adding your extension version to admin pages

So you’d like users of your extension to know which version it is, so you can give them support, without asking them to open extension files?

In your helper add this method:

public function addAdminhtmlVersion($module)
    $layout = Mage::app()->getLayout();
    $version = (string)Mage::getConfig()


<script type="text/javascript">
$$(".legality")[0].insert({after:"'.$module.' ver. '.$version.'<br/>"});


    return $this;

And in your controller action, add the method call:

// ADD:

Now on admin pages that were introduced by your module it will show “Your_Module ver. 1.2.3” right above the Magento version.

Importing product images with CSV

I’ve seen recently this issue – editing and saving products in admin deleted existing images from frontend.

On evaluation it appeared that the images, while showing on the frontend, did not appear in the admin product edit Images tab at all.

Inspection of the database tables revealed that while EAV records contained image names (used on frontend), the image gallery (used in admin) was missing the relevant entries, so it appears that CSV import does not populate gallery table.

This problem will happen with both new and updated products, when new image names are added with CSV.

I’ve made an SQL statement that will fill the missing records, and which you will need to run each time after importing products with images using CSV:

insert into catalog_product_entity_media_gallery (attribute_id, entity_id, `value`)
select ga.attribute_id, v.entity_id, v.value 
from catalog_product_entity_varchar v
inner join eav_entity_type et on et.entity_type_code='catalog_product'
inner join eav_attribute va on va.entity_type_id=et.entity_type_id and va.frontend_input='media_image' and va.attribute_id=v.attribute_id
inner join eav_attribute ga on va.entity_type_id=et.entity_type_id and ga.attribute_code='media_gallery'
left join catalog_product_entity_media_gallery g on g.entity_id=v.entity_id and g.value=v.value
where v.value<>'no_selection' and v.value<>'' and g.value is null;