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.


Link to comment
Share on other sites

  • 11 months later...

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