CODEWITHSUNDEEP
mysql
Sparkup
Sparkup

Reputation: 3754

Mysql two level thread ordered by date

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).

Upvotes: 0

Views: 56

Answers (2)

GolezTrol
GolezTrol

Reputation: 116140

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. :)

Upvotes: 1

Aydomir
Aydomir

Reputation: 21

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.

Upvotes: 0

Related Questions