Alec Smart Alec Smart - 2 months ago 6
MySQL Question

mySQL query for selecting children

I am not sure if this is possible in mySQL. Here are my tables:-

Categories table:


  • id

  • name

  • parent_id (which points to Categories.id)



I use the above table to map all the categories and sub-categories.

Products table:


  • id

  • name

  • category_id



The category_id in the Products table points to the sub-category id in which it belongs.

e.g. If I have Toys > Educational > ABC where ABC is product, Toys is Category and Educational is sub Category, then ABC will have category_id as 2.

Now the problem is that I want to use a SQL query to display all the products (in all the sub-categories and their sub-categories.. n level) for a particular category.

e.g.:

select * from categories,products where category.name = 'Toys' and ....


The above query should display the products from Educational also and all other sub categories and their subcategories.

Is this possible using a mySQL query? If not what options do I have? I would like to avoid PHP recursion.

Update: Basically I want to display the top 10 products in the main category which I will be doing by adding a hits column to products table.

Answer

What I've done in previous projects where I've needed to do the same thing, I added two new columns.

  • i_depth: int value of how deep the category is
  • nvc_breadcrumb: complete path of the category in a breadcrumb type of format

And then I added a trigger to the table that houses the category information to do the following (all three updates are in the same trigger)...

-- Reset all branches
UPDATE t_org_branches
	SET nvc_breadcrumb = NULL,
	i_depth = NULL

-- Update the root branches first
UPDATE t_org_branches 
	SET nvc_breadcrumb = '/', 
		i_depth = 0 
	WHERE guid_branch_parent_id IS NULL

-- Update the child branches on a loop
WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) 
	UPDATE tobA 
		SET tobA.i_depth = tobB.i_depth + 1, 
			tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' 
		FROM t_org_branches AS tobA
			INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) 
		WHERE tobB.i_depth >= 0 
			AND tobB.nvc_breadcrumb IS NOT NULL 
			AND tobA.i_depth IS NULL

And then just do a join with your products table on the category ID and do a "LIKE '%/[CATEGORYID]/%' ". Keep in mind that this was done in MS SQL, but it should be easy enough to translate into a MySQL version.

It might just be compatible enough for a cut and paste (after table and column name change).


Expansion of explanation...

t_categories (as it stands now)...

Cat Parent	CategoryName
1   NULL	MyStore
2   1		Electronics
3   1		Clothing
4   1		Books
5   2		Televisions
6   2		Stereos
7   5		Plasma
8   5		LCD

t_categories (after modification)...

Cat  Parent  CategoryName   Depth	Breadcrumb
1   NULL	MyStore			NULL	NULL	
2   1		Electronics		NULL	NULL
3   1		Clothing		NULL	NULL
4   1		Books			NULL	NULL
5   2		Televisions		NULL	NULL
6   2		Stereos			NULL	NULL
7   5		Plasma			NULL	NULL
8   5		LCD				NULL	NULL

t_categories (after use of the script I gave)

Cat  Parent  CategoryName   Depth	Breadcrumb
1   NULL	MyStore			0		/	
2   1		Electronics		1		/1/
3   1		Clothing		1		/1/
4   1		Books			1		/1/
5   2		Televisions		2		/1/2/
6   2		Stereos			2		/1/2/
7   5		LCD				3		/1/2/5/
8   7		Samsung			4		/1/2/5/7/

t_products (as you have it now, no modifications)...

ID   Cat Name
1   8	Samsung LNT5271F
2   7	LCD TV mount, up to 36"
3   7	LCD TV mount, up to 52"
4   5	HDMI Cable, 6ft

Join categories and products (where categories is C, products is P)

C.Cat Parent CategoryName   Depth	Breadcrumb	ID	 p.Cat	Name
1    NULL	MyStore			0		/			NULL NULL	NULL
2    1		Electronics		1		/1/			NULL NULL	NULL
3    1		Clothing		1		/1/			NULL NULL	NULL
4    1		Books			1		/1/			NULL NULL	NULL
5    2		Televisions		2		/1/2/		4	 5		HDMI Cable, 6ft
6    2		Stereos			2		/1/2/		NULL NULL	NULL
7    5		LCD				3		/1/2/5/		2	 7		LCD TV mount, up to 36"
7    5		LCD				3		/1/2/5/		3	 7		LCD TV mount, up to 52"
8    7		Samsung			4		/1/2/5/7/	1	 8		Samsung LNT5271F

Now assuming that the products table was more complete so that there is stuff in each category and no NULLs, you could do a "Breadcrumb LIKE '%/5/%'" to get the last three items of the last table I provided. Notice that it includes the direct items and children of the category (like the Samsung tv). If you want ONLY the specific category items, just do a "c.cat = 5".