Announcement Announcement Module
Collapse
No announcement yet.
JPA 2 Criteria API Order by grouping function of OneToMany association Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JPA 2 Criteria API Order by grouping function of OneToMany association

    I have the following entity relationship:


    @Entity
    public class TableA{
    private int aid;

    private Collection<TableB> bs;

    ...
    }

    @Entity
    public class TableB{

    private int bid;

    private Date date1;

    private Date date2;

    private TableA a;

    ...
    }

    I want to list TableA's in the following order:

    - max(date1, date2) desc


    With native SQL, I could do the following:

    SELECT * FROM TableA A, (
    SELECT
    aid,
    MAX(COALESCE(date1, date2, TO_DATE('1901-01-01', 'yyyy-MM-dd'))) AS MAX_DATE
    FROM TableB
    GROUP BY aid //foreign key
    ORDER BY MAX_DATE DESC
    ) B
    WHERE A.aid = B.aid
    ORDER BY MAX_DATE DESC;

    How can I achieve this using JPA 2 Criteria API?

  • #2
    Please also comment/suggest if there's an alternative way of doing this using Criteria API.
    Last edited by [email protected]; Apr 25th, 2013, 05:20 PM.

    Comment

    Working...
    X