JPA NamedQuery order by not selecting tuples if parent is null

Question

I have a table, having a self-referencing parent-child relationship, as follows:

----------------------------------------
| Table: Menu                          |
----------------------------------------
|  id   |  parent |    name  | ordinal |
----------------------------------------
|   1   |   null  |  search  |    1    |
---------------------------------------
|   2   |   null  | location |    2    |
----------------------------------------
|   3   |    1    | artifact |    1    |
----------------------------------------
|   4   |    2    |   city   |    1    |
----------------------------------------
|   5   |    2    |  county  |    2    |
----------------------------------------

I want to list items as parents first (according to their ordinals), then remaining children according to their ordinals. The dept of hierarchy is 1. But when I run following query, it lists only items that have parents (i.e. children only); excluding parents with null parents (i.e. root)

SELECT m FROM Menu m ORDER BY m.parent.ordinal, m.ordinal --> excludes root items

Is there a way to write something like this:

SELECT m FROM Menu m ORDER BY IF_EXISTS(m.parent.ordinal), m.ordinal --> should include root items too

Thank you.

score 0 · Answer 1 · edited May 23 '17 at 11:47

You can try using COALESCE() here, and assigning a value of zero to parent records. This would place them before any children records.

SELECT m
FROM Menu m
ORDER BY
    COALESCE(m.parent.ordinal, 0),
    m.ordinal

Here is what your table would look like using this logic:

---------------------------------------------------------
| Table: Menu                                           |
---------------------------------------------------------
|  id   |  parent |    name  | ordinal | parent.ordinal |
---------------------------------------------------------
|   1   |   null  |  search  |    1    |       0        |
---------------------------------------------------------
|   2   |   null  | location |    2    |       0        |
---------------------------------------------------------
|   3   |    1    | artifact |    1    |       1        |
---------------------------------------------------------
|   4   |    2    |   city   |    1    |       2        |
---------------------------------------------------------
|   5   |    2    |  county  |    2    |       2        |
---------------------------------------------------------

I assume that you are using JPA 2.0 or later (q.v. this SO question).

JPA NamedQuery order by not selecting tuples if parent is null

1 Answers1