jorge911

Prestashop programmers are already fixing this bug, currently under code review http://forge.prestashop.com/browse/NM-577

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...

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...

Ok, I understand now... There is a forge ticket already for this: http://forge.prestashop.com/browse/PSCFV-7286 and I have added my comments. Unfortunately there is this entry on the history: Damien Metzger changed the Priority to 'Minor' on PSCFV-7286 - There is lack of foreign keys in database 22/Aug/13 11:12 AM Which is discouraging, but if we keep adding comments to that ticket may be they will open their ears... or we will have to organise a comunity team to fork and take on the job. It's not serious not having foreign keys, it is like driving a car without a seatbealt!

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.

I know the meaning, the doubt was if it was falling on his deaf ears or on the deaf ears of the core developers... switch ($on_whose_ears_it_falls) { case "on his ears": $my_answer = "I don't think it is a very constructive answer (although I might be wrong)"; break; case "on core developers ears": $my_answer = "it would help to know why he thinks the core developers will be deaf to it"; break; default: $my_answer = "then, on whose ears?"; } $this->context->smarty->assign(array( 'answer' => $my_answer, )); you can work out the tpl (no pun intended, just a little bit of humor on a Sunday )

Sorry, what is that supposed to mean?

I'm another developer asking myself how is it possible that there are no foreign keys defined in prestashop. Above it is asked what are the benefits for PS on using FK. There are many academic papers explaining it but there is a good resume on stackoverflow: http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys Here is the list with some of my additional comments, Reasons to use Foreign Keys: you won't get Orphaned Rows (real benefit for PS!) you can get nice "on delete cascade" behavior, automatically cleaning up tables (real benefit for PS!) knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. (real benefit for PS!!) FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance (real benefit for PS!!) they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc (will help PS developers) someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented (will help PS developers) Reasons not to use Foreign Keys: you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn (on PS it will probably be offset by the performance improvements above, it would need to be tested) by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. -Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing-. This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state -but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?-. (since PS does not use batch transactions the pain is aliviated and by enforcing relationships and having the DB refuse creating orphaned rows by the code is actually an improvement on database integrity and performance, although initially a bit of a pain for core developers who will start to create SQL queries that satisfy the correct database relationships). sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it (is that what PS developers have done? But it shouldn't be dirty for the benefit of everyone!!!). you are just being lazy :-) (same question as on previous point :-) ) Real, business, serious software developement uses foreign keys on its DBs. I know because I have migrated databases at multinational telecomunication companies and we had to keep databases foreign keys (well, for the migration we had to drop them all and then recreate...). So if Prestashop wants to become a real ecommerce leader it should enforce foreign keys... in my opinion. Or do we create a comunity branch and we start to enforce it? Once enforced in the DB and corrected the relevant code, keeping in sync with the main official branch should be quite easy, just review and correct if necessary any additional queries found.

I'm answering myself for the benefit of others that might have the same issue as I have solved it. It is an issue with translations and language. On my previous entry I wrote a modified URL and not the one that I'm really using (I did change it because I was writting in english) but the original URL was index.php?id_category=3&controller=category#/precio-2-25 I you might guess, "precio" is "price" in spanish, and "precio" is what is displayed by Prestashop when I apply a filter by price on the layered navigation. If I try to modify the URL constructed by Prestashop to different values, for example something like: index.php?id_category=3&controller=category#/precio-2-27 then the price filter will be lost, which is the problem that I reported. Looking at the code of blocklayered.php I found out that the strings "price" and "weight" are HARDCODED!!! Instead of using the values from the database depending on the language. So when Prestashop BUILDS the URL when I filter is applied it does use the translated value (e.g. "precio"), but when it READS the values from the URL it uses hardcoded constants that are in english only (e.g. "price"). So it is an inconsistent use and therefore a bug. The error in the code (in my opinion) is on blocklayered.php line 1719: else if (in_array($attribute_name, array('price', 'weight'))) as it uses the hardcoded attribute names 'price' and 'weight'. The correct code should get the filters of type slider from the database, get the names of the filter on the active language and then search for the name on the url, instead of searching only for the name of the filter in english language only. The other solution would be to build the URL always in english instead of on the active language then it will always work also. The bug really is that translation is used when building the URL but not used when reading the URL. It should either be translated always or never for both building and reading the URL.

Hi, I want to use a URL to go to a page where the layered navigation price slider filter is used. In other words, I want to list all products within a price range when clicking on a link like this: index.php?id_category=3&controller=category#/price-2-25 The issue is that I have no product with a price of 25€ (not exactly 25€ and neither a rounded 25€). So when I go to a link like this the layered navigation shows all prices. On the other hand if I have a product for 27€ and I use a link link: index.php?id_category=3&controller=category#/price-2-27 It does work correctly. Could someone point me to where in blocklayered.php or other file I need to modify so it behavies as desired. I cannot be the only one that wants the price slider to be a little more flexible on the price ranges.... I'm on version 1.6.0.14 and with the blocklayered module fully updated. Thanks.

Ok, me siento un poco avergonzado porque la solución estaba delante de mis narices... Cuando añado un nuevo tema tengo que decir "SI" a "Columna Izquierda por Defecto" (y seguramente también para "Columna Derecha por Defecto". Por eso no mostraba la columna izquierda... Tengo que decir que me ha liado un poco que en la página de categorías el contador de productos decía 0 y luego no mostraba ningún producto en la parte de abajo, ¿sólo por que no había columna por defecto a la izquierda? no debería ser independiente mostrar los productos de las subcategorías aunque no esté la columna de la izquierda? Creo que esto es un bug en Prestashop 1.6.0.14 Dejo la respuesta por si alguien lo encuentra útil.

Ok, I feel a bit ashamed because the solution was right in front of my face. When adding a new theme I had choose yes on "Default Left Column" (and probably also for "Default Right Column"). That is why the left column was not on the category page. I have to say that it misslead me the fact that the category page was counting 0 products and not showing them below, just because there was no "Default Left Column". Shouldn't products be displayed even if there is no left column? I leave here the answer just in case someone else find it useful.

En 1.6.0.14 creo una copia del tema por defecto default-bootstrap utilizando en botón de añadir tema e indicando 12 productos por página. El tema default-bootstrap funciona perfectamente pero cuando cambio al tema copia del default (sin cambiar ningún archivo) no aparece en la página de categorías ni el bloque de categorías, ni la navegación por facetas, ni los productos de las subcategorías (que sí salían con el tema por defecto). Adjunto como se ve el tema por defecto y como se ve la copia del tema por defecto. Hay un tema algo similar aquí: https://www.prestash...ges-in-ps-1603/ pero la solución era poner el número de productos por página que yo sí he hecho, así que esto no soluciona mi problema. Qué puede estar produciendo esto? Cómo puedo arreglarlo? Gracias! PS: Tengo una instalación local.

Si tienes 1.6.0.14 u otra versión relativamente moderna ya no vale con sólo copiar la carpeta del tema a /themes pues en esta versión los temas también se registran en la BBDD. Para importar un tema ahora tienes que utilizar el botón de añadir tema del back-end e indicar como directorio del tema el mismo directorio que has copiado en /themes. Recuerda indicar el número de productos por página pues si no pones nada puede que haya un bug por ahí todavía y no se vean los productos en la página de categorías. Espero que te ayude...

In 1.6.0.14 I create a copy of the default-bootstrap theme using the add theme button and indicating the number of products per page as 12. The default-bootstrap works perfectly but when I change to the copy (without any modification whatsoever) the categories page stop displaying the categories block, the layered navigation and it doesn't display any products from the subcategories (when on the default theme was displaying over 2000). I attach how it looks in the default theme and how it looks on the copy of the default theme. There is a slightly similar question here: https://www.prestashop.com/forums/topic/312183-products-not-displayed-on-category-pages-in-ps-1603/ but there the solution was to indicate a number of products per page and I'm doing it, so that does not seem to be my problem. What can be producing this? How can I fix it? Thanks! PS: I have it installed locally.