500 Error

[clampdown]

Hello,

Can anybody help - for some reason i'm getting a 500 Server Error on manufacturer pages...

Unknown column 'product_attribute_shop.id_product' in 'on clause'

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, IFNULL(product_attribute_shop.`id_product_attribute`,0) id_product_attribute
			, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`,
			pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, image_shop.`id_image` id_image, il.`legend`, m.`name` AS manufacturer_name,
				DATEDIFF(
					product_shop.`date_add`,
					DATE_SUB(
						"2016-05-05 00:00:00",
						INTERVAL 20 DAY
					)
				) > 0 AS new FROM `ps_product` p
			 INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)LEFT JOIN `ps_product_attribute_shop` product_attribute_shop
						ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
			LEFT JOIN `ps_product_lang` pl
				ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1 )
				LEFT JOIN `ps_image_shop` image_shop
					ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
			LEFT JOIN `ps_image_lang` il
				ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
			LEFT JOIN `ps_manufacturer` m
				ON (m.`id_manufacturer` = p.`id_manufacturer`)
			 LEFT 
			JOIN ps_stock_available stock
			ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1  AND stock.id_shop_group = 0  )JOIN `ps_category_product` cp ON (p.id_product = cp.id_product)JOIN `ps_category_group` cg ON (cp.`id_category` = cg.`id_category` AND cg.`id_group` = 1)JOIN `ps_category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1
				WHERE p.`id_manufacturer` = 10
				 AND product_shop.`active` = 1
				 AND product_shop.`visibility` IN ("both", "catalog")
				GROUP BY p.id_product
				ORDER BY product_shop.`price` asc
				LIMIT 0,30

in phpmyadmin here is the database columns for product_attribute_shop

 

 

id_product_attribute

id_shop

wholesale_price

price

ecotax

weight

unit_price_impact

default_on

minimal_quantity

available_Date

 

[jllramos]

In product_attribute_shop there aren't id_product column: p.`id_product` = product_attribute_shop.`id_product`

INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)LEFT JOIN `ps_product_attribute_shop` product_attribute_shop
						ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
			LEFT JOIN `ps_product_lang` pl 

I saw in prestashop tables and it has this column id_product. But in your specifications is not.....

 

Edited May 5, 2016 by jllramos (see edit history)

[clampdown]

 

In product_attribute_shop there aren't id_product column: p.`id_product` = product_attribute_shop.`id_product`

INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)LEFT JOIN `ps_product_attribute_shop` product_attribute_shop
						ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
			LEFT JOIN `ps_product_lang` pl

Thank you for the reply.

 

So... where do I put this? in MySQL ?

[jllramos]

Take a look of your tables.

 

Do you have id_product in table product_attribute_shop??

[clampdown]

No.. so do I need to add it?

[jllramos]

What version is your Prestashop??

 

Yes, you need it or you need to change your query...

[clampdown]

OK thanks - currently running 1.6.0.14

[tuk66]

The product_attribute_shop.id_product field is new in PS 1.6.1.0, so some part of your code is not compatible with your version (module, patch).