3

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?

2
+50

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.

  • Thx for your suggestion. I've done that in the Toolbar.php that i've written on top. There is not order by. ORDER BY salable` DESC, qty DESC LIMIT 10` that is the only order but salelable is not correct. Name is selected in my sorting filter. – René Höhle May 11 '17 at 19:09
  • I know :) Therefore you should put your debug code in getCollection() - not in setCollection() - method (and maybe post your query here) . – sv3n May 11 '17 at 19:11
  • I've done that. To test it again. – René Höhle May 11 '17 at 19:13
  • And it is still saleable instead of name? (Without $this->_productCollection->addAttributeToSort("name");? – sv3n May 11 '17 at 19:15
  • It's the original code without any modifications and yes there is only salable in the Order by statement and not the name. When i add the line to sort i get an error: You cannot define a correlation name 'stock_qty' more than once very strange. – René Höhle May 11 '17 at 19:18
0

try

$this->_productCollection->order(array('`e`.`name` DESC'));
  • I don't want to change the core code. My goal is to use the normal Sorting function. It makes no sense to write your line to that file... this is only a debugging. – René Höhle May 11 '17 at 9:07

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.