Yii Framework Forum: Complex Find With Joins - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Complex Find With Joins Rate Topic: -----

#1 User is offline   awebdeveloper 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 55
  • Joined: 29-September 12

Posted 20 January 2013 - 04:00 PM

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.
0

#2 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 394
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 21 January 2013 - 12:38 PM

View Postawebdeveloper, on 20 January 2013 - 04:00 PM, said:

I have the following tables

Product
	id			int(11)		AUTO_INCREMENT	 	 	 	 	 	 	
	name			varchar(254)			 	 	 	 	 	 	 
	slug			varchar(254)			 	 	 	 	 	 	 
	product_category_id	int(11)

ProductCategories
	id			int(11)		AUTO_INCREMENT	 	 	 	 	 	 	
	name			varchar(254)			 	 	 	 	 	 	 
	slug			varchar(254)			 	 	 	 	 	 	 
	parent_id		int(11)


A Product also has a icon/image stored in ProductMedia.

What is the most efficient way to get all products with it's associated product media in a category and it's sub category. All relations here are has_one.


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.
Posted Image
0

#3 User is offline   awebdeveloper 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 55
  • Joined: 29-September 12

Posted 22 January 2013 - 10:39 PM

View Postfouss, on 21 January 2013 - 12:38 PM, said:

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.



This does not give products in sub categories
0

#4 User is offline   Philipp Stracker 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 09-February 13

Posted 20 February 2013 - 05:29 PM

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.
1

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users