Jump to content
JohnSmithUK

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

Are you sure your ps_layered_price_index table contains column id_country?

can you check this on the database

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
Posted (edited)
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 by JohnSmithUK (see edit history)

Share this post


Link to post
Share on other sites
6 minutes ago, JohnSmithUK said:

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

ok good chance

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites
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),

Share this post


Link to post
Share on other sites

as i said the column doesn't exist

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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! 

Share this post


Link to post
Share on other sites

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 ) ;

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites
1 minute ago, JohnSmithUK said:

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

sorry? im just trying to help you

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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..)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More