Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
Best Value (position) searches stopped working after upgrade to 1.2.0.1 from 1.1.8
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

A few days ago I upgraded from 1.1.8 to 1.2.0 and then 1.2.0.1.  I was very thorough after the upgrade and ran a UNIX diff to compare EVERY file in the distribution against my local copies to ensure I had an accurate copy of every one.  In addition, I ran diffs to compare my modified theme files against the default theme files they are derived from to be sure that I copied over any changes that were made other than the look-and-feel stuff that we’ve customized locally.

After this, I got a report from a user that the Best Value sort was not working.  I have verified this is the case.  However, sort by price or name both work fine.  Products sorted by Best Value are displayed unsorted (actually sorted by entity_id ascending).

I traced through various classes and methods to figure out what was going on when setOrder was called on a collection and set up a number of “break points” in the code to see what variables were being set to what at various points.

Mage_Eav_Model_Entity_Collection_Abstract->addAttributeToSort() is where I end up:

public function addAttributeToSort($attribute$dir='asc')
    
{
        
if (isset($this->_joinFields[$attribute])) {
            $this
->getSelect()->order($this->_getAttributeFieldName($attribute).' '.$dir);
            return 
$this;
        
}
        
if (isset($this->_joinAttributes[$attribute])) {
            $attrInstance 
$this->_joinAttributes[$attribute]['attribute'];
            
$entityField $this->_getAttributeTableAlias($attribute).'.'.$attrInstance->getAttributeCode();
        
else {
            $attrInstance 
$this->getEntity()->getAttribute($attribute);
            
$entityField 'e.'.$attribute;
        
}
        
if ($attrInstance{

            
if ($attrInstance->getBackend()->isStatic()) {
                $this
->getSelect()->order($entityField.' '.$dir);
            
else {
                $this
->_addAttributeJoin($attribute'left');
                if (isset(
$this->_joinAttributes[$attribute])) {
                    $this
->getSelect()->order($attribute.' '.$dir);
                
}
                
else {
                    $this
->getSelect()->order($this->_getAttributeTableAlias($attribute).'.value '.$dir);
                
}
            }
        }

        
return $this;
    
}

At the top of this function I can print out $attribute and $dir and demonstrate that this is where the sort code ultimately goes.  When called from catalog/block/product/list, the else block gets hit:

else {
            $attrInstance 
$this->getEntity()->getAttribute($attribute);
            
$entityField 'e.'.$attribute;
        
}

After this point $attriInstance is set to false, presumably because catalog_category_product_index.position is not part of the EAV system so the EAV functions can’t return a proper AttributeInstance.

After this point, the function falls through to the return without ever setting a sort order on the query that is being built in the collection.

If I add the following code directly above the return statement, I can get Best Value sorting to work properly:

if ($attribute == 'position')
{
$this
->getSelect()->order("position $dir");
}

It is easy to verify this with a $this->getSelect()->__toString() printout on either side of the order() function call.

However, since this function is also called from other locations on entities that have position attributes that are part of the EAV system, this will tack on a redundant order clause at the end of the SQL statement. 

I am baffled because I’ve been so thorough at ensuring that my code exactly matches the distribution code.  The magento demo store is also running 1.2.0.1 and clearly Best Value search works there.  There is something weird going on here that warrants further investigation.  I am not convinced that this is a 1.2.0.1 bug, but last week Best Value searches worked and this week they don’t.

I put in a bug report, and also figured I’d post here in case anyone else is experiencing this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
pixelpusher
Sr. Member
 
Avatar
Total Posts:  135
Joined:  2008-04-23
SoCal
 

my “best value” sorting does not work either
1.1.8 -> 1.2.0.1

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

My quick hack is not a good workaround since it breaks other things.

I have a 1.1.6 installation in a Virtual PC VHD from when I was evaluating Magento a few months ago.  It has the demo store data installed.  I verified that Best Value sort works fine on it.

I upgraded it to 1.1.8 and tested it and again, the Best Value/position sort works perfectly.

I then upgraded it to 1.2.0.1 and test it again.  And it did not work.  Clicking the asc/desc arrow results in no change to the sorting.  They are sorted by entity_id only, not by position.

I saved a copy of my 1.1.8 demo store VHD, so I can go back and try different things to see what’s different.  I’m going to go ahead with an upgrade from 1.1.8 to 1.2.0 and see if that affects things.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

I added the following PHP near the bottom of app/design/frontend/default/default/template/catalog/product/list/toolbar.phtml in order to determine what SQL query is being run to fetch and sort the collection:

<?php print "<p><pre>" $this->getCollection()->getSelect()->__toString() . "</pre></p>"?>

I tested this in 1.1.8 on the demo store database and got the following output:

SELECT `e`.*, `_table_position`.`position`, `_table_website_id`.`website_id`, `_price_rule`.`rule_price` AS `_rule_priceFROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product_index` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id='8' AND _table_position.is_parent=1)
 
INNER JOIN `catalog_product_website` AS `_table_website_idON (_table_website_id.product_id=e.entity_id) AND (_table_website_id.website_id=1)
 
INNER JOIN `catalog_product_enabled_index` AS `enabled_indexON enabled_index.product_id=e.entity_id AND enabled_index.store_id='1' AND enabled_index.visibility IN (24)
 
LEFT JOIN `catalogrule_product_price` AS `_price_ruleON _price_rule.product_id e.entity_id AND _price_rule.rule_date '2009-01-10' AND _price_rule.website_id '1' AND _price_rule.customer_group_id '1' WHERE (e.entity_type_id '10'ORDER BY `_table_position`.`positiondesc LIMIT 10

Then I upgraded to 1.2.0.1 using the same demo store database and got the following output after the upgrade, browsing the same category URL:

SELECT `e`.*, `cat_index`.`position`, `_price_rule`.`rule_price` AS `_rule_priceFROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product_index` AS `cat_indexON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.category_id='8' AND cat_index.is_parent=1
 LEFT JOIN 
`catalogrule_product_price` AS `_price_ruleON _price_rule.product_id e.entity_id AND _price_rule.rule_date '2009-01-10' AND _price_rule.website_id '1' AND _price_rule.customer_group_id '0' WHERE (cat_index.visibility IN (24)) LIMIT 10

It is clearly no longer adding an ORDER BY clause on a position search, and it is clearly the upgrade from 1.1.8 to 1.2.0.1 that causes this.  That is the ONLY variable I changed on this test.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

My better workaround is to add some code to /app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/
Collection.php. 

Edit the setOrder() function.

Add this:

elseif ($attribute == 'position'{
            $this
->getSelect()->order("position $dir");

Right above this:

else {
            parent
::setOrder($attribute$dir);
        
}

That will bypass the more convoluted code on the parent classes for setting the code (since it’s broken) and just set it at the level of the Product Collection class when a Best Value sort is requested.  This doesn’t interfere with other types of Collections, which my previous workaround did.

My new theory is that this problem might be caused by the changes in Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection->addCategoryFilter(); The way the tables are being joined together has changed completely.  I don’t know for sure though, and I’ve gotten a decent workaround.  So I hope I’ve posted enough clues here that someone on the coding team will figure this out and fix it for the next version.

 
Magento Community Magento Community
Magento Community
Magento Community
 
alex.bsc
Guru
 
Total Posts:  340
Joined:  2008-06-06
 

i’m getting quite tired of magento’s issues. it was bad enough in 1.1.8.

 
Magento Community Magento Community
Magento Community
Magento Community
 
pixelpusher
Sr. Member
 
Avatar
Total Posts:  135
Joined:  2008-04-23
SoCal
 

this is still broke in 1.2.0.2 Is there some template file that needs to be updated?

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

I submitted a bug report on this a couple of days ago, referencing this thread.  They closed it and it says it’s “Resolved” now.  I just used svn to grab the latest 1.2-trunk alpha code from the developer code repository.  There does not appear to be any change to the code in the repository that would fix this problem.

The workaround described above in the app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Collection.php setOrder() function is working fine for me.  Give it a shot and see if it solves your problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

I was just checking the roadmap for 1.2.1 and saw the bug report I submitted, 10020, listed on there as resolved in the next version.  So apparently the fix for this is on the way.

 
Magento Community Magento Community
Magento Community
Magento Community
 
squirrels
Sr. Member
 
Total Posts:  93
Joined:  2008-08-27
 

@ddeppner

Thank you - works perfectly! smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
ddeppner
Jr. Member
 
Total Posts:  23
Joined:  2008-08-10
 

Actually I just found another problem with the workaround I posted.  If you have subcategories AND layered navigation set up it will error out with a SQL error when you click on a top-level category off the navigation bar but then click on a subcategory in the layered navigation.  This is because the table containing the position field is being joined twice in that case and the position field is ambiguous and it doesn’t know which table to sort by.

The solution is to tweak the above workaround to call the field “cat_index.position” rather than just “position” in the ->order() function call.  It will order based on the position set in the category selected from the nav bar in that case, not the subcategory picked from the layered navigation.  Just be aware of that.

 
Magento Community Magento Community
Magento Community
Magento Community
 
squirrels
Sr. Member
 
Total Posts:  93
Joined:  2008-08-27
 

@ddeppner
Thank you for your continuous help!
Wouldn’t have noticed the error caused for several days I’m sure – and then I probably wouldn’t think to connect it with the Best value thing… So thank you again!

I must ask though… In which files do I need to change that array? I’m guessing in app/code/core/Mage/Catalog/Block/Product/List/Toolbar.php but has to be somewhere else as well - otherwise the sorting won’t work…

 
Magento Community Magento Community
Magento Community
Magento Community
 
Etasker
Member
 
Total Posts:  54
Joined:  2008-07-17
Denmark
 
ddeppner - 11 January 2009 12:39 AM

My better workaround is to add some code to /app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/
Collection.php. 

Edit the setOrder() function.

Add this:

elseif ($attribute == 'position'{
            $this
->getSelect()->order("position $dir");

Right above this:
else {
            parent
::setOrder($attribute$dir);
        
}

Thank you ddeppner!

This worked for me!

 
Magento Community Magento Community
Magento Community
Magento Community
 
squirrels
Sr. Member
 
Total Posts:  93
Joined:  2008-08-27
 

@viovao

It does work but as ddeppner found yesterday, this sorting together with categories in layered navigation causes a 500 error.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Smokersroom
Member
 
Total Posts:  63
Joined:  2007-09-04
 
alex.bsc - 11 January 2009 12:22 PM

i’m getting quite tired of magento’s issues. it was bad enough in 1.1.8.

x 1,000,000

 
Magento Community Magento Community
Magento Community
Magento Community
 
victor21
Jr. Member
 
Total Posts:  2
Joined:  2008-10-01
 

Hi,

I found the getCurrentDirection() function was not returning the right $dir (descending order for me).
I simply commented that, and force getCurrentDirection() to give me ‘desc’ like this :

public function getCurrentDirection()
    
{
        
if ($dir = (string) $this->getRequest()->getParam($this->getDirectionVarName())) {
            $dir 
strtolower($dir);
            if (
in_array($dir, array('asc''desc'))) {
           
//return $dir;
                
return 'desc';
            

        }
        
return 'desc';
    
}

I know this is not the right way, but it works for me. Hope it helps.

Website SEO and Site e-commerce

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2