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)
That won't retrieve the single product ID, but all the product IDs in all the categories AND locations.Code: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)
I'm stumped!
Bookmarks