CODEWITHSUNDEEP
postgresqlhibernatejpaspring-data-jpahibernate-mapping
code-geek
code-geek

Reputation: 463

Hibernate - more than one row returned by a subquery used as an expression

I have a scenario to extract information from three(Table1, Table2, Table4) tables based on the common id of Table1

For extracting information from Table 4, I have to look another intermediate table(Table3) for the common Id between Table 3 and Table 4. The query result of Table 4 information would contain more than a single row as query response.

I have written the following HQL(Hibernate Query) for my requirement

select t1,t2,(select t4.column1 from Table4 t4 
join Table3 t3 on t4.column0=t3.column0 and t3.column1=t1 and t3.column2='desired_value') from Table1 t1 
join Table2 t2 on t2.column1=t1.column1 
where t1.column0=:id and t1.column3=:value

I have entity classes for all the four tables.

How can I select the multiple rows from the subquery

What is the euiquvalent of POSTGRESQL array_agg() / string_agg() method in Spring Data JPA?

Without the subquery, I am able to fetch Object[] response with my Entity class objects.

How can I add my subquery in order to retrieve all my desired data in a Single transaction without creating a separate query method in my Repository class?

What I tried so far:

select t1,t2,(select new List(t4.column1) from Table4 t4 
join Table3 t3 on t4.column0=t3.column0 and t3.column1=t1 and t3.column2='desired_value') from Table1 t1 
join Table2 t2 on t2.column1=t1.column1 
where t1.column0=:id and t1.column3=:value

Below is the output object that I am trying to achieve:

List<Object[]> as my query response 

         where each Object[] will be of size 3 with following

                 Object[0] -> Table1_Entity object
                 Object[1] -> Table2_Entity object
                 Object[2] -> will be a String[] containing a specific column values from Table4 Entity class

i.e response = [[obj1, obj2, ["value1, value2","value3"]], [obj3, obj4, ["value1, value2","value3"]], ......]

Upvotes: 0

Views: 1060

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16430

Try this:

select t1,t2, (select cast(function('string_agg', t4.column1, ', ') as string) from Table4 t4 
join Table3 t3 on t4.column0=t3.column0 and t3.column1=t1 and t3.column2='desired_value') from Table1 t1 
join Table2 t2 on t2.column1=t1.column1 
where t1.column0=:id and t1.column3=:value

Upvotes: 1

Related Questions