Collection addAttributeToSelect multiple columns in query

Question

I'm trying something very simple:

$categoryCollection = Mage::getModel('catalog/category')
    ->getCollection();

$categoryCollection
    ->addAttributeToSelect('name')
    ->addAttributeToFilter('entity_id', array('in' => array(1,2,3,4)));

I would expect to have this query when doing $categoryCollection->getSelect()->__toString()

SELECT main_table.name FROM catalog_category_flat_store_1 AS main_table WHERE (entity_id IN(1, 2, 3, 4));

Instead of that I get this:

SELECT main_table.entity_id, main_table.level, main_table.path, main_table.position, main_table.is_active, main_table.is_anchor, main_table.name FROM catalog_category_flat_store_1 AS main_table WHERE (entity_id IN(1, 2, 3, 4));

Why is adding the extra columns, when I explicitly say I want only name, also what is the correct way of doing this?

Answer 1

To explain the behavior: The columns in the main entity table are not attributes in the EAV sense. The EAV collections always include these columns in their query, and with addAttributeToSelect() you specify which attribute tables to join (or if the flat index is used, which additional columns to select).

The columns in the entity table are considered to be essential and as you found out the only way to not select them is to remove them from the Zend_Db_Select query object.

Answer 2

Collection is not a query. A collection has a query.

You cannot exclude them from a collection.

They are part of the entity table in the EAV model and part of the basic Magento category mechanism. You always have them in a collection, even when using flat tables.

Answer 3

After debugging I found a way of doing what I wanted:

$categoryCollection = Mage::getModel('catalog/category')
    ->getCollection();

$categoryCollection
    ->addAttributeToFilter('entity_id', array('in' => array(1,2,3,4)));

$categoryCollection->getSelect()->reset(Zend_Db_Select::COLUMNS);
$categoryCollection->getSelect()->columns('name');
$sql = $categoryCollection->getSelect()->__toString();

This produces the query:

SELECT main_table.name FROM catalog_category_flat_store_1 AS main_table WHERE (entity_id IN(1, 2, 3, 4))

Which is what I wanted