Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Get list of products having pairs responding their criterias

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
leakim971
Asked:
leakim971
  • 4
  • 2
3 Solutions
 
Frosty555Commented:
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
 
leakim971PluritechnicianAuthor Commented:
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
 
johanntagleCommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
johanntagleCommented:
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
 
leakim971PluritechnicianAuthor Commented:
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
 
leakim971PluritechnicianAuthor Commented:
>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
 
leakim971PluritechnicianAuthor Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now