Solved

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

Posted on 2009-03-30
21
552 Views
Last Modified: 2013-12-12
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
Comment
Question by:m00b13s
  • 11
  • 10
21 Comments
 
LVL 15

Expert Comment

by:oobayly
ID: 24018160
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24018318
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24018352
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24018393
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24018818
Just realised, if you change your reworked version of the query to:
SELECT DISTINCT ...

This will return on unique rows
0
 
LVL 1

Author Comment

by:m00b13s
ID: 24018857
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24018939
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24019208
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24019529
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24019872
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:m00b13s
ID: 24020095
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24020113
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24020150
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24020240
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24020687
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24020706
Ignore the xml? tags, no idea how they got inserted in there.
0
 
LVL 1

Author Comment

by:m00b13s
ID: 24022263
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24022495
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
 
LVL 1

Author Comment

by:m00b13s
ID: 24022641
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
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24027072
"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
 
LVL 1

Author Closing Comment

by:m00b13s
ID: 31564289
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
php image upload 3 25
Page showing diff display 4 19
Converting SQL server date to string 3 15
wordpress issue 2 18
This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now