Need help understanding Building Prestashop queries

[walkout]

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`,
				DATEDIFF(
				p.`date_add`,
				DATE_SUB(
				NOW(),
				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;
				else
					$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.

 

[vekia]

pl alias is an aliast of table:

AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'

it's a ps_lang

[edu.glider]

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.