Wrong sorting with "name" attribute

Question

I have a problem regarding the sorting with the "name" attribute. When i sort by Name the table is changing but it's not sorted by name.

In app/core/Mage/Catalog/Block/Product/List/Toolbar.php i made an output in the set collection method.

/**
 * Set collection to pager
 *
 * @param Varien_Data_Collection $collection
 * @return Mage_Catalog_Block_Product_List_Toolbar
 */
public function setCollection($collection)
{
    $this->_collection = $collection;

    $this->_collection->setCurPage($this->getCurrentPage());

    // we need to set pagination only if passed value integer and more that 0
    $limit = (int)$this->getLimit();
    if ($limit) {
        $this->_collection->setPageSize($limit);
    }
    if ($this->getCurrentOrder()) {
        $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
    }
    return $this;
}

The setOrder method is called with "name" as first parameter i've checked that. But when i output the query after that.

echo $this->_collection->getSelect();

i get the following output:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0

i can't see a sort by the name field.

/xxxx/xxx.html?dir=asc&order=name

That URL is called. But the output is not sorted by name it's in a random order. The products are all "simple" products and the attribute is a "global" attribute because we have some shops that share the name.

I've read a lot but can't find a solution. Perhaps someone an help me to find the correct file and line or know that problem.

Edit:

Some more informations:

$this->_productCollection->addAttributeToSort("name");
echo $this->_productCollection->getSelect();

If i do that some really wired is extended to my query:

AND stock_qty.website_id=1 ORDER BY `salable` DESC, `qty` DESC

So why is "salable" added if i try to sort by name?

Answer 1

To check the query ...

// start debug here ...
public function getCollection()
{
    echo $this->_collection->getSelect();
    return $this->_collection;
}

It should look like ...

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` FROM `prefix_catalog_product_entity` AS `e` INNER JOIN `prefix_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3' INNER JOIN `prefix_catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN `prefix_catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '71') AND `at_name_default`.`store_id` = 0 LEFT JOIN `prefix_catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '71') AND (`at_name`.`store_id` = 1) ORDER BY `name` ASC LIMIT 12

I'll update this answer later ...

Edit:

We found the problem via chat. It was caused by 3rd-party-code.

Answer 2

try

$this->_productCollection->order(array('`e`.`name` DESC'));