Stumped by SQL Queries on the iso_product_categories table

[thyon]

Hey

I'm stumped by writing a query to retrieve automatically related items (for a custom module).

In the Isotope database, we've stored a category root which contains BOTH the category AND location in seperate trees.

Category Root =>
Adventures => Abseiling, Adventure Camps, Adventure Center Parys, .....
Holidays => Action Holidays, Adventure Holidays, Beach Holidays, Boat Holidays, Bush & Beach Holidays, ...
Accommodation => ...
Vehicles => ....
[attachment=0:2f8774x2]category-root.jpg[/attachment:2f8774x2]

Location Root =>
Botswana
Kenya
Lesotho
Madagascar
Malawi
Mauritius
Mozambique
Namibia
South Africa =>
Western Cape =>
Cape Garden Route & Klein Karoo
Cape Overberg
Cape West Coast
Cape Winelands
Cape Town & Cape Peninsula =>
Cape Town City Bowl

[attachment=1:2f8774x2]location-root.jpg[/attachment:2f8774x2]


In the custom related items query, the query is trying to retrieve items that are for example:
1. Retrieve items in the same location, but NOT in the same category e.g. an Accommodation product, must NOT show accommodation products, but include adventures, holidays and vehicles
2. Retrieve items in the same parent location AND in the same category e.g. an Adventure product, must show other adventure products, accommodation, holidays and vehicles.
etc.

So to write the query I need to get the products that have category IDs that are the same (I've already done the parent and get all the child IDs for each main category), but what is stumping me, is how to write the query to get a product that is BOTH in two categories

I have a list of the categories and the location, and in this case, the query must retrieve the product that is in the category AND in the location. In this case I'm unable to write the query, because the iso_product_categories table has to be queried over multiple rows and that doesn't seem to work.

iso_product_categories
prod_id cat_id
14 25 (category 1)
14 21 (category 2)
14 697 (location)

SELECT DISTINCT p.* FROM tl_iso_product_categories c, tl_iso_products p 
WHERE p.id=c.pid
AND
c.page_id IN (25, 697)

That won't retrieve the single product ID, but all the product IDs in all the categories AND locations.

I'm stumped!

[andreas.schempp]

Hey thyon,

It is quite complex to understand what you try to do. But shouldn't your query look like that?

SELECT p.*, COUNT(c.pid) AS encounters FROM tl_iso_product_categories c, tl_iso_products p 
WHERE p.id=c.pid
AND c.page_id IN (25, 697)
GROUP BY c.pid
HAVING encounters=2

[thyon]

Well I'll look at your query, as i don't understand it yet, but basically the question is really quite simple. If you have two category roots, e.g. brands and categories, then everything works dandy when you click on a brand OR a category, but this is to get a product when it's in TWO sub-categories. I don't think this wasn't planned in the original isotope. Maybe this is something to look at. Lost of stores have multiple category trees and allowing you to filter on multiple tree root points (in my case category and then location), will be quite useful.

[andreas.schempp]

How can the user view 2 different categories at the same time??

[thyon]

That's the problem. In this case, the Custom Related Products module I'm writing, will get products that are both in a specific location (which is a category) AND also in a specific category. In this setup, the client selects the product category from the category root (in the page tree) and then further down they also check the boxes for the location root (also in the page tree). So in this "store" the client has products listed in category and locations. So this is difficult to find the query to get a product that is both in a list of locations AND in a list of categories.

Why did you guys not use the comma separated method to store the pages field in the products database? Was there a specific reason for not doing it that way, because if it was stored like that, it would be really easy to do the query, as that was how it was done in the catalog.

[andreas.schempp]

1: Because that would not be "correct" SQL and more difficult to search.
2: My query should do what you need, does it not? I'm sure there is an SQL query to solve your problem.

[thyon]

im sure there is a solution. Unfortunately, your solution query will fail when the product is in multiple categories (holidays and accommodation) or in multiple locations (which is the case). in that case the categories might match, but not the location, but still the count value of 2 will match. I've not yet had your query return any rows, but I understand what you're trying to do.

I will need to create a query to match the category and then another to match the location and then intersect the products. It doesn't look like a single query will do that, so it's gonna be a slow process.

[andreas.schempp]

You can try FIND_IN_SET(), but that query is a reeeal hack:

SELECT * FROM tl_iso_products p 
WHERE 
FIND_IN_SET(25, (SELECT GROUP_CONCAT(page_id SEPARATOR ',') FROM tl_iso_product_categories c WHERE p.id=c.pid)
AND FIND_IN_SET(697, (SELECT GROUP_CONCAT(page_id SEPARATOR ',') FROM tl_iso_product_categories c WHERE p.id=c.pid))

maybe it would even work like this:

SELECT * FROM tl_iso_products p 
WHERE 
(SELECT COUNT(*) AS matches FROM tl_iso_product_categories c WHERE p.id=c.pid AND page_id='25') > 0
AND (SELECT COUNT(*) AS matches FROM tl_iso_product_categories c WHERE p.id=c.pid AND page_id='697') > 0

[thyon]

That last one was about the same as I was just developing and that one works right, as it intersects the two sets. I've just adapted it to include a IN () to check a parent and all it's children ids. The breakthrough was to swap the query table to tl_iso_products instead.

Thanks for your help.