cte sql to simple subset query for JPA

Question

How can we change below CTE query (recursive) to simple subset sql:

  WITH links (parent, child) AS
  ( SELECT parent, child
        FROM Heirarchy_Table
        WHERE parent = '111111'
   UNION ALL 
    SELECT ht.parent, ht.child
        FROM Heirarchy_Table ht
        INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

I need to use this query for JPA as JPA is not handling "WITH" clause. [Note: "links" cte is used recursively]. Many Thanks!!

https://stackoverflow.com/questions/52990198/using-common-table-expression-cte-in-jpa-criteria-api — Sean Lange, Feb 26 '19 at 16:00
Thanks Sean, but I understand that the JPA is not handling "WITH" clause. Also, in above query we are using recursive. So, SELECT * from (SELECT * ...) is not working for me. — Shyam, Feb 26 '19 at 16:05
You can't do recursion without doing recursion. So moving this to a subquery or whatever is not going to work. The best way to handle this would be to create a stored procedure and call that from JPA. — Sean Lange, Feb 26 '19 at 16:07
yes, makes sense. I was trying to avoid stored procedure. But, this looks like a nice work around. Many Thanks!! — Shyam, Feb 27 '19 at 12:44

David Browne - Microsoft · Accepted Answer · 2019-02-27T13:42:25.253

How can we change below CTE query (recursive) to simple subset sql:

Create a view.

create view links
as
WITH links (parent, child) AS
( SELECT parent, child
    FROM Heirarchy_Table
    WHERE parent = '111111'
UNION ALL 
SELECT ht.parent, ht.child
    FROM Heirarchy_Table ht
    INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

Then you can just query the view

select * from links

Or you can use an inline table-valued function, sometimes called a "parameratized view", like this:

create or alter function GetLinks(@parent int)
returns table
as 
return
WITH links (parent, child) AS
(SELECT parent, child
    FROM Heirarchy_Table
    WHERE parent = @parent
UNION ALL 
SELECT ht.parent, ht.child
    FROM Heirarchy_Table ht
    INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

which you can query like:

select * from GetLinks(1111)

Here, "WHERE parent = '111111'" is getting value in runtime and also this is inside CTE. So, i think, view would not be a good option. — Shyam, Feb 27 '19 at 12:46
Thanks David. This looks like a nice option. I was not aware of this functionality in MsSql. — Shyam, Mar 06 '19 at 10:18

score 0 · Answer 2 · answered Jul 25 '21 at 19:51

@Query(value = "WITH RECURSIVE domainCTE AS (" +
        "   SELECT *, " +
        "       id AS root " +
        "       FROM domain " +
        "       WHERE parent IS NULL " +
        "   UNION ALL " +
        "       SELECT domain.*, " +
        "           domainCTE.root " +
        "       FROM domain " +
        "       JOIN domainCTE ON domain.parent = domainCTE.id " +
        ") " +
        "SELECT DISTINCT d.id, d.name, d.parent FROM domain d " +
        "    JOIN domainCTE cte " +
        "    ON d.id = cte.root " +
        "    WHERE cte.id IN :domainIds",
        nativeQuery = true
)
List<Domain> findDomainRootsByDomainIds(List<Long> domainIds);

Welcome to SO. Please add relevant description with the code to better understand your answer. Code-only answers are not recommended by SO. — Muhammad Tariq, Jul 26 '21 at 01:39

cte sql to simple subset query for JPA

2 Answers2

Linked