I have a complpicated query for a shopping cart if a customer puts in the cart a product, the system must show some "additional" categories of products based on a db field
each product has a category and some "cross selling" (related) categories each product has a parent category and "siblings" in the same category
If a user adds a product in the cart, the cart should show its "cross selling" categories (something like "you may be interested in...")
but each product category in the cart must remove its "sibling" (in the same parent category) if they are "cross selling categories" of another product in the cart...
a "sibling" can be "cross selling" for its related sibling
let me explain in more simple terms:
a user buys a flight to London the system must then suggest related categories such as "Tower of london tickets" as well as "harry potter kids ticket", "harry potter adult tickets", "harry potter family tickets"
if the user adds one of the "harry potter" tickets, the system must then exclude all "harry potters" that are in the same category, even if they are related to the flight the customer bought
database side, everything is around a single "categories" table and a list of products
category table is like this (simplified):
category_id, parent_id, cross_selling categories
category_id is an integer.parent_idis an integer.cross_selling` categories is a string: list of integers like (188, 189, 200)
You will likely need to change how you store this information. Your database structure makes this nearly impossible to do in pure sql which this should be in.
The proper structure would be to take out your cross_selling column and create a new table...
(product_cross_selling) product_id, category_id
Given your example a product 1 would have 1, 188, 1,189, 1,200
Then you can simply have a query to lookup related items...
select item.* FROM products JOIN product_cross_selling ON item.category_id = category_id WHERE product_cross_selling.product_id = ?
Where ? is the id of the cart item that was added.
I hope this makes sense.