Jump to content

mysql query problem


emvoo
 Share

Recommended Posts

Hi there

 

I have some problem with mysql query.

 

Im trying to build table with all products ids, product names, manufacturers names and quantities per attribute in column per attribute 

 

Getting ids and names is easy. Problem starts when I try to JOIN columns per attribute and fill it with quantities. Some attributes are not assinged to product therefore dont have id_stock_available (quantity) in ps_stock_available

 

In php code im getting attibutes ids from db into array. Than i build query based on that array.

 

I wont be pasting whole query as its 465 lines long (including spaces between lines). Will paste beginning, some part of middle query and the and.

 

If I run a query with just $i = 1 (t1.id_attribute1)

SELECT
    ps_2product_lang.id_product,
    ps_2product_lang.name,
    ps_2category_lang.name AS manufacturer_name,
    t1.id_attribute1

FROM ps_2product_lang

LEFT JOIN ps_2product
	ON ps_2product_lang.id_product = ps_2product.id_product
    							
LEFT JOIN ps_2category_lang
	ON ps_2product.id_category_default = ps_2category_lang.id_category

LEFT JOIN ps_2category
	ON ps_2category_lang.id_category = ps_2category.id_category
    
JOIN
(
    SELECT
	ps_2stock_available.id_product AS id_product,
    	ps_2stock_available.quantity AS id_attribute1
    
    FROM ps_2stock_available
    
    JOIN
    	ps_2product_attribute_combination
   	
    ON
    	ps_2stock_available.id_product_attribute = ps_2product_attribute_combination.id_product_attribute
    
    WHERE
    	ps_2product_attribute_combination.id_attribute = 1
    
    ORDER BY
    	id_product
)
AS t1

ON
    ps_2product_lang.id_product = t1.id_product

WHERE ps_2product_lang.id_lang = 7
AND ps_2category_lang.id_lang = 7
AND ps_2product_lang.id_product > 12
AND (ps_2category.id_parent = 18 OR ps_2category.id_parent = 84)

ORDER BY
	manufacturer_name, name

i get products with id_attribute = 1 with column attribute1 filled with data (quantities)

 

Part below is being repeated in query as many times as there are attributes created in prestashop.

JOIN
(
    SELECT
	ps_2stock_available.id_product AS id_product,
    	ps_2stock_available.quantity AS id_attribute1
    
    FROM ps_2stock_available
    
    JOIN
    	ps_2product_attribute_combination
   	
    ON
    	ps_2stock_available.id_product_attribute = ps_2product_attribute_combination.id_product_attribute
    
    WHERE
    	ps_2product_attribute_combination.id_attribute = 1
    
    ORDER BY
    	id_product
)
AS t1

ON
	ps_2product_lang.id_product = t1.id_product

And now the more JOINs there are in query the less results i get from DB as if JOINs worked like ANDs.

 

As i mentioned already some attributes are not assigned to products therefore no quantities. In those id like to fill`em with NULL value but i havent included that in query (i thought that JOIN will return null`s to query results)

 

What am I doing wrong?

 

Thanks a lot in advance

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
 Share

×
×
  • Create New...

Important Information

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