What I love to achieve is
a: display all Products which are in all from the selected category's
b: return / update the course list with category's available according to selection
I love products to become saved and become discovered by utilisation of the adjacency list model or nested sets.
I have played around with with both and could use advice what will be the perfect for this situation.
Presently I am using (testing with) the adjacency list model such as this:
items: ID | item_name ==================== 1 | car 2 | boat 3 | bike items_cats: (many to many) iid | cid ==================== 1 | 1 1 | 2 1 | 4 1 | 7 2 | 1 2 | 3 2 | 4 2 | 7 3 | 1 3 | 3 3 | 4 3 | 8 categorys: ID | cat_name | parent_id ======================== 1 | safety: | 0 (0 = no parent) 2 | safe | 1 3 | dangerous | 1 4 | fun: | 0 5 | a bit | 5 6 | boring | 5 7 | funny | 5 8 | cool | 5
So its not a problem to obtain products according to cid but how does one:
1- Display all products who've cat id: cid 7 (funny)?
2- return (array/object) of category's who've products which contain cid 7?
Can you all do that in a single query or would two become more efficient?
3- Display all products who've cat id: cid 7 and also contain cat id '3' (harmful)
4- return (array/object) of category's who've products which contain cid 7 and cid 3?
For choosing on multiple category's I discovered the flowing solution. Is a high quality one and would there be to achieve any performance particularly when the amount of category's grow?
SELECT DISTINCT t1.product_id, t1.category_id FROM items_cats t1 INNER JOIN items_cats t1b ON t1.iid =t1b.iid WHERE t1.cid=3 AND t1b.cid=7
To obtain a listing of products which have category ID = 7, begin with your many:many table
select i.item_name from items_cat ic join items i on ic.iid = i.id where ic.cid = 7
to obtain all groups connected with anything which has the course ID of seven, you are able to expand from the foremost and get groups connect for individuals item IDs
select DISTINCT ic2.cid, c.cat_name, coalesce( CatParent.cat_name, "" ) as ParentCategoryName from ( select distinct ic.iid from items_cat ic where ic.cid = 7 ) QualifiedItems JOIN items_cat ic2 on QualifiedItems.iid = ic2.iid JOIN categorys c on ic2.cid = c.id LEFT JOIN categorys CatParent on c.parent_id = CatParent.ID
For 3 and 4, it might be similar, but to qualify BOTH (or anytime, several), you have to apply an OR, an organization BY and make certain the final count matches individuals you had been attempting to qualify
select i.item_name from items_cat ic join items i on ic.iid = i.id where ic.cid in( 3, 7 ) group by i.item_name having count(*) = 2
So that you can better understand and apply these concepts, I'll leave the final choice for you to implement... Should you really find yourself in trouble, tell me... :)