Solved

Get list of products having pairs responding their criterias

Posted on 2012-04-07
7
413 Views
Last Modified: 2012-04-10
Hello,

I've a list of products in a web page.
For each product, I need to propose product with some criterias depending the selected product of my list.
Some products of my list don't have product answering their criteria : I need to remove these products in my list for which there's no products answering their criteria.

For example :
for blue t-shirt, I can propose blue pants
for red t-shirt, I can propose red pants or blue pants
but for swimsuit, I can't propose pants.
I don't want to see swimsuit in my list because there's no available product for it.

CREATE OR REPLACE VIEW testview AS SELECT d1.* FROM  `products` d1,  `products`d2
WHERE d2.price >= 0.00
AND d2.price <= (d1.price/2) 
AND CASE d1.color
	WHEN 'R' THEN d2.color IN ('Y','U')
	WHEN 'G' THEN d2.color IN ('Y','V')
	WHEN 'B' THEN d2.color IN ('U','V')
	WHEN 'Y' THEN d2.color IN ('R','G')
	WHEN 'U' THEN d2.color IN ('R','B')
	WHEN 'V' THEN d2.color IN ('G','B')
END
AND d2.size = d1.size 
AND d2.offset >= (((100-3)/100) * d1.offset)
AND d2.offset <= (((100+3)/100) * d1.offset)
AND d2.power>= (((100-3)/100) * d1.power)
AND d2.power <=(((100+3)/100) * d1.power)
AND CASE d1.width
	WHEN 'AAAA' THEN d2.width IN ('BBBB','CCCC')
	WHEN 'BBBB' THEN d2.width IN ('CCCC','DDDD')
	WHEN 'CCCC' THEN d2.width IN ('DDDD','EEEE')
	WHEN 'DDDD' THEN d2.width IN ('AAAA','BBBB')
END

Open in new window


I don't want to use PHP code, but, if possible an optimized MySQL view

Thanks for your help
0
Comment
Question by:leakim971
  • 4
  • 2
7 Comments
 
LVL 31

Accepted Solution

by:
Frosty555 earned 250 total points
ID: 37820507
I can see this is going to get very messy very fast. The criteria for determining which products to place together can vary wildly depending on a lot of different strategies you can employ (what's in style right now? what is in stock? what vendor arrangements do you have?).

Wouldn't it make more sense to have a many-to-many relationship between your products that lets you define which products can be proposed in combination with other products?

E.g.

TABLE   product_suggestions
   viewed_product_id    (int)
   suggested_product_id     (int)
   comment    (varchar)

Then on your webpage you can do something like:

SELECT * FROM product_suggestions 
    INNER JOIN products AS viewed ON viewed.id = viewed_product_id
    INNER JOIN products AS suggested ON suggested.id = suggested_product_id
WHERE viewed_product_id = '1234'

Open in new window


Then, if necessary, randomly pick 4 or 5 out of the list to present to your viewer.

This gives you complete flexibility over what products are related to others, and you can even specify a little comment like  "Goes great with these pants!" to show on the screen next to the product suggestion for your customers. The MySQL query would be lightning fast so that page load time is not affected, and you could even do things like order the suggested products by popularity, or only show suggested products that are in stock (assuming you track that in your products table), simply by including it in your WHERE or ORDER BY clause.

The hardest part would be creating that list of relationships between your products, but you can write scripts and design administration pages that let you automate that task or make it easier for you to do. They only need to be done once for each set of products you bring into the system, and it can be somebody's job to strategically decide what products to place together (based on color, type, popularity, vendor agreements or whatever other strategic marketing you want to employ)
0
 
LVL 82

Author Comment

by:leakim971
ID: 37820513
Hello Frosty555,

Yes,
product_suggestions

Open in new window

table is what I'm looking for. I want to create a VIEW instead a table for multiple reasons :
- I don't really have the hand on the code to create/add and INSERT INTO product_suggestions
- the products table are frequently updated

>The hardest part would be creating that list of relationships between your products

Yes, this is the origin  of my question :)

Thanks for your help.
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 250 total points
ID: 37820527
Kinda tired right now so I can't process the needed logic, but I just want to say you might want to revisit your design - instead of "computing" for the related product upon selection, you might want to store the related products to a table.  Something like:

product_id | related_product_id
1 | 2
1| 3
1| 5
2| 1
2|6
...

In the above product with id=1 is related to products with id's 2,3,5.  Another row shows the reverse relation that product 2 is related to product 1, etc.  This way you just need to do a simple "select p.* from products p join related_products_table r on p.product_id=r.related_product_id where r.product_id=chosen_product_id".  The advantage of this is you only use a complex process to determine related products once, then just use a simple select to pull them out.  Server load is lighter and website response time is (a lot) faster.

Now maybe you already have lots of products and/or you don't want to have to manually determine related items for each newly added product, so you still need a good SQL to determine what you need to put to the related_products_table.  But as I said, I need to take a breather now.  Maybe if other experts haven't chimed in by the time I get back, I can take another look at it.

Oh, in case you observe/celebrate it, Happy Easter! =)
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 250 total points
ID: 37820537
Ah, it took me a while to type my post above I see somebody already said the same thing.  Anyway, I still recommend against a view, because it will still compute every time a product is chosen (a view will basically call the SQL that created it every time it is queried).  Once you have the correct logic for the SQL to determine related products, maybe you can employ triggers that use it to populate/update the related_products_table whenever a new product is added/deleted/modified.
0
 
LVL 82

Author Comment

by:leakim971
ID: 37820545
Hello johanntagle,

Thanks a lot for your comment.
>Now maybe you already have lots of products and/or you don't want to have to manually determine related items for each newly added product, so you still need a good SQL to determine what you need to put to the related_products_table

Yes, I know it's a crappy design... Helas, I can't change it...

Yes I can use PHP to build the related_product table but it's not really my problem.

Currently the product are displayed in a list, as usual with a query like :
SELECT * FROM products

Open in new window


I would like to replace
products

Open in new window

by products_having_related_product to replace my previous script by :
SELECT * FROM products_having_related_product products

Open in new window

0
 
LVL 82

Author Comment

by:leakim971
ID: 37820547
>Once you have the correct logic for the SQL to determine related products, maybe you can employ triggers that use it to populate/update the related_products_table whenever a new product is added/deleted/modified.

Yes, I'm looking for this logic, the script in my original question is translated from PHP code...

and... Happy Easter to you!
Thanks
0
 
LVL 82

Author Comment

by:leakim971
ID: 37820623
Ok, look like my original script work as I want after some updates...
I will let this post open some hour before thank you for your help to see if someone can update it to run faster (give me some idea(s))
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

705 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

21 Experts available now in Live!

Get 1:1 Help Now