Blank Category Pages error

[sheepstations]

Hi, I have just upgraded from P1.7.5.0 to P1.7.6.8 and now find that I can not view any category pages. Product pages are fine.
When in debug mode, I get the following error messages.
Can anybody offer a solution? I have looked around the forum on this topic and can't find an answer that helps me.

Thanks in advance,

JR
 

[PrestaShopDatabaseException]

Unknown column 'psi.id_country' in 'on clause'<br /><br /><pre>SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=84 AND c.nright<=91 AND ps.id_shop='1' GROUP BY p.id_product) p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 1 AND psi.id_country = 8) GROUP BY p.id_product ORDER BY psi.price_min ASC LIMIT 0, 20</pre>
at line 769 in file classes/db/Db.php

764. if ($webservice_call && $errno) { 765. $dbg = debug_backtrace(); 766. WebserviceRequest::getInstance()->setError(500, '

 ' . $this->getMsgError() . '. From ' . (isset($dbg[3]['class']) ? $dbg[3]['class'] : '') . '->' . $dbg[3]['function'] . '() Query was : ' . $sql, 97); 767. } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) { 768. if ($sql) { 769. throw new PrestaShopDatabaseException($this->getMsgError() . '<br /><br /><pre>' . $sql . '</pre>'); 770. } 771. 772. throw new PrestaShopDatabaseException($this->getMsgError()); 773. } 774. }

DbCore->displayError - [line 385 - classes/db/Db.php] - [1 Arguments]

DbCore->query - [line 613 - classes/db/Db.php] - [1 Arguments]

DbCore->executeS - [line 92 - modules/ps_facetedsearch/src/Adapter/MySQL.php] - [1 Arguments]

PrestaShop\Module\FacetedSearch\Adapter\MySQL->execute - [line 94 - modules/ps_facetedsearch/src/Filters/Products.php]

PrestaShop\Module\FacetedSearch\Filters\Products->getProductByFilters - [line 133 - modules/ps_facetedsearch/src/Product/SearchProvider.php] - [5 Arguments]

PrestaShop\Module\FacetedSearch\Product\SearchProvider->runQuery - [line 308 - classes/controller/ProductListingFrontController.php] - [2 Arguments]

ProductListingFrontControllerCore->getProductSearchVariables - [line 580 - classes/controller/ProductListingFrontController.php]

ProductListingFrontControllerCore->doProductSearch - [line 137 - controllers/front/listing/CategoryController.php] - [2 Arguments]

CategoryControllerCore->initContent - [line 292 - classes/controller/Controller.php]

ControllerCore->run - [line 515 - classes/Dispatcher.php]

DispatcherCore->dispatch - [line 28 - index.php]

[Crezzur.com]

The error is easy to understand if you know where to look for.
The actual error Unknown column 'psi.id_country' in 'on clause' is saying it cannot find column id_country which is using the prefix psi

As you can see in your sql code we use prefixes to identify which table we are using, example:

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac

In the above examples we use the prefixes pa and pac, so when using pa.TABLENAME we want to get a value from the ps_product_attribute. the same for pac and so on ...

Identifying your problem:
So your error is saying it cannot find column id_country in the table that uses prefix psi, So when we look at your code we see :

INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 1 AND psi.id_country = 8)

Which means that the prefix psi is the name for the table ps_layered_price_index.

To make sure everything is correct there we need to check if the column id_country exist in this table (ps_layered_price_index.)
In your sql statement you see you are trying to call AND psi.id_country = 8  so we need to look if there is a id_country with the value 8.

Check if the value exist:

SELECT * FROM ps_layered_price_index WHERE id_country = 8;

If this does not return any result we are calling a unknown country id.
Using the following SQL statement you can view your full table and check which id_country is being used.

SELECT * FROM ps_layered_price_index;

 

[sheepstations]

Hi and thank you for the great reply. That makes things seem much clearer.

So what I can tell you after looking at the table, the column id_country does not exist in (ps_layered_price_index.)

Can I ask you to provide the SQL command to insert the column id_country into the table so that all values = 8 ?

That would be very much appreciated.

JR

[Crezzur.com]

This SQL code will add the column id_country to the table ps_layered_price_index with the default value 8

ALTER TABLE ps_layered_price_index ADD `id_country` int(11)  DEFAULT 8 ;

 

Edited October 6, 2020 by Crezzur (see edit history)

[sheepstations]

I had to fix the syntax a little to

ALTER TABLE `ps_layered_price_index` ADD `id_country` int(11)  DEFAULT 8 ;

but that worked perfectly.

Thanks very much for your help. All good now.

JR