PrestaShopDatabaseException] Unknown column 'psi.id_country' in 'on clause'

[JohnSmithUK]

[PrestaShopDatabaseException]

Unknown column 'psi.id_country' in 'on clause'

 

SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufacturer, sa.quantity, p.condition, p.weight, p.price FROM ps_product p LEFT JOIN ps_stock_available sa ON (p.id_product=sa.id_product AND 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) WHERE p.visibility IN ('both', 'catalog') AND c.nleft>=3 AND c.nright<=50 AND ps.id_shop='1' AND p.active = TRUE GROUP BY p.id_product) p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_currency = 1 AND psi.id_country = 21) GROUP BY p.id_product ORDER BY psi.price_min ASC LIMIT 0, 39

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

 

764.         if ($webservice_call && $errno) {
765.             $dbg = debug_backtrace();
766.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] ' . $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.     }

[JohnSmithUK]

Getting this error "Only on Category pages"

[JohnSmithUK]

1 minute ago, ilyass86 said:

psi.id_country = 21 

Hi,

i think that should be outside your join condition

How do I fix this? This was not an issue a day ago. 

[ilyass86]

Are you sure your ps_layered_price_index table contains column id_country?

can you check this on the database

[ilyass86]

try to excute your request directly from your database management system (mysql)

if it's work the problem is in the code

else the problem is in your request

[JohnSmithUK]

1 minute ago, ilyass86 said:

try to excute your request directly from your database management system (mysql)

if it's work the problem is in the code

else the problem is in your request

good point. Let me try this. I'll be right back. 

[JohnSmithUK]

14 minutes ago, ilyass86 said:

try to excute your request directly from your database management system (mysql)

if it's work the problem is in the code

else the problem is in your request

Okay error: Error in query (1054): Unknown column 'psi.id_country' in 'on clause'

Edited June 3, 2019 by JohnSmithUK (see edit history)

[ilyass86]

6 minutes ago, JohnSmithUK said:

good point. Let me try this. I'll be right back. 

ok good chance

[ilyass86]

check if your table contains this column id_country ?

 

Edited June 3, 2019 by ilyass86 (see edit history)

[JohnSmithUK]

2 minutes ago, ilyass86 said:

check if your table contains this column id_country ?

 

Okay error: Error in query (1054): Unknown column 'psi.id_country' in 'on clause'

How do I fix this and what would cause this? Thanks in advance! 

id_contry in which table? 

[ilyass86]

it looks like the id_country column does not appear in your table

[ilyass86]

1 minute ago, JohnSmithUK said:

Okay error: Error in query (1054): Unknown column 'psi.id_country' in 'on clause'

How do I fix this and what would cause this? Thanks in advance! 

id_contry in which table? 

in ps_layered_price_index table

[JohnSmithUK]

Just now, ilyass86 said:

it looks like the id_country column does not appear in your table

How do I add that and does that exist in others installations and how would prestashop work for this long without that column? can you give me a sql string to run to add this please? 

[ilyass86]

3 minutes ago, JohnSmithUK said:

How do I add that and does that exist in others installations and how would prestashop work for this long without that column? can you give me a sql string to run to add this please? 

can you make me a capture of the columns of your ps_layered_price_index table first? (to check if the column exists or no)

 

[JohnSmithUK]

Just now, ilyass86 said:

can you make me a capture of the columns of your ps_layered_price_index table first? (to check if the column exists or no)

 

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `ps_layered_price_index`;
CREATE TABLE `ps_layered_price_index` (
  `id_product` int(11) NOT NULL,
  `id_currency` int(11) NOT NULL,
  `id_shop` int(11) NOT NULL,
  `price_min` int(11) NOT NULL,
  `price_max` int(11) NOT NULL,
  PRIMARY KEY (`id_product`,`id_currency`,`id_shop`),
  KEY `id_currency` (`id_currency`),
  KEY `price_min` (`price_min`),
  KEY `price_max` (`price_max`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ps_layered_price_index` (`id_product`, `id_currency`, `id_shop`, `price_min`, `price_max`) VALUES
(1,	1,	1,	19,	20),

[ilyass86]

as i said the column doesn't exist

ok wait a minutes i will try to make the good one for you 

[ilyass86]

I think you have installed a new version of prestashop and on this version they have changed this table

[JohnSmithUK]

3 minutes ago, ilyass86 said:

I think you have installed a new version of prestashop and on this version they have changed this table

okay please do help me fix this if you can. Thanks! 

[JohnSmithUK]

This would add the column but what should the value be set to? 

ALTER TABLE ps_layered_price_index ADD id_country INT( 11 ) NOT NULL , ADD KEY ( id_country ) ;

[ilyass86]

if you remove the id_country = 21

SELECT p.id_product, p.price, psi.price_min, psi.price_max FROM (SELECT p.id_product, p.id_manufacturer, sa.quantity, p.condition, p.weight, p.price FROM ps_product p 
                                                                 LEFT JOIN ps_stock_available sa ON (p.id_product=sa.id_product AND 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) WHERE p.visibility IN ('both', 'catalog') 
                                                                 AND c.nleft>=3 
                                                                 AND c.nright<=50 
                                                                 AND ps.id_shop='1'
                                                                 AND p.active = TRUE GROUP BY p.id_product) p 
                                                                 INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_currency = 1) 
                                                                 GROUP BY p.id_product ORDER BY psi.price_min ASC LIMIT 0, 39
 

the request work fine but i need to understad  your needs

if you need to add the country condition in your request you can get it from the ps_shop_country table and i can add it to your request

[ilyass86]

Just now, JohnSmithUK said:

This would add the column but what should the value be set to? 

ALTER TABLE ps_layered_price_index ADD id_country INT( 11 ) NOT NULL , ADD KEY ( id_country ) ;

NO! you do not have to change the structure of this table. it is a prestashop table you must not change it otherwise once you make a new installation nothing will work

[JohnSmithUK]

2 minutes ago, ilyass86 said:

NO! you do not have to change the structure of this table. it is a prestashop table you must not change it otherwise once you make a new installation nothing will work

What's the id_country value in your table? 

[JohnSmithUK]

ilyass86 you don't know what you're talking about. 

[ilyass86]

1 minute ago, JohnSmithUK said:

ilyass86 you don't know what you're talking about. 

sorry? im just trying to help you

[JohnSmithUK]

2 hours ago, JohnSmithUK said:

This would add the column but what should the value be set to? 

ALTER TABLE ps_layered_price_index ADD id_country INT( 11 ) NOT NULL , ADD KEY ( id_country ) ;

Creating an empty id_country with value 0 did not fix the issue. I'm sending a message for some help to https://www.prestashop.com/forums/profile/741527-selectshopat/

[JohnSmithUK]

2 hours ago, ilyass86 said:

sorry? im just trying to help you

doesn't seem like you know what you are talking about. I need someone whom knows why this is happening not trying to add empty rows and tables to cause more damage. 

[ilyass86]

5 minutes ago, JohnSmithUK said:

doesn't seem like you know what you are talking about. I need someone whom knows why this is happening not trying to add empty rows and tables to cause more damage. 

 

I did not say that you have to add a column. it was your proposal

if you want someone to help you, you have to stay polite and give them all the information they need to help you 

I can't understand your problem from the error produced only but from the details that you can provide (example : your old version of prestashop and the new one..)

[tofrou]

Hi,

I had got the same issue (PS 1.7.5)

I solved it by updating module ps_faceted_search to 3.0.3 and reindexing product

-> https://github.com/PrestaShop/PrestaShop/issues/14097