sort by categories, "subcategories", products

[blackhorse]

two tables: category and product. top level category has the parent_id as 0, and subcategory's parent_id is its parent category's category_id.

table: category

name
category_id
parent_id
sort_order

table: product

name
product_id
category_id
sort_order

I only have two level categories. Now I want to list all my products, sort by top level (parent_id is 0) category's sort order first, then sort by subcategory's sort order (parent_id is not 0), then sort by product's sort order.

This is a general category-subcategory-product database. Someone must already have an efficient sql written. Could you share it with me? thanks!

Currently, I used two steps, select the top level categories by sort order first. And then select products by subcateory's sort order and product's order within each top level category. My database is small. So it will be OK. But for a large database, I want to use an efficient sql.

Only two level categories needed. Top level cateory parent id is set as 0, second level category's parent_id will be none zero. (I don't need a general sql for unlimited levels of categories. That might be too slow.)

Thanks a lot.

[tomhath]

I didn't test this because I'm not completely sure I have it right. But I think you have, in effect, have three tables:

category where parent_id is 0
category where parent_id > 0
product

So if we join them together:

select 
  c.category_id as category_id, 
  s.category_id as subcategory_id,
  p.product_id
from category as c
inner join category as s on s.parent_id = c.category_id
inner join product as p on p.category_id = c.category_id
order by c.sort_order, s.sort_order, p.sort_order

[blackhorse]

thanks! I will give a try!