Joining EAV tables on a Non-EAV Collection

Sometimes, we may want to join EAV tables and their associated value tables on other tables via a foreign key. E.g. we have a table which has a download count for customers who are able to download PDFs. We have a column called customer_id which is our foreign key to link us to the customer_entity table.

Our download table has the following columns:

  • download_log_id
  • customer_id
  • file_path
  • type
  • count
  • customer_name
  • created_at
  • updated_at

The table is grouped by file_path, with each download increasing the count column. We can’t use a collection of the type customer/customer, because we’ll end up with duplicate customers with the same ID in the collection, which Magento will throw an error at.

Instead, we’ll use the collection based from the download table’s collection, and will attempt to join the customer’s EAV tables onto it. To add the attributes firstname and lastname, we can use a custom helper which is detailed in full below:

class Stormking_Skin_Helper_Eav extends Mage_Core_Helper_Abstract{
    protected $_aliasIndex = 0;

    public function joinEAV($collection, $mainTableForeignKey, $eavType, $attrCode, $mainTable = 'main_table'){
        $this->_aliasIndex++;

        $entityType = Mage::getModel('eav/entity_type')->loadByCode($eavType);
        $entityTable = $collection->getTable($entityType->getEntityTable());

        //Use an incremented index to make sure all of the aliases for the eav attribute tables are unique.
        $attribute = Mage::getModel("eav/config")->getAttribute($eavType, $attrCode);

        $attr =  Mage::getModel('eav/entity_attribute')->loadByCode($eavType, $attrCode);

        $alias = 'table_' . $this->_aliasIndex;
        $field = $attrCode; // This will either be the original attribute code or 'value'

        if ($attribute->getBackendType() != 'static'){
            $field = 'value';
            $table = $entityTable. '_'.$attribute->getBackendType();

            $collection->getSelect()
                ->joinLeft(array($alias => $table),
                    $mainTable . '.'.$mainTableForeignKey.' = '.$alias.'.entity_id and '.$alias.'.attribute_id = '. $attr->getId(),
                    array($attribute->getAttributeCode() => $alias . "." . $field)
                );
        }else{
            $collection->getSelect()
                ->joinLeft(array($alias => $entityTable),
                $mainTable . '.'.$mainTableForeignKey.' = '. $alias.'.entity_id',
                    $attribute->getAttributeCode()
                );
        }

        // Return the table alias and field name (either $attrCode or value) so we can use the table in future queries
        return array(
            "table" => $alias,
            "field" => $field
        );

    }
}
  • This creates a join for each of the attributes we wish to join on.
  • The table alias is incremented for each use so we don’t get alias clashes.
  • We return the aliased table name and the name of the field used. This will either be the original field name (in the case of static values) or value in the case of values in a corresponding EAV value table.

Applying The Helper

$collection = Mage::getModel('stormkingskin/download_log')->getCollection();

$helper = Mage::helper('llapgoch_core/eav');
$helper->joinEAV($collection, 'customer_id', 'customer', 'email');
$helper->joinEAV($collection, 'customer_id', 'customer', 'firstname');
$helper->joinEAV($collection, 'customer_id', 'customer', 'lastname');
$helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

Using the Aliased Table in Other Queries

We may want to use the table which has been joined by our joinEAV query in later additions to our collection. In the example above, suppose we then want to join on the customer’s group_id from the customer_entity table on Magento’s customer_group table so that we can get the customer group name.

The joinEAV method returns the aliased table name which we can then use to make this work:

 
$groupTable = $helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

$collection->getSelect()->joinLeft(
	array('customer_group' => $collection->getTable('customer/customer_group')),
	'customer_group.customer_group_id=' . $groupTable . '.group_id',
	array('customer_group_code')
);

– This allows us to use the table name from our previous query in our new join to pull out the customer_group_code data.

Filtering in Adminhtml Grids

Using the above methods will cause issues with Adminhtml grids. All of the fields not in the main table’s collection will be stripped out and will cause the SQL to fail. Fortunately, there is a workaround albeit a little convoluted:

1. Log each of the aliased table names

In our grid class, create an array where we will store our generated alias names:

protected $_aliasTables = array();

protected function _prepareCollection() {

	$collection = Mage::getModel('stormkingskin/download_log')->getCollection();
	$helper = Mage::helper('stormkingskin/eav');

	$this->_aliasTables['email'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'email');
	$this->_aliasTables['firstname'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'firstname');
	$this->_aliasTables['lastname'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'lastname');

	$groupTable = $helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

	$collection->getSelect()->joinLeft(
		array('customer_group' => $collection->getTable('customer/customer_group')),
		'customer_group.customer_group_id=' . $groupTable['table'] . '.group_id',
		array('customer_group_code')
	);

	$collection->getSelect()->joinLeft(
		array('customer_address' => $collection->getTable('customer/address_entity')),
		'customer_address.parent_id=main_table.customer_id',
		array("")
	);

	// Use the last parameter to join this to the customer_address table instead of the default main_table
	$this->_aliasTables['company'] = $helper->joinEAV($collection, 'entity_id', 'customer_address', 'company', 'customer_address');

	$this->setCollection($collection);
	return parent::_prepareCollection();
}

– Note that our query to get the company name differs somewhat as we need to rely on the ID from the customer_address_entity table rather than the default main_table. We use a joinLeft just before to set up the relationship between the main_table and the customer_entity_address table. We can then create our relationship between the customer_entity_address model and the entity’s value table.

2. Add a filter condition callback to any affected fields

This will allow us to override Magento’s default query filter builder process and use our own. All of the field aliases will still be stripped off the final query, but we will now be able to use their original names using the aliases from the last step.

protected function _prepareColumns() {
	$this->addColumn('firstname', array(
		'header'	=> $this->__('First Name'),
		'index'		=> 'firstname',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));

	$this->addColumn('lastname', array(
		'header'	=> $this->__('Last Name'),
		'index'		=> 'lastname',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));

	$this->addColumn('company', array(
		'header'	=> $this->__('Company'),
		'index'		=> 'company',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));
}

3. The Filter Condition Callback Method

protected function _joinUsingHaving($collection, $column){

	$index = $column->getIndex();

	if(!isset($this->_aliasTables[$index])){
		return;
	}

	$tableAlias = $this->_aliasTables[$index];
	$value = $collection->getConnection()->quote("%" . $column->getFilter()->getValue() . "%");
	$collection->getSelect()->where($tableAlias['table'] . "." . $tableAlias['field'] . " LIKE " . $value);
}
  • We check the existence of the index we’re looking for in our alias array.
  • We quote our filter value ready for the custom where query.
  • We then use our table alias and field name.

See here for more information on advanced adminhtml collection queries