JPA: select in polymorphic entities with JPQL, eclipselink and joined inheritance using multiple downcast

Question

I'm having an interesting exercise trying to select multiple derived entities using a single JPQL query while with eclipselink 2.7.6.

The polymorphism is implemented using joined inheritance. The entities diagram and the java classes are as follows:

+--------------+
|  MainEntity  |
+--------------+                        +--------------+
|              | --- myRef:OneToOne --- |  Referenced  |
+--------------+                        +--------------+
                                        |  r: string   |
                                        +--------------+
                                               ^
                                               |
                                   +-----------+-----------+
                                   |                       |
                            +--------------+        +--------------+
                            |  Derived1    |        |  Derived2    |
                            +--------------+        +--------------+
                            |  d1: string  |        |  d2: string  |
                            +--------------+        +--------------+

@Entity                          
@Table(name="MAIN_ENTITY")
public class MainEntity
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "MAIN_ENTITY_ID")
    public Integer mainEntityId;

    @OneToOne(optional = true)
    @JoinColumn(name = "MY_REF", referencedColumnName = "REFERENCED_ID")
    public Referenced myRef;
}

@Entity
@Table(name="REFERENCED")
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="REFERENCED_TYPE",discriminatorType=DiscriminatorType.STRING)
public abstract class Referenced
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "REFERENCED_ID")
    public Integer referencedId;

    @Column(columnDefinition = "TEXT", name = "R")
    public String r;
}

@Entity
@Table(name="Derived1")
@DiscriminatorValue("DERIVED_1")
public class Derived1 extends Referenced
{
    @Column(columnDefinition = "TEXT", name = "D1")
    public String d1;
}

@Entity
@Table(name="Derived2")
@DiscriminatorValue("DERIVED_2")
public class Derived2 extends Referenced
{
    @Column(columnDefinition = "TEXT", name = "D2")
    public String d2;
}

My goal is to have a single query, which results in a table having the common columns (those of the Referenced entity) present on the left hand side, as well as the distinct columns of the derived entities presented on the right hand side in a single table.

If I initialized the data like this:

Derived1 d1 = new Derived1();
d1.r = "R set from Derived1";
d1.d1 = "D1 set from Derived1";
MainEntity me1 = new MainEntity();
me1.myRef = d1;

Derived2 d2 = new Derived2();
d2.r = "R set from Derived2";
d2.d2 = "D1 set from Derived2";
MainEntity me2 = new MainEntity();
me2.myRef = d2;

em.getTransaction().begin();
em.persist(d1);
em.persist(me1);
em.persist(d2);
em.persist(me2);
em.getTransaction().commit();

Using SQL i can retrieve the table I want using LEFT JOIN operators:

SELECT 
    m.MAIN_ENTITY_ID,
    r.REFERENCED_ID,
    r.R,
    d1.D1,
    d2.D2
FROM 
    REFERENCED r 
INNER JOIN
    MAIN_ENTITY m on m.MY_REF = r.REFERENCED_ID
LEFT JOIN 
    DERIVED1 d1 ON r.REFERENCED_ID = d1.REFERENCED_ID 
LEFT JOIN 
    DERIVED2 d2 ON r.REFERENCED_ID = d2.REFERENCED_ID

Results:

MAIN_ENTITY_ID REFERENCED_ID R                   D1                   D2                   
-------------- ------------- ------------------- -------------------- -------------------- 
2              1             R set from Derived1 D1 set from Derived1 [null]               
1              2             R set from Derived2 [null]               D1 set from Derived2

However, so far I have a tough time using JPQL doing the same thing. I tried using any combination of the TREAT and (LEFT) JOIN JPQL operators I have no luck whatsoever. Either the resulting SQL joins forces the IDs of d1 and d2 to be equal (resulting on no results naturally), or I'm getting too many results, all of them permutation of the target result I'm aiming for.

I could reproduce the SQL result using JPQL using a combination of the TREAT and UNION operators like this:

SELECT 
    m.mainEntityId,
    m.myRef.referencedId,
    m.myRef.r,
    TREAT(m.myRef AS Derived1).d1,
    null as d2
FROM 
    MainEntity m
UNION
SELECT 
    m.mainEntityId,
    m.myRef.referencedId,
    m.myRef.r,
    null as d1,
    TREAT(m.myRef AS Derived2).d2
FROM 
    MainEntity m

Results:

mainEntityId referencedId r                   d1                   d2                   
------------ ------------ ------------------- -------------------- ------------------
2            1            R set from Derived1 D1 set from Derived1 null               
1            2            R set from Derived2 null               D1 set from Derived2

However repeating the query multiple times with corresponding null selections seems to be non-efficient and error-prone because I'm forced to repeat the whole structure for each sub-type. Especially for more normalized data models this approach seems overly counter-intuitive.

Obviously I'm trying to impose the SQL paradigm on the JPQL one and while having a bit of a success, the overall message is that I'm doing something wrong. So my question is, is there a better way to achieve this using JPQL? If not, what are you people doing in such a case?

Thanks in advance!

score 1 · Accepted Answer · answered Apr 18 '20 at 09:02

What you are trying can be achieved by using the following JPQL query and JOIN-s:

    SELECT
        m.mainEntityId,
        r.referencedId,
        r.r,
        d1.d1,
        d2.d2
    FROM
        MainEntity m
        LEFT JOIN m.myRef r
        LEFT JOIN TREAT(m.myRef AS Derived1) d1
        LEFT JOIN TREAT(m.myRef AS Derived2) d2

The query returns the same two rows as in your SQL example. I had to follow these rules to get to the correct result via JPQL:

Do not use more than one indirection in the SELECT or FROM clause (things like SELECT m.myRef.r need to be broken down to a JOIN m.myRef r and a SELECT r.r).
Use attributes rather than entity names when joining tables. Correct: FROM MainEntity m LEFT JOIN m.myRef r, incorrect: FROM MainEntity m LEFT JOIN Reference r. Explanation: Specifying the exact attribute you are joining on is necessary so JPA knows what ON condition to generate. If you have more than 1 relation between MainEntity and Reference then JPA would not know which column exactly are you joining on unless you specify it.
Using LEFT JOIN works as expected. Using INNER JOIN however makes Eclipselink generate some weird stuff in the SQL (it appends a , Reference t2 to the JOIN clause and the result is incorrectly full of unexpected permutations). I cannot explain why this happens. INNER JOIN is semantically correct in my opinion and this looks to me like an Eclipselink bug. Maybe you could open a separate issue and ask about it.

This works, thank you. You are also right about the JOIN vs the LEFT JOIN. Semantically speaking, the inner join should be the one to use, however the result is completely the opposite. — el.nicko, Apr 18 '20 at 16:05

JPA: select in polymorphic entities with JPQL, eclipselink and joined inheritance using multiple downcast

1 Answers1