SQL Sorting of child elements based on parent elements
Join non-empty strings in PHP
Fulfill empty links using regexp and Sublime Text
Simple facebook test assignment is not so simple in fact
Suppress Prestashop 1.5.x update message in admin panel
Prestashop database query builder enchancement
Disabling snowflakes on websites
Tags autocomplete on Prestashop product edit page
Simulating noSQL in Django that uses SQL database

SQL Sorting of child elements based on parent elements

When you have questions and answers (for example) on your site, you want to sort and show answers under questions. My solution is based on few assumptions:

  • Questions and Answers are stored in the same SQL table
  • Questions and Answers IDs are chronological
  • Difference between a question and an answer is based on id_parent column
  • Question’s id_parent is zero
  • Answer’s id_parent is non-zero

Now normally if you sort by ID then you may end up with something like:

  • Question 1
  • Question 2
  • Answer to question 1
  • Question 3
  • Answer to question 2
  • Answer to question 1
  • Answer to question 3

But in fact we want:

  • Question 1
  • Answer to question 1
  • Answer to question 1
  • Question 2
  • Answer to question 2
  • Question 3
  • Answer to question 3

The solution is very simple – use new select column which will:

  • Keep id_parent if it is non-zero
  • Set id_parent as ID if it is zero

SQL query that will return questions and answers to them in right order is:

SELECT *, IF(`id_parent`>0,`id_parent`,`id_comment`) AS `id_super`
FROM `comments`
ORDER BY `id_super` ASC, `ID` ASC