# TL;DR

Install Topological Sort to fix the sorting algorithm.

# Problem

Mage_Weee is a module which is needed in Magento to calculate the so called Waste Electrical and Electronic Equipment Directive. It is a tax in Europe for electronic stuff, so that it is already paid, when you throws it away. Don't ask me. We don't sell any kind of electric stuff, so it is a good decision to remove the module.

<!-- app/etc/modules/zzzzzz_DeactivatedModules.xml-->
<?xml version="1.0"?>
<config>
<modules>
<Mage_Weee>
<active>false</active>
</Mage_Weee>
</modules>
</config>


What happens then was very irritating for me.

Before:

After:

You might sense the miscalculation of the tax. Our tax settings are still correct and everything is fine, but the tax is added to the price, instead of being included.

Easiest fix here is to just NOT deactivate Mage_Weee, but this is unsatisfying. So I dig deeper (and have already an idea, what happens):

## Magento total models

Magento is calculating all the stuff in quote and order with total models. You can check what total models are called in

\Mage_Sales_Model_Quote_Address::collectTotals
foreach ($this->getTotalCollector()->getCollectors() as$model) {

# Yea! Results!

The price_index filtered for customer_group = 0. I know this is the id for the NOT LOGGED IN, so I had a look into the customer_group table. Don't ask me why but THIS IS WRONG:

customer_group_id  |  customer_group_code  |  tax_class_id
-------------------|-----------------------|--------------
4  |        NOT LOGGED IN  |             3


I had a look into the price_index and found prices for the customer groups with the IDs 1,2,3 and 4. No prices for 0.

Ok, I built a long query to change this, I tried it in a big transaction but it didn't work - don't ask me why, it threw after the first query a

Cannot add or update a child row: a foreign key constraint fails


if you can tell me why, please send me an email!

So I skipped the foreign key check:

START TRANSACTION;
UPDATE salesrule_customer_group SET customer_group_id = '0' WHERE salesrule_customer_group.customer_group_id =4;
UPDATE salesrule_product_attribute SET customer_group_id = '0' WHERE salesrule_product_attribute.customer_group_id =4;
UPDATE customer_group SET customer_group_id = '0' WHERE customer_group.customer_group_id =4;
UPDATE catalog_product_bundle_price_index SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalog_product_entity_group_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalog_product_entity_tier_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalog_product_index_group_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalog_product_index_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalog_product_index_tier_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalogindex_minimal_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalogrule_customer_group SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalogrule_group_website SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalogrule_product SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE catalogrule_product_price SET customer_group_id = '0' WHERE customer_group_id =4;
UPDATE weee_discount SET customer_group_id = '0' WHERE customer_group_id =4;
COMMIT;


# But why is the wrong ID a problem?

Magento expects that the NOTLOGGEDIN customer group have the id 0:

Mage_Customer_Model_Group::NOT_LOGGED_IN_ID  = 0


And this const is used in:

// app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php:1340

$customerGroupId = Mage::getSingleton('customer/session')->getCustomerGroupId(); // app/code/core/Mage/Customer/Model/Session.php:174 // Mage_Customer_Model_Session::getCustomerGroupId() public function getCustomerGroupId() { if ($this->getData('customer_group_id')) {
return $this->getData('customer_group_id'); } if ($this->isLoggedIn() && $this->getCustomer()) { return$this->getCustomer()->getGroupId();
}
return Mage_Customer_Model_Group::NOT_LOGGED_IN_ID;
}


# How does this happen?

I have no idea. Mage_Customer has three install scripts, all of them create the customer group NOT LOGGED IN explicit with the ID 0.

# Conclusion

The index is created with a big SQL statement (I think, didn't check this) and therefore uses the customer_group_id from the database and magento's product list uses the constat from Mage_Customer_Model_Group if they don't match, you have a problem.

# UPDATE

Damian tweeted that you have to place a

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';


at the beginning of your SQL dump. If you don't do this, your admin store, the customer_group, etc. geting new IDs.

How to use the Mage_Eav_Model_Entity_Collection_Abstract::joinTable() method to join a flat table on a magento EAV collection

Joining a "normal" (aka flat table) to a magento EAV table is easy - if you know how to do.

As always there are a lot resources with tipps I don't like about joining Tables, because they all use getSelect()->join() and fall back to Zend_Db to join the tables. This can (but don't have to) lead to a few problems. In my past I got for example problems with getSize(), because the changes are only made to the Select-Statement not the Count-Statement.

# joinTable() TL;DR

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable( array('bonus' => 'mycompany/bonus'), 'product_id=entity_id', array('bonus_id' => 'bonus_id') );  The parameters are: public function joinTable($table, $bind,$fields = null, $cond = null,$joinType = 'inner')

1. Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
2. Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your field of the flat table BEFORE and your attribute of the EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
3. Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
4. Condition is a * WHERE ON condition* in the SQL.
5. joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

## more details

The method can be found here:

app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:754


### check the tablename and alias

First, the array of the tablename is splitted into alias and tablename, the tablename is resolved and the alias is checked for existance.

$tableAlias = null; if (is_array($table)) {
list($tableAlias,$tableName) = each($table); } else {$tableName = $table; } // validate table if (strpos($tableName, '/') !== false) {
$tableName = Mage::getSingleton('core/resource')->getTableName($tableName);
}
if (empty($tableAlias)) {$tableAlias = $tableName; }  ### check the fields Then the fields are checked. Are they already defined? If not, add them. // validate fields and aliases if (!$fields) {
throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Invalid joint fields'));
}
foreach ($fields as$alias=>$field) { if (isset($this->_joinFields[$alias])) { throw Mage::exception( 'Mage_Eav', Mage::helper('eav')->__('A joint field with this alias (%s) is already declared',$alias)
);
}
$this->_joinFields[$alias] = array(
'table' => $tableAlias, 'field' =>$field,
);
}


### the hard part, check the bind

The bind is exploded at the =, and the foreign key is expected after the equality sign. It took me 25min to find this. I didn't think about this. I thought it will be took directly into the SQL, but no, magento processes it a lot. If the column of your flat table is the second "argument", magento can't find the attribute and throws an exception: Invalid attribute name: product_id

I started with 'main_table.entity_id=bonus.product_id' and ended with 'product_id=entity_id' and here is the code:

// validate bind
list($pk,$fk) = explode('=', $bind);$bindCond = $tableAlias . '.' .$pk . '=' . $this->_getAttributeFieldName($fk);


Then the join method is choosed and the conditions are added. Interessting to see here is, that the whole condition is put into the ON statement. To be honest, I don't know the difference between ON and WHERE, but this looks like ON is processed on the table and WHERE is processed on the result?

And the second gem, I found here is str_replace('{{table}}', $tableAlias,$cond)., so you can use {{table}} if your condition is a string instead of the alias.

// process join type
switch ($joinType) { case 'left':$joinMethod = 'joinLeft';
break;

default:
$joinMethod = 'join'; }$condArr = array($bindCond); // add where condition if needed if ($cond !== null) {
if (is_array($cond)) { foreach ($cond as $k =>$v) {
$condArr[] =$this->_getConditionSql($tableAlias.'.'.$k, $v); } } else {$condArr[] = str_replace('{{table}}', $tableAlias,$cond);
}
}
$cond = '('.implode(') AND (',$condArr).')';

// join table
$this->getSelect()->$joinMethod(array($tableAlias =>$tableName), $cond,$fields);

return \$this;


# The end

And in the end, magento uses getSelect->join() wonderful :-) BUT magento makes a lot of checks for you and using this method feels better.