Mysql two level thread ordered by date

Question

I'm having a few issues with a mysql query. The table structure is the following :

+----+--------+---------------------+
| id | parent |      datetime       |
+----+--------+---------------------+
|  1 | null   | 2014-03-01 09:14:02 |
|  2 | 1      | 2014-03-01 09:38:32 |
|  3 | 1      | 2014-03-01 09:45:52 |
|  4 | 2      | 2014-03-01 09:42:23 |
|  5 | null   | 2014-03-01 09:47:42 |
|  6 | null   | 2014-03-01 09:33:01 |
|  7 | 5      | 2014-03-01 09:54:39 |
+----+--------+---------------------+

I would like a 2 level result with parents ordered by datetime and in between if any children ordered by datetime. The data would to be listed as follows :

+----+--------+------------------------+
| id | parent |        datetime        |
+----+--------+------------------------+
|  1 |  null  |  2014-03-01 09:14:02   |
|  2 |   1    |    2014-03-01 09:38:32 |
|  4 |   2    |    2014-03-01 09:42:23 |
|  3 |   1    |    2014-03-01 09:45:52 |
|  5 |  null  |  2014-03-01 09:47:42   |
|  7 |   5    |    2014-03-01 09:54:39 |
|  6 |  null  |  2014-03-01 09:33:01   |
+----+--------+------------------------+

I can order parents by datetime, with the children in between but not by datetime (the children).

GolezTrol · Accepted Answer · 2014-03-01T20:20:07.453

With UNION ALL, you can select the parents first, then all the children. In the two unioned queries you can select extra information to help you sort. In this case, I introduce a type (1 is parent, 2 is child), and an orderdatetime, which is basically the datetime of the parent in case of a child.

SELECT
  x.id,
  x.datetime
FROM
  (SELECT
    p.id,
    p.datetime,
    1 AS itemtype,
    p.id as sortparent,
    p.datetime AS sortdatetime
  FROM
    YourTable p
  WHERE
    p.parent IS NULL
  UNION ALL
  SELECT
    c.id,
    c.datetime,
    2 AS itemtype,
    c.parent as sortparent,
    p.datetime AS sortdatetime
  FROM
    YourTable p
    INNER JOIN YourTable c ON c.parent = p.id) x
ORDER BY
  x.sortdatetime,
  x.sortparent
  x.itemtype,
  x.datetime

For the sorting, I sort:

First by sortdatetime, which is the datetime of the parent in case of a child. This is the basic sorting you asked for. This sorts all children together with parents of the same time.
Then I sort by the introduced sortparent to group parents and children together in case two parents have the same datetime. I don't know if that is possible, but better safe than sorry. Now you now for sure that the children are with their actual parent and not another parent that happens to have the same datetime.
Then I sort by the introduced itemtype to move the parent to the top of the 'family' group.
And finally sort by datetime, to sort the children of the parent by their own date. I don't know if you need that, but otherwise they would be sorted at random, and I hate random in query results. :)

Just ran the query not getting expected results, pretty close though, `id = 4` is being seen as top level element probably due to the fact the it's 3 levels down. — Sparkup, Mar 01 '14 at 20:19
Ah. I completely missed that. I read '`two level`' in the question and didn't expect there to be a third. If that means there can be a fourth as well, you're in trouble. In Oracle you can 'easily' use `connect by` to write truly recursive queries, but in MySQL that's hard, if possible at all. Maybe you can search for a `connect by` equivalent for MySQL. — GolezTrol, Mar 01 '14 at 20:22
Computer says no: http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql — GolezTrol, Mar 01 '14 at 20:25
Going to add a column call `thread` where the value is the `id` of `parent` `null` in which case your answer works. So thank you ! — Sparkup, Mar 01 '14 at 20:30

score 0 · Answer 2 · answered Mar 01 '14 at 20:15

Could save 'datetime' as time()?

+----+--------+------------------------+
| id | parent |        datetime        |
+----+--------+------------------------+
|  1 |  null  |     1388615716         |
|  2 |   1    |     1388697666         |
|  4 |   2    |     1388790487         |
|  3 |   1    |     1388952461         |
+----+--------+------------------------+

And sort through ORDER BY datetime.

Mysql two level thread ordered by date

2 Answers2