Blocklayered navigation version 2.0.13 not working

[jorge911]

Recently I have updated the blocklayered module (layered navigation) from 2.0.7 to 2.0.13 (version numbers on the blocklayered.php file). Since I updated the module does not work and it shows the error message.

 

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) W' at line 4
 

							SELECT m.name, 0 nbr, m.id_manufacturer							FROM ps_cat_restriction p JOIN							INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)							WHERE 1							GROUP BY p.id_manufacturer ORDER BY m.name

at line 646 in file classes/db/Db.php

641. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);642. 		}643. 		elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))644. 		{645. 			if ($sql)646. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');647. 			throw new PrestaShopDatabaseException($this->getMsgError());648. 		}649. 	}650. 651. 	/**

• DbCore->displayError - [line 340 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 516 - classes/db/Db.php] - [1 Arguments]
• DbCore->executeS - [line 2382 - modules/blocklayered/blocklayered.php] - [1 Arguments]
• BlockLayered->getFilterBlock - [line 687 - modules/blocklayered/blocklayered.php] - [1 Arguments]
• BlockLayered->hookHeader - [line 512 - classes/Hook.php] - [1 Arguments]
• HookCore::exec - [line 456 - classes/controller/FrontController.php] - [1 Arguments]
• FrontControllerCore->initContent - [line 11 - override/classes/controller/FrontController.php]
• FrontController->initContent - [line 104 - controllers/front/CategoryController.php]
• CategoryControllerCore->initContent - [line 12 - override/controllers/front/CategoryController.php]
• CategoryController->initContent - [line 180 - classes/controller/Controller.php]
• ControllerCore->run - [line 373 - classes/Dispatcher.php]
• DispatcherCore->dispatch - [line 28 - index.php]

The "offending" code is on blocklayered.php where thereis a SQL query like this:

                        $sql_query['second_query'] = '
                            SELECT m.name, 0 nbr, m.id_manufacturer

                            FROM '._DB_PREFIX_.'cat_restriction p JOIN
                            INNER JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
                            WHERE 1
                            GROUP BY p.id_manufacturer ORDER BY m.name';

 

which obviously is wrong (in red), but furthermore, it is trying to use a table ps_cat_restriction that does not exist on the database at least on version 1.6.0.14 !!!

 

It looks like if the blocklayered module version 2.0.13 is NOT compatible with 1.6.0.14, may be it is for 1.6.1? Then why has it been made available for upgrade on 1.6.0.14 and probably on other older versions also?

 

Even if the use of ps_cat_restriction is intended for 1.6.1 how is it possible that the extra "JOIN" has gone into the query provoquing a syntax error???!!!!

 

For now I have returned back to blocklayered 2.0.7, fortunately I had a backup.

Edited August 2, 2015 by jorge911 (see edit history)

[Ctdl]

up

[jorge911]

I have created a bug in Prestashop forge: http://forge.prestashop.com/browse/PSCSX-6447

 

Also I have added a comment in Prestashop's Github to the developer that modified that part of the code recently. He didn't introduce the duplicate "JOIN" but he left it there, I don't understand how he can modify that line and don't see the error there....

 

aporvino commented on 945915a a day ago

Lines 2169 and 2170 are incorrect and is making the module to completely fail with a mysql syntax error:

FROM '.DB_PREFIX.'cat_restriction p JOIN
INNER JOIN '.DB_PREFIX.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

Obviously the final JOIN on 2169 should be there.

 

Let's see if they take care...

[jorge911]

I just found that there was a bug report already for this from June on blocklayered version 2.0.10, still present on 2.0.13...

http://forge.prestashop.com/browse/PNM-3496

 

On that bug there are further details of when the error happens:

The blocklayered module generates an invalid SQL query for the "manufacturers" filter if the "Hide filter values when no product is matching" is set to "off" and filter by manufacturers is enabled.

 

So if we set to "on" the "Hide filter values when no product is matching" the error should not happen in theory...

[jorge911]

Prestashop programmers are already fixing this bug, currently under code review

 

http://forge.prestashop.com/browse/NM-577