• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Product catalog query that returns master products where criteria applied to child columns

I have 3 tables as follows:

content
content_products
content_associations

content is the central content table and represents various content, content_products is an extension table, i.e. it stores details such as product_type, product_price, product_category to extend content. the combination of content and content_products joined on content_id is a product.

There are 2 types of product_type, these are Master and Variant. A Master product stores product_category but does not store product_price. A Varient product does not store product_category but does store price.

The relationship between a master and its variants is mapped by the table content_associations using from_content_id and to_content_id.

So a single product will have 1 Master product entry and as many Variant product entries as appropriate. They are then linked.

I am in need of a query that can return all Master product rows where a criteria is applied to the set of associated Variants.

For example "find all Master products that have child Variant products with a price > 10".

The query should return all columns from content and content_products for the Master only where the variant conditions hold true.
0
m00b13s
Asked:
m00b13s
  • 11
  • 10
1 Solution
 
oobaylyCommented:
I'm afraid I couldn't quite follow your schema for the association table, so assumed the following
content_id is used to link content to content_associations
product_id is used to link content_associations to content_products

Hopefully you'll understand what I'm doing here:
SELECT content.*, master_prod.*
FROM content_products AS master_prod
INNER JOIN content_associations AS master_assoc ON master_prod.product_id = master_assoc.product_id
INNER JOIN content ON master_assoc.content_id = content.content_id
INNER JOIN content_associations AS variant_assoc ON content.content_id = variant_assoc.content_id
INNER JOIN content_products AS variant_prod ON variant_assoc.product_id = variant_prod.product_id AND variant_prod.price > 10
WHERE master_prod.price IS NULL

Open in new window

0
 
m00b13sAuthor Commented:
Hi and thanks. I am not getting total success with the query you suggest. Firstly, let me clarify the associations table. It contains from_content_id and a to_content_id columns. The from_content_id is the master's and the to_content_id the variant.

So I reworked your query joining on those columns. I got the query to run but no results come back.

If I take off half the query then I get back master rows duplicated by the number of variants they have which seems like progress, but the as soon as you add the variant joins on it causes the query to find nothing.




REWORKED
--------
 
SELECT content.*, master_prod.*
FROM content_attribs_product AS master_prod
INNER JOIN content_associations AS master_assoc ON master_prod.content_id = master_assoc.assoc_from_content_id
INNER JOIN content ON master_assoc.assoc_from_content_id = content.content_id
INNER JOIN content_associations AS variant_assoc ON content.content_id = variant_assoc.assoc_to_content_id
INNER JOIN content_attribs_product AS variant_prod ON variant_assoc.assoc_to_content_id = variant_prod.content_id AND variant_prod.product_price_gbp > 10
WHERE master_prod.product_price_gbp IS NULL
 
VERSION THAT RETURNS MASTER ROWS X VARIANT COUNT (DUPE MASTER ROWS)
-------------------------------------------------------------------
 
SELECT content.*, master_prod.*
FROM content_attribs_product AS master_prod
INNER JOIN content_associations AS master_assoc ON master_prod.content_id = master_assoc.assoc_from_content_id
INNER JOIN content ON master_assoc.assoc_from_content_id = content.content_id

Open in new window

0
 
oobaylyCommented:
Can you post the output form the following commands, it'll be a lot easier to see what keys and and fields are being used.

SHOW COLUMNS FROM content;
SHOW COLUMNS FROM content_attribs_product;
SHOW COLUMNS FROM content_associations;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
m00b13sAuthor Commented:
how's this :)
content
-------
 
content_id  	int(11)  	NO  	PRI  	NULL  	auto_increment
content_created_on 	timestamp 	NO 	  	CURRENT_TIMESTAMP 	 
content_updated_on 	timestamp 	YES 	  	NULL 	 
content_published_on 	timestamp 	YES 	  	NULL 	 
content_title 	text 	NO 	  	  	 
content_summary 	text 	YES 	  	NULL
 
content_attribs_product
-----------------------
 
content_id  	int(11)  	NO  	MUL  	   	 
product_type 	enum('Master','Variant') 	NO 	MUL 	Standard 	 
product_sku 	varchar(32) 	NO 	PRI 	  	 
product_description 	longtext 	NO 	  	  	 
product_price 	decimal(10,2) 	NO 	  	0.00
 
 
content_associations
--------------------
 
assoc_id  	int(11)  	NO  	PRI  	NULL  	auto_increment
assoc_from_content_id 	int(11) 	NO 	MUL 	  	 
assoc_to_content_id 	int(11) 	NO 	MUL 	  	 
assoc_sort_order 	int(11) 	NO 	 

Open in new window

0
 
oobaylyCommented:
Just realised, if you change your reworked version of the query to:
SELECT DISTINCT ...

This will return on unique rows
0
 
m00b13sAuthor Commented:
that's true but it only makes 1/2 of the query work:

SELECT DISTINCT hako_content.*, master_prod.*
FROM hako_content_attribs_product AS master_prod
INNER JOIN hako_content_associations AS master_assoc ON master_prod.content_id = master_assoc.assoc_from_content_id
INNER JOIN hako_content ON master_assoc.assoc_from_content_id = hako_content.content_id

this returns master products BUT does not return master products based on a criteria query relating to the variants of the master which would be the 2nd 1/2 of your query

INNER JOIN hako_content_associations AS variant_assoc ON hako_content.content_id = variant_assoc.assoc_to_content_id
INNER JOIN hako_content_attribs_product AS variant_prod ON variant_assoc.assoc_to_content_id = variant_prod.content_id
AND variant_prod.product_price_gbp > 10
WHERE master_prod.product_price_gbp IS NULL

when adding the 2nd half no results are returned, so this goes back to the original question, just how do you return master rows based on a sub-query of the variants :S
0
 
oobaylyCommented:
Forgot about that requirement. Hopefully this would do the job.
Just a suggestion, when asking a question, please don't keep changing names of tables & fields in your comments: it causes a bit of confusion if when trying to understand your example queries.
SELECT DISTINCT content.*, master.*
FROM content_attribs_product AS variant
INNER JOIN content ON variant.content_id = content.content_id
INNER JOIN content_associations ON variant.content_id = content_associations.assoc_to_content_id
INNER JOIN content_attribs_product AS master ON content_associations.assoc_from_content_id = master.content_id
WHERE variant.product_price > 10

Open in new window

0
 
m00b13sAuthor Commented:
thanks so much, it seems to be getting there but not quite right just yet ;) the query above is returning the variant rows and not master rows.

but i need only the master rows that satisfy the variant criteria associated with them

sorry :p i am trying this end also to modify what you have provided but just can't get it right yet
0
 
oobaylyCommented:
Yes, it will do, as the joins aren't being told which product type to use, oops
However, seeing as content_id is specified in every table, I don't think the associations table needs to be used, saving out on a join.

Finally, seeing product can only be linked to a single content item, why use the associations table at all? The only time you need an association table like this is when a product can be linked to different content items.
SELECT DISTINCT content.*, master.*
FROM content_attribs_product AS variant
INNER JOIN content ON variant.content_id = content.content_id
INNER JOIN content_associations ON variant.content_id = content_associations.assoc_to_content_id AND variant.product_type = 'Variant'
INNER JOIN content_attribs_product AS master ON content_associations.assoc_from_content_id = master.content_id AND master.product_type = 'Master'
WHERE variant.product_price > 10
 
-- Query without using associations.
SELECT DISTINCT content.*, master.*
FROM content_attribs_product AS variant
INNER JOIN content_attribs_product AS master ON variant.content_id = master.content_id AND master.product_type = 'Master'
INNER JOIN content ON variant.content_id = content.content_id
WHERE variant.product_type = 'Variant'
AND variant.product_price > 10

Open in new window

0
 
m00b13sAuthor Commented:
The queries look good but I'm afraid both queries run but return no rows :S This is just beyond my SQL, do you have any idea why no rows are coming back this time?

The reason I use the association table is for sequencing of the associations. I can control their order which is important.
0
 
m00b13sAuthor Commented:
i beg your pardon, the query does work and it does bring back only master detail, however it is still bringing back as many master rows as there are variants associated with it. i think this must be because the variant content table columns contains unique information so the distinct is unable to work. i am going to try to par the query columns down to those that would be unique and hopefully the distinct will then work
0
 
m00b13sAuthor Commented:
no paring columns down won't work. so the situation is that the query is working well for pulling out the master product data according also to the variant criteria.

the bit that is not working is that the content table fields are being taken from the variants.

is there a way to make the results bring back the content fields for the master only (like it is now doing for the product_attribs fields)?
0
 
oobaylyCommented:
Distinct should only done on fields returned. Are you selecting fields from the variant aliased table?
Regard the ordering, surely moving the order field into the product table would be the equivalent? Granted the Master product record wouldn't need a ordering value, but that field could be set to allow nulls. Of course if your app is written around that schema already, it'll be more hassle to change it.
0
 
m00b13sAuthor Commented:
so the query down below is the one i am working with - the first solution you provided.

the main select statement SELECT DISTINCT content.*, mastr.*

however is bringing back values from the content table that relate to the variants of the master record. what i need is to have the master's content table values coming back.





      from content  ----> from content_attribs_product ------->
123 | variant title 123 | master sku 001 | master material wood
124 | variant title 124 | master sku 001 | master material wood
125 | variant title 125 | master sku 001 | master material wood
126 | variant title 126 | master sku 001 | master material wood
 
and what i want instead is
 
222 | mastr title | master sku 001 | master material wood
 
QUERY
------
 
SELECT DISTINCT content.*, mastr.*
FROM content_attribs_product AS variant
INNER JOIN content ON variant.content_id = content.content_id
INNER JOIN content_associations ON variant.content_id = content_associations.assoc_to_content_id AND variant.product_type = 'Variant'
INNER JOIN content_attribs_product AS mastr ON content_associations.assoc_from_content_id = mastr.content_id AND mastr.product_type = 'Configurable'
WHERE variant.product_price_gbp < 100

Open in new window

0
 
oobaylyCommented:
Erm, just tried replicating your schema on my local server, filled in some data, the way I'd expect from the table schemas that you provided, and came across two issues:
1 I didn't know what to put in the associations table, so I'm obviously missing something here
2 Ignored the associations table and ran the following query on the data that I inserted. The queries ran the way I expected.

Basically, I'm making the assumption that:
  •  content_attribs_product.content_id
  •  content_associations.assoc_from_content_id
  •  content_associations.assoc_to_content_id
all relate to content.content_id, now I'm not convinced that this is correct.

Sooo, can you draw a relational diagram and attach it to this thread, because to be quite honest I don't think I can help while not understanding how your tables work together.
0
 
oobaylyCommented:
Ignore the xml? tags, no idea how they got inserted in there.
0
 
m00b13sAuthor Commented:
alright i've put up a mini schema and sample data as an image which should illustrate what i am dealing with :)
Product-Schema.gif
0
 
oobaylyCommented:
A picture is worth a thousand words :)
This one works for me using the sample data
SELECT DISTINCT content.*, master.*
FROM content_attribs_product AS variant
INNER JOIN content_associations ON variant.content_id = content_associations.assoc_to_content_id
INNER JOIN content_attribs_product AS master ON content_associations.assoc_from_content_id = master.content_id AND master.product_type = 'Master'
INNER JOIN content ON master.content_id = content.content_id
WHERE variant.product_price > 10

Open in new window

0
 
m00b13sAuthor Commented:
hi,

could you attach your mysqldump by any chance? the query is not returning any results for me still and i want to double check your schema matches what i have. i notice you no longer have any restriction for 'Variant' product type?

by the way, i really appreciate how much effort you are putting into this, it's beyond what i would expect of anyone so thanks so much for sticking with me.
0
 
oobaylyCommented:
"i notice you no longer have any restriction for 'Variant' product type?"
Correct, as content_associations.assoc_to_content_id is defined as referencing Variant items only.
In fact, I was able to cut out the master.product_type = 'Master' constraint as that too was unnecessary.

I haven't bothered including the foreign key contraints, but that won't affect the  query

SELECT  DISTINCT content.*, master.*
FROM content_attribs_product AS variant
INNER JOIN content_associations ON variant.content_id = content_associations.assoc_to_content_id
INNER JOIN content_attribs_product AS master ON content_associations.assoc_from_content_id = master.content_id
INNER JOIN content ON master.content_id = content.content_id
WHERE variant.product_price > 10

Open in new window

content.sql.txt
0
 
m00b13sAuthor Commented:
You're a genius and I'd buy you a beer if I knew you. Thanks so much for your help, this last query works like a dream!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now