0

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?

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.

  • thanks for the explanation. What do you think is better in my scenario where I just want to get the category name and nothing else, custom sql query or how I did it setting the columns in the select object of the collection? – lloiacono Feb 23 '16 at 10:37
  • 1
    I would probably do it similar as you. Additionally you can use $connection->fetchColumn($select) to retrieve an array of column values for a query with one column, or $connection->fetchPairs($select) for an [id=>name] array if the query has two columns, id and name. – Fabian Schmengler Feb 23 '16 at 13:23
1

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.

1

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

  • That's not the right way to do it. What you need is a direct query, but you shouldn't use collections if you want to exclude base columns. Have a look here on how to performa direct SQL query: fishpig.co.uk/magento/tutorials/direct-sql-queries – Phoenix128_RiccardoT Feb 22 '16 at 17:15
  • I disagree with you, can you explain why "is not the way to do it"? This is exactly what I want to do, I know I could write a query custom query. In the end there are many ways of doing the same thing – lloiacono Feb 22 '16 at 17:20
  • 1
    Collection are containers of items (e.g.: A category collection is not a query containing category rows, but it is more like a list of category objects inherited by Varien_Data_Collection). If you, for example, exclude the entity_id column you will not be able to call the "load" method in a consistent way and you will not be bale to use the collection with other Magento methods. This is why you should not approach this kind of scenario with a collection. Consider it a best-practice guideline. – Phoenix128_RiccardoT Feb 22 '16 at 17:24
  • 1
    There is nothing wrong with doing this, if required to do. A usage case could be the need to use that collection as a subquery in another. - example where IN (result from the given collection ids, done as a subquery, and only one filed /column must be the end result) - it is not wrong, and way better than hacking a query together using direct SQL. – ProxiBlue Feb 23 '16 at 6:40
  • @ProxiBlue thanks for you input, I was waiting for more opinions about this topic. – lloiacono Feb 23 '16 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.