show categories and subcategories in order

Question

I have been having a lot of trouble in creating a SQL query to show the categories and subcategories from one table. I have the following columns:

category_id
category_name
category_parent
category_order

Basically what I want to produce as a result is something like this

parent category 1
sub category 1
sub category 2
parent category 2
sub category 1
sub category 2
sub category 3
parent category 3
sub category 1

if the category_order is set to 0, the categories or subcategories should be sorted based on the order they were created. For that I plan to sort by id.

if possible i want to use a UNION so that they are already in order and i would just have to loop. can anyone help me build a query for this.

actually I already have one that uses JOIN but the result is not as precise as what i want it.

this is my previous query:

SELECT
fcat.id fcat_id,
fcat.name fcat_name,
fcat.order fcat_order,
fcat.parent fcat_parent,
fsub.id fsub_id,
fsub.name fsub_name,
fsub.order fsub_order,
fsub.parent fsub_parent
FROM forum_categories AS fcat
LEFT OUTER JOIN forum_categories AS fsub ON fcat.id = fsub.parent
ORDER BY ISNULL(fcat.order) ASC, fcat.id ASC, ISNULL(fsub.order) ASC, fsub.id ASC

however, it does not sort the subcategories, because the parent categories and sub categories are joined. my query only sorts the parent.

Answer 1

select *, if (parent_category_id is NULL, id, concat(parent_category_id, id)) as o
from category
order by o

Answer 2

this is my answer for a depth of 3 subcategories level1, level2, and level3

SELECT
    main.id AS main_id,
    level1.id AS level1_id,
    level2.id AS level2_id,
    level3.id AS level3_id
FROM
    categories AS main
LEFT OUTER JOIN categories AS level1 ON level1.parent_id = main.id
LEFT OUTER JOIN categories AS level2 ON level2.parent_id = level1.id
LEFT OUTER JOIN categories AS level3 ON level3.parent_id = level2.id
WHERE
    main.parent_id = "ID-OF-MAIN-CATEGORY"
ORDER BY
    main_id,
    level1_id,
    level2_id,
    level3_id

your query would look something similar to this (schematically speaking)

hope that helps

Answer 3

I found a simple way to do it. It order by hierarchy and alphabetical.

First, I put the SQL needed to see the example:

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_parent` int(11) NOT NULL,
  `category_name` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

Now a fast insert to have results:

INSERT INTO categories (category_id, category_parent, category_name) VALUES (1, 0, 'Category number 1'), (2, 0, 'Category number 2'), (3, 0, 'Category number 3'), (4, 0, 'Category number 4'), (5, 0, 'Category number 5'), (6, 0, 'Category number 6'), (7, 2, 'First Subcategory of category 2'), (8, 2, 'Second Subcategory of category 2'), (9, 2, 'And third Subcategory of category 2 (has to appear first because alphabetic order)'), (10, 1, 'First Subcategory of category number 1'), (11, 1, 'Ans second Subcategory of category number 1 (has to appear first because alphabetic order)');

And the SQL sentence:

SELECT category_id,category_parent,category_name,
(CASE
    WHEN category_parent=category_id OR category_parent=0  THEN category_id
    WHEN category_parent<>category_id THEN category_parent
END) AS 'the_parent'
FROM categories ORDER BY the_parent ASC, category_parent ASC, category_name ASC

I hope it's useful

Answer 4

I had same problem and tested many example or Tomas and JTseng solutions. These were missing numeric ordering or subcategories ordering. I modified JTseng query and it is ok (for me) now, this way:

SELECT c.*,
  CASE WHEN isnull(is_par.cat_id) THEN c.cat_sort_order ELSE is_par.cat_sort_order END as sort_order,
  CASE WHEN isnull(is_par.cat_id) THEN c.cat_id ELSE is_par.cat_id END as catid ,
  CASE WHEN isnull(is_par.cat_id) THEN 0 ELSE c.cat_sort_order END as subcat_order
FROM `category` c
LEFT JOIN `category` is_par ON is_par.cat_id = c.cat_parent_id
ORDER BY  sort_order , catid, subcat_order

I wrote this solution after testing solutions where ordering was made by creating a concatenated string, made of three strings: the first one for main sort order of parent categories, second string was for grouping parent categories and their children, and the third string for ordering parent and their children according to child ordering (and to put parent cat in first place, they were given a virtual sub cat sort order set to 0). But this string ordering, gave an alphabetic ordering, not a numerical one. Thus a tranposed ordering strings to extra numeric fields as did JTseng this way:

• The first CASE WHEN is there to detect if a category is parent or not. If it is a parent, the parent sort order is kept, if it is a subcategory its sort order is replaced by parent's sort order
• The second CASE WHEN is used to replace in the way, a subcategory id by its parent category id (this is equivalent to JTseng groupid)
• The third CASE WHEN is used to set 0 to parent sub sort order and keep sub cat sort order

To have a view of the LEFT JOIN action, just execute this query in your phpMyAdmin page:

SELECT c.cat_id as ccatid,c.cat_sort_order as ccatsortorder, is_par.cat_id as isparcatid,is_par.cat_parent_id as ispar_catparentid,is_par.cat_sort_order as isparcatsortorder,
  CASE WHEN isnull(is_par.cat_id) THEN c.cat_sort_order ELSE is_par.cat_sort_order END as sort_order,
  CASE WHEN isnull(is_par.cat_id) THEN c.cat_id ELSE is_par.cat_id END as catid,
  CASE WHEN isnull(is_par.cat_id) THEN 0 ELSE c.cat_sort_order END as subcat_order
FROM `category` c
LEFT JOIN `category` is_par ON is_par.cat_id = c.cat_parent_id

Answer 5

I think the ordering is the interesting part here. In particular, the ordering within a category is interesting. I made a fiddle illustrating this: http://sqlfiddle.com/#!8/78059/3

Here's the query:

select * from (
  select c.*,
  coalesce(nullif(c.parent, 0), c.id) as groupID,
  case when c.parent = 0 then 1 else 0 end as isparent,
  case when p.`order` = 0 then c.id end as orderbyint
  from category c
  left join category p on p.id = c.parent
) c order by groupID, isparent desc, orderbyint, name

We can annotate each category with whether it's a parent or not. Then we can group the categories. Within each group, the order is dependent on the parent order. Here I'm doing an order based on the id when parent.order is 0. If it's not 0, then orderbyint is null, and then we would sort by name.

Answer 6

When the depth of the tree is limited to just 2:

select c1.*, 
       c2.*,
       if (c2.category_parent is NULL, "parent category", "sub category") as text
from cat c1 left join cat c2 
    on c1.category_id = c2.category_parent
order by c1.category_id, c2.category_id

You may use the condition c2.category_parent is NULL to test the level of the category.