Retreiving Configurable Products Whose Children Have a Particular Attribute

A title as catchy as a loose tablecloth. This will give you a list of all configurable products whose children have a particular attribute.

SELECT config_product.entity_id, prod_name.value AS product_name, config_product.sku
     FROM  catalog_product_entity config_product
JOIN eav_attribute
    ON eav_attribute.entity_type_id = 4 
    AND eav_attribute.attribute_code = "name"
JOIN catalog_product_entity_varchar prod_name
    ON prod_name.`attribute_id` = eav_attribute.`attribute_id`
    AND prod_name.`entity_id` = config_product.`entity_id`
WHERE config_product.entity_id IN (
    SELECT parent_id 
        FROM catalog_product_entity_int att_value 
    JOIN `catalog_product_super_link` link
        ON link.product_id = att_value.entity_id
    AND att_value.`value` = attribute_value
);
  • As Magento only allows the creation of product links on dropdowns, catalog_product_entity_int should be fine to remain hardcoded.
  • Change attribute_value to the option value id. An easy way to retrieve this from Magento’s admin is to look at the name of the input box when looking at the attribute’s Manage Label/Options tab; it will be something like “option[value][959][0]” where 959 is the option ID.

The SQL firstly uses a subquery to get all products which are being used as simples (if their product_id appears in the super_link table), and gets their configurable counterparts from this. We need to do this as a subquery their may be multiple configurables using the same simple product. It then uses the parent IDs in looking up more details from the catalog_product_entity and catalog_product_entity_varchar tables.

Useful Installer Snippets

To perform database operations from within an installer script, we can get the connection object. Connection objects provide functionality to modify tables, columns, indexes. From model resources, connections can be retrieved using getReadConnection() and _getWriteAdapter(), which is a protected method. Connection objects are instances of the Magento_Db_Adapter_Pdo_Mysql which extends Magento_Db_Adapter_Pdo_Mysql.

All of the following assume that the variable $installer has been assigned as $this from the installer class.

Defining an Installer

<global>
    <resources>
        <foo_bar_setup> // Individual namespace for a resource
            <setup>      // Setup information
                <class>Mage_Core_Model_Resource_Setup</class> // Class to run from within
                <module>Foo_Bar</module>
            </setup>
        </foo_bar_setup>
    </resources>
</global>

At a first glance it appears odd to have to include the module name in the setup node, however because all XML is merged into one big file, Magento has no way of knowing who the originating module of the setup declaration was.

Getting a table’s name

<?php
$installer->getTable('wishlist/wishlist');

This calls the Mage::getSingleton(‘core/resource’)->getTableName($tableName) and caches the result for future calls.

Generating Index Names

Although any name could be used as an index name, Magento includes a handy method to automatically generate index names to ensure there are no clashes between them.

<?php
$installer->getIdxName($tableName, $fields, $indexType = '');

– Table name can be retrieved with the installer’s getTableName() method.
$fields is an array which contains all of the fields the index is for.
$indexType can be one of the following:

<?php
Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY
Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX
Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT

Adding an index to a table

<?php
$installer->getConnection()->addIndex($installer->getTable('wishlist/wishlist'),
    $installer->getIdxName($installer->getTable('wishlist/wishlist'),
        array('customer_id'),
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
    ),
    array('customer_id'),
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
);

addIndex() accepts three parameters; the first is the index name where we use getIdxName() and pass in the table name, the fields, and the index type. The second parameter an array of columns for the index, and the third is the type of index we’re creating.

Checking a Table Exists

<?php
$installer->getConnection()->isTableExists($installer->getTable('wishlist/wishlist'));

Converting a Date to MySQL Format

<?php
$installer->getConnection()->convertDate($date);
$installer->getConnection()->convertDateTime($dateTime);

Running SQL

$installer->run('DROP table `{$installer->getTable('wishlist/wishlist')}`');

This aliases the connection’s multiQuery method in Varien_Db_Adapter_Pdo_Mysql.
Note: $connection->query() could also be used.

Generating Foreign Key Names

Just like indexes, Magento provides a useful method for generating foreign key names:

<?php
$installer->getFkName($priTableName, $priColumnName, $refTableName, $refColumnName) 

Renaming a Column

$installer->run("ALTER TABLE table_name CHANGE `column_name` `new_column_name` VARCHAR(255);

Adding a Foreign Key When Creating A Table

$table->addForeignKey($installer->getFkName('catalogsearch/result', 'product_id', 'catalog/product', 'entity_id'),
        $installer->getTable('catalogsearch/result'), 'product_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)

– Would create a from the catalogsearch/result table to the catalog/product table from the column product_id to entity_id

Adding a Foreign Key to an Existing Table

<?php
$installer->getConnection()->addForeignKey(
    $installer->getFkName('catalogsearch/result', 'product_id', 'catalog/product', 'entity_id'),
    $installer->getTable('catalogsearch/result'),
    'product_id',
    $installer->getTable('catalog/product'),
    'entity_id'
);

– The first parameter is the foreign key name, which we generate using Magento’s getFkName() method.
– The second and third parameters are the primary table and column.
– The fourth and fifth parameters are the reference table and column.
– The sixth parameter is the database action to perform when the primary column is deleted.
– The seventh parameter is the database action to perform when the primary column is updated.

Possible options for the onDelete and onUpdate parameters are:

<?php
Varien_Db_Adapter_Interface::FK_ACTION_CASCADE
Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL
Varien_Db_Adapter_Interface::FK_ACTION_NO_ACTION
Varien_Db_Adapter_Interface::FK_ACTION_RESTRICT
Varien_Db_Adapter_Interface::FK_ACTION_SET_DEFAULT

Removing a Foreign Key

As far as I’m aware, Magento doesn’t offer a utility method for removing foreign keys. Instead, we can use the run command and build the foreign key’s name with getFkName().

<?php
$wishlistTable = $installer->getTable('wishlist/wishlist');
$fkName = $installer->getFkName('wishlist/wishlist', 'customer_id', 'customer/entity', 'entity_id');

$installer->run("ALTER TABLE {$wishlistTable} DROP foreign key {$fkName}", array());

Adding a Column to an Existing Table

$this->getConnection()->addColumn(
	$this->getTable('wishlist/wishlist'),
	'cookie_id',
	array(
		'type' => Varien_Db_Ddl_Table::TYPE_TEXT,
		'length' => 255 // Will be created as VARCHAR
		'nullable' => true,
		'default' => null,
		'comment' => 'Created At’ // A comment must be provided
   )
);

– The first column is the table name and the second is the name of the column we’re adding. The third is an array of attributes for the new column; a list of types can be ascertained from the Varien_Db_Ddl_Table class.
Note: To create a column with a type of varchar, use TYPE_TEXT with a length of 255. TYPE_VARCHAR, has been deprecated and the constant is only kept in for the MySQL adapter.

Column Types

<?php
const TYPE_BOOLEAN          = 'boolean';
const TYPE_SMALLINT         = 'smallint';
const TYPE_INTEGER          = 'integer';
const TYPE_BIGINT           = 'bigint';
const TYPE_FLOAT            = 'float';
const TYPE_NUMERIC          = 'numeric';
const TYPE_DECIMAL          = 'decimal';
const TYPE_DATE             = 'date';
const TYPE_TIMESTAMP        = 'timestamp'; // Capable to support date-time from 1970 + auto-triggers in some RDBMS
const TYPE_DATETIME         = 'datetime'; // Capable to support long date-time before 1970
const TYPE_TEXT             = 'text';
const TYPE_BLOB             = 'blob'; // Used for back compatibility, when query param can't use statement options
const TYPE_VARBINARY        = 'varbinary'; // A real blob, stored as binary inside DB

Magento Adminhtml Grids and Advanced Collection Queries

Sometimes, when we’re creating an Adminhtml grid, we need to join our collection onto another table in order to get the relevant data that we need. This can open up some pitfalls when Magento attempts to do its pagination routines.

Consider the following, our _prepareCollection method:

<?php
protected function _prepareCollection(){
	$collection = Mage::getResourceModel('llapgoch_banners/banner_collection');
		
	$collection->getSelect()->joinLeft(
		array('banner_item' => $collection->getTable('llapgoch_banners/banner_item')),
		'banner_item.banner_id=main_table.banner_id',
		array()			
	);
		
	$collection->getSelect()->group('main_table.banner_id');
	$collection->addExpressionFieldToSelect('num_banner_items', 'COUNT(*)', null);

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

– Note our addExpressionFieldToSelect call, where we get the number of banner items for each banner. Our query would look like this:

SELECT `main_table`.*, COUNT(*) AS `num_banner_items` FROM `llapgoch_banner` AS `main_table` LEFT JOIN `llapgoch_banner_item` AS `banner_item` ON banner_item.banner_id=main_table.banner_id GROUP BY `main_table`.`banner_id`

– All good so far, let’s see what columns we’ve defined in our _prepareColumns method:

<?php
protected function _prepareColumns(){
		parent::_prepareColumns();
		$helper = Mage::helper('llapgoch_banners');
		
		$this->addColumn('banner_id', array(
			'header' => $helper->__('ID'),
			'type' => 'text',
			'index' => 'banner_id',
			'width' => '150px'
		));
		
		$this->addColumn('title', array(
			'header' => $helper->__('Title'),
			'type' => 'string',
			'index' => 'title'
		));
		
		$this->addColumn('is_active', array(
			'header' => $helper->__('Active'),
			'type' => 'options',
			'index' => 'is_active',
			'width' => '150px',
			// Add options to the filter
			'options' => array(
				1 => 'Enabled',
				0 => 'Disabled'
			)
		));
		
		$this->addColumn('num_banner_items', array(
			'header' => $helper->__('Number of Banner Items'),
			'type' => 'text',
			'index' => 'num_banner_items',
			'width' => '150px',
		));
		
		return $this;
	}
 

All appears fine so far, until that is we filter by the num_banner_items field! Magento thoughtlessly strips out all column names from our query, and attempts to add num_banner_items into our where clause – and being an aggregate alias, that would never work anyway.

To get around this, we can add our own filter method to the column, and take control of how it affects the query in question:

<?php
$this->addColumn('num_banner_items', array(
	'header' => $helper->__('Number of Banner Items'),
	'type' => 'options',
	'index' => 'num_banner_items',
	'width' => '150px',
	'filter_condition_callback' => array($this, '_numBannersFilter'),
));

protected function _numBannersFilter($collection, $column){
	$collection->getSelect()->having('COUNT(*) = ?', $column->getFilter()->getValue());
}
  • Now, we still can’t use our alias num_banner_items as Magento will still strip out all columns (apart from its own COUNT(*)) when it runs through its own pagination routine for the grid. Instead, we have to use our aggregate function again. Yes, it’s messy… but it works.

SQL in Magento

Although Magento’s collections and models are convenient, they can often be cumbersome and awkward to get to perform certain tasks. Fortunately, through the connection (Magento_Db_Adapter_Pdo_Mysql, and ultimately Zend’s MySql connection stack down to Zend_Db_Adapter_Abstract) and select (Varien_Db_Select, and ultimately Zend’s Zend_Db_Select) allow us perform database queries without too much hassle.

Getting the Connection Object

A connection object can be used from the following places:

<?php
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

$collection->getConnection();
$model->getResource()->getReadConnection();

Getting a Table’s Name

<?php
$table = Mage::getSingleton('core/resource')->getTableName('llapgoch_banners/banner');

Getting part of a SELECT

$collection->getSelect()->getPart(Zend_Db_Select::FROM);

Fetch Col

<?php
$connection->fetchCol('SELECT sku from ' . $resource->getTableName('catalog/product'));

– This puts the columns into a single array. I.e. all skus will be in a one dimensional array (array(‘sku1’, ‘sku2’, ‘sku3’) etc.

Fetch One

<?php
$connection->fetchOne('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns a single row for the query

Fetch All

<?php
$connection->fetchAll('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns all rows for the query

Combining with a Select Object

Writing direct SQL is all well and good, but it’s often neater and safer to write such queries using a select object, and passing that to the connection:

<?php
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
		
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'), array('title', 'is_active'))
     ->where('banner_id > 1');
		
$results = $connection->fetchAll($select);

Binding Values

Binding values is useful for automatic value escaping. By referencing variables using :variable_name, we can pass in arrays of replacement variables into the connection’s fetch methods:

<?php
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
		
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'))
	->where('banner_id > :num_banners');
		
$results = $connection->fetchAll($select, array(
	'num_banners' => 1
));

Select Object Methods

Where

 <?php $select->where($condition, $value = null);

– where provides the option of passing a single value into the condition to be replaced, but can also be used like the above example using binds. To use the value in the condition, we use a question mark:

 <?php $select->where('banner_id > ?', 1);

Limit

 <?php $select->limit($number, $offset);

OrderRand

 <?php $select->orderRand();

From

 <?php $select->from($tableName, $cols = array());

Columns

 <?php $select->columns($cols = "*", $correlationName = null);?>

– Specifies the columns to be brought in the select. Can contain aggregate queries.

Union

 <?php $select->union($select = array(), $type = self::SQL_UNION); 

Join

 <?php $select->join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

– This is the same as the joinInner method.

Join Left

 <?php $select->joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

Join Right

 <?php $select->joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null); 

OR Where

 <?php $select->orWhere($cond, $value = null, $type = null); 

Group

 <?php $select->group($spec) 

– $spec can be an array or a string

Having

 <?php $select->having($cond, $value = null, $type = null); 

– $value will be replaced into $cond using a question mark in the query.

OR Having

 <?php $select->orHaving($cond, $value = null, $type = null);

– $value will be replaced into $cond using a question mark in the query.

Order

 <?php $select->order($spec);

– $spec can be a string or an array of columns.

Query

 <?php $select->query($fetchMode = null, $bind = array()); 

– $fetchMode is an integer. This is optional to using the connection’s fetchAll, fetchOne, and fetchCols methods

Reset

 <?php $select->reset($part = null);

– Allows parts of the query to be reset

getAdapter

 <?php $select->getAdapter();

– Returns the adapter (connection object) passed into the constructor.

TODO: Add info about the Zend Connection (adapter) methods in Zend_Db_Adapter_Abstract