SQL Query to retrieve Products by id_category

[MadFrenchie]

[sOLVED]

 

Heya folks,

I spent the last days trying to figure out a tricky issue... 
No way to set up an SQL query to retrieve my products by basing my query on the "id_category". At each try I keep getting more data than expected whereas in the back office I get the 6 wanted products.

I  have no choice except doing that by SQL. This is what I have so far:
 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product)

INNER JOIN ps_store_shop pss on pss.id_store = 904

WHERE pl.id_lang = 2

AND cl.id_lang = 2

AND p.id_shop_default = 1

AND c.id_shop_default = 1

AND c.id_category = 8

AND ps.visibility IN ("both", "catalog")

AND ps.active = 1

AND pl.id_shop = 6 

GROUP BY p.id_product


Any help in these regards would be much appreciated.

Best Regards,
MF

Edited December 17, 2014 by MadFrenchie (see edit history)

[shacker]

i use this to pass categories :

 

SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default 

                     

        FROM `'._DB_PREFIX_.'category_product` cp 

        LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product` 

        LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1) 

        LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.$langs.') 

        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.$langs.') 

        LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) 

        LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.$langs.') 

        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`) 

        LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.$langs.') 

        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) 

        WHERE cp.`id_category` IN ('.$skipcat.') AND p.`active` = 1 '.(($skipman != null) ? 'AND m.id_manufacturer IN ('.$skipman.')' : '').'

        GROUP BY cp.`id_product` 

    ORDER BY '.$sort.' 

        LIMIT '.$nb.'';

[MadFrenchie]

Thanks mate, but what does $skipman stands for in the clause where?
And is the tax really necessary?

[MadFrenchie]

I still keep getting more data than wanted... : / While in the backoffice I have only 6 products based on my id_category
There must be something freaking weird around...

[shacker]

$skipman is manufacturers filter by id, and tax really not, its only an complete example.

maybe you have more products in shop (if is multishop) 

[MadFrenchie]

Exactly, I have a "multishop" platform, and I'm looking for the products of 'one' specific shop ! What am I supposed to add?
Again, thanks a bunch for the help mate !

[shacker]

this  , the id shop

 

  WHERE cp.`id_category` IN ('.$skipcat.') AND p.`active` = 1 '.(($skipman != null) ? 'AND m.id_manufacturer IN ('.$skipman.')' : '').'

        AND ps.id_shop = '.$context->shop->id.'  

[MadFrenchie]

It still doesn't make it :

This is what I get (Query above):


And this is what I'm supposed to have:

[shacker]

add the cp.* in the SELECT and ps too like 

 

  SELECT p.*,cp.*, ps.*

 FROM `'._DB_PREFIX_.'category_product` cp 

        LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product` 

        LEFT JOIN `'._DB_PREFIX_.'product_shop` ps ON p.`id_product` = ps.`id_product` 

 

edit the product with id 81 to see in what category is

[MadFrenchie]

In the backoffice, there is no way to retrieve the product id 81 by using the input set up out there.
However, it seems to be belonging to the category 8.

This is what I get after the modifications you suggested:

[shacker]

ok, maybe this product is in product table, but not in product_shop. you can try remove product table from the sql and replace for product_shop only

[MadFrenchie]

Thanks for the tips bro', I finally gave up using existing objects instead:
 

$objCat = new Category($row['id_category']);

$secResult[] = $objCat->getProducts(2, 1, 100);


This made my life way easier.
Thanks a bunch again.

MF

[shacker]

remember to change the title to solved, edit the first post.

regards

[MadFrenchie]

Done, Thanks.

Regards,
MF

[shacker]

Thanks:)