Rewrite SQL query with JOINS in JPA

Question

I have this SQL query for MariaDB.

select @ref:=id as id, unique_id, reference_id
from mytable
join (select @ref:=id from mytable WHERE unique_id = 55544)tmp
where reference_id=@ref

https://www.db-fiddle.com/f/jKJodfVfvw65aMaVDyFySd/0

How this query can be implemented in HQL query? I would like to use it in JPA?

Answer 1

(Answer largely re-written after comments below)

JPA doesn't have built-in support for hierarchical queries. The main option is a native query.

E.g. with this entity class:

@Entity
public class MyTable {
    @Id
    @GeneratedValue
    private int id;

    private int uniqueId;

    @ManyToOne
    private MyTable reference;

    // ... getters and setters ...
}

The following is an example of a native hierachical SQL query (actually against MySQL, just in case):

    Query query = entityManager.createNativeQuery(
            "select @ref\\:=id as id, unique_id, reference_id\r\n" +
                    "from my_table\r\n" +
                    "join (select @ref\\:=?)tmp\r\n" +
                    "where reference_id=@ref",
            MyTable.class);
    query.setParameter(1, 1);
    query.getResultList();

This was chasing down a chain of references successfully.

(Other alternatives)

There probably aren't too many other options that can do this as a single query. If scalability is less of a concern, adding a back reference would be a simple way to navigate the model:

    @OneToMany(mappedBy = "reference")
    private Set<MyTable> backReferences;

Those would then be straightforward to recursively navigate. Clearly the relation defaults to lazy loading, so would add little overhead until used.

Answer 2

With @df778899's MyTable in spring-data it could look like:

@Repository
public interface MyRepository extends ...

   @Query("select @ref:=id as id, unique_id, reference_id "+
           "from mytable join (select @ref:=id from mytable WHERE unique_id = :pUid) tmp "+
           "where reference_id=@ref", //just copy paste the query, use :pUid instead of constant...
            nativeQuery = true) // and this!
   List<MyTable> myCustomHirachicalQuery(@Param("pUid") Integer uid/*String/Long/...*/);
...