Development

iOS meditation shortcut Scriptable Todoist widget that selects a top task from a filter Using Google API (gapi) in Chrome Extensions Automatically prepend issue tag to commit message Linting only changed files with Gulp.js Website visual diffing Simple MobX Simple Redux base64 encoder for node-sass Easy REST API data validation using Django Forms

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