Complex Find With Joins

Yii 1.1 General Discussion
1

I have the following tables

Product


    id                      int(11)         AUTO_INCREMENT                                                  

    name                    varchar(254)                                                                     

    slug                    varchar(254)                                                                     

    product_category_id     int(11)

A Product belongs to a category in ProductCategories. A category has sub category(self join)


    id                      int(11)         AUTO_INCREMENT                                                  

    name                    varchar(254)                                                                     

    slug                    varchar(254)                                                                     

    parent_id               int(11)

A Product also has a icon stored in ProductMedia.


    id                      int(11)         AUTO_INCREMENT                                                  

    url                     varchar(254)                                                  

    type                    enum('icon','banner','video')

What is the most efficient way to get all products with it’s associated icon that belong to a category including products in it’s sub category. All relations here are has_one.

2

SQL?

so




SQL * 

FROM Product INNER JOIN ProductCategories

ON Product.product_category_id=ProductCategories.id

and that’s all!!

If a product has only one icon you just add icon name in the the Product table and store the icon in a file.

3

This does not give products in sub categories

4

I assume there can be mutliple levels of sub-categories, so having only a parent-id might need several steps.

Following solutions might be possible for you:

** Note: I did not test any of these example sql queries below **

A.) limit the depth of the child-nesting. then you can create 1 query with 2 or 3 INNER JOINs


  SELECT * FROM Product p

  LEFT JOIN ProductCategory c1 ON c1.id=p.product_category_id

  LEFT JOIN ProductCategory c2 ON c2.parent_id=p.product_category_id



  SELECT * FROM Product p WHERE 

  EXISTS (SELECT 1 FROM ProductCategory c WHERE c.id=p.product_category_id)

  OR EXISTS (SELECT 1 FROM ProductCategory c1 INNER JOIN ProductCategory c2 ON c2.parent_id=c1.id WHERE c2.id=p.product_category_id)

  OR EXISTS (SELECT 1 FROM ProductCategory c1 INNER JOIN ProductCategory c2 ON c2.parent_id=c1.id INNER JOIN ProductCategory c3 ON c3.id=c2.parent_id WHERE c3.id=p.product_category_id)

pro: Easy and relatively fast

con: limited depth, not very "clean" solution

B.) use a loop (either in sql or in php)

pro: easy to write, flexible

con: worst performance

C.) use a path instead of parent-id, like "path = 1-3-8" would map to this hierarchy


     + root

       +- category 1

         +- sub-category 3

           +- sub-category 8

pro: fast, clear, easy

con: parent-chain is prone to errors when you move category to a different parent, needs string comparison to find items which might be slow for long paths or many items

D.) use a nested set for the categories. this has a "left" and "right" element, instead of parent. A bit difficult to get into but very fast and powerful for hierarchy-structures.

Above example might be: category 1 has "left:2", "right:6"


  SELECT * from category where left>=2 and right<=6

This would give you all categories, of any depth below category 1

pro: very fast and flexible, only solution that defines the item-hierarchy and also item-order

con: quite difficult to understand

I hope this did help you to decide which solution to go for. Every one has pro and cons. some are fast but complex, others are easy but slow, …

In your case I would go for the path-solution ©, because products are unlikely to move from one category to another, meaning the "difficult to update" minus is not relevant.

Also you can use the path to display a kind of breadcrumb if you like, etc.