Jump to content

Need help understanding Building Prestashop queries


Recommended Posts

Hey Guys ,


I Am new to module development , and i am going through code of existing modules on prestashop available to download.


What i am Trying to do - 

In Instant search Suggest - I have product combination 100gm , 1kg etc. of 1 Product


When Person Searches - search results should show each product as separate product.

Example Product 100gm 

              Product 500gm

              Product 1 kg


But before i jump to modify what i want , I need help understanding what currently code is written for.


Below mentioned Query which i am unable to understand ,how and which tables and its fileds its selecting.


SELECT p.* , product_shop.* , stock.out_of_stock  - which table and field it is selecting 


As i cant find a any Dbprefix_ table name by the name p , product_shop , stock in my prestashop database.


Through out the below mentioned query - 

pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,

i cant understand the use of pl and its clearly not a table.

$alias = 'product_shop.';
				$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
				pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
				image_shop.`id_image`, il.`legend`, m.`name` manufacturer_name '.$score.', product_attribute_shop.`id_product_attribute`,
				INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY
				) > 0 new
				FROM '._DB_PREFIX_.'product p
				'.Shop::addSqlAssociation('product', 'p').'
				INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
				p.`id_product` = pl.`id_product`
				AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa	ON (p.`id_product` = pa.`id_product`)
				'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
				'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
				LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
				LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
				Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
				LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
				WHERE p.`id_product` '.$product_pool.'
				AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))
				AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop='.(int)$context->shop->id.')
				'.($order_by ? 'ORDER BY  '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'';
				$result = $db->executeS($sql);
				if (!$result)
					$result_properties = false;
					$result_properties = Product::getProductsProperties((int)$id_lang, $result);
				return $result_properties;

if there is any documentation which could give me an insight how queries are build here and which tables they are accessing - it would be a great help to me.


Thanks in Advane.


Share this post

Link to post
Share on other sites

  • 11 months later...

I assume that Prestashop has these aliases by default.

I've read the developers guide and I've just found the standards of creating alias not their default MySQL aliases.


So I'm wondering if there is any place to check them all.

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

  • Create New...

Important Information

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