How to return 2 items from 2 different tables within 1 query in Spring JPA?

Question

I have a postgres query within my springboot app that needs to return a list with each element (being an array) containing 2 items from 2 different tables. The entities for each table are as follows:

table_one:

@Data
@Entity(name = "table_one")
@Table(name = "table_one")
public class TableOne {
    @Id
    @GeneratedValue
    private UUID id;
    private String commonField;
    private String type;
    private String specId;
}

table_two:

@Data
@Entity(name = "table_two")
@Table(name = "table_two")
public class TableTwo {
    @Id
    @GeneratedValue
    private UUID id;
    private String commonField;
    private String name;
}

Now, these tables are connected and the output of 1 would be required to get the 2nd item. The 1st item would be of type TableOne and the 2nd item would be of type List<TableTwo>. The 2nd item contains a list of TableTwo objects that relate to a TableOne objects that have the same type field.

My initial idea was to use spring projections (I used this guide to create one). However, it turns out that projections cannot seem to return collections.

Then I decided to create CTEs and use them instead. My query is as so:

@Query(value = "WITH subset_items AS (SELECT table_two.common_field FROM table_two WHERE table_two.name = ‘beans’), item_one AS (SELECT DISTINCT * FROM table_one WHERE table_one.common_field in subset_items)
SELECT *,* FROM item_one , subset_items WHERE subset_items.common_field in (SELECT table_one.common_field FROM table_one WHERE table_one.type = item_one.type)", nativeQuery = true)
List<Object[]> myMethod();

However, I hit a road block and while investigating, I found out that CTEs cannot be used as part of a WHERE clause (in my case my 2nd CTE uses the 1st one). From one I can tell, there is no tricks to make it work.

My next idea was to make use of CREATE FUNCTION with return type of table. I used official docs to make my query and end up with this:

    @Query(value=
        "CREATE FUNCTION subset_items() RETURNS TABLE(common_field text) AS " +
            "$$ SELECT table_two.common_field FROM table_two WHERE table_two.name = 'beans' $$ LANGUAGE sql; " +
        "CREATE FUNCTION ent_id() RETURNS TABLE(common_field text,spec_id text) AS " +
            "$$ SELECT common_field,spec_id FROM table_one WHERE table_one.common_field in subset_items$$ LANGUAGE sql; " +
        "SELECT *, * FROM item_one , subset_items WHERE subset_items.common_field in " +
            "(SELECT table_one.common_field FROM table_one WHERE table_one.type = item_one.type)", nativeQuery = true)
List<Object[]> myMethod();

However, I ran into an exception that I cannot solve when starting my app. It is as bellow:

Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

So my question is as follows: what causes this exception? Did I do something wrong when creating my functions?

Alternatively, I would be happy to try another approach. I also think that the query can be simplified where a few simple subqueries will do the trick but I can't seem to figure it out.

Answer 1

You can use it but it is still a normal table and must be treated as such:

WITH subset_items AS (
    SELECT table_two.common_field 
      FROM table_two 
     WHERE table_two.name = ‘beans’)
    , item_one AS (
        SELECT DISTINCT * 
          FROM table_one 
         WHERE table_one.common_field IN 
            (SELECT common_field 
               FROM subset_items))
    SELECT *, * 
      FROM item_one, subset_items 
     WHERE subset_items.common_field IN (
        SELECT table_one.common_field 
          FROM table_one 
         WHERE table_one.type = item_one.type)