CODEWITHSUNDEEP
hibernateconstructorhql
Tomer
Tomer

Reputation: 41

SELECT NEW() with many-to-one collection inside the constructor - HQL

I don't seem to find an answer to something that I refuse to accept as "Not Possible" :)

Here is my HQL query:

SELECT new TestTable(t.id,t.param1,t.param2,t.param3,stps) 
FROM TestTable t left join t.steps as stps 
WHERE t.someObj.id IN (:someObjIds)

TestTable has the following constructor:

public TestTable(Integer param1, Integer param2, Date param3, Date param4, Set steps)

I have tried to use Collection in constructor instead of a Set but it didn't work, the constructor will receive only the first item out of the collection as a parameter and not the entire collection as I expected.

In the query I also tried to use left join fetch t.steps, tried without left join at all, tried to surround the "stps" parameter in the constructor with "elements" like this: elements(stps)

But nothing worked. The reason I'm doing this is because TestTable is very big and has a lot of columns and associations but in this case I want only 4 columns and one collection. When one query can return up to 400,000 objects this becomes necessary.

Any ideas anyone?

Upvotes: 4

Views: 12130

Answers (2)

Sanjay Kumar
Sanjay Kumar

Reputation: 1474

You can't use a collection-valued path-expression in a constructor expression. Follow answer

Upvotes: 4

zinan.yumak
zinan.yumak

Reputation: 1590

Your query may work without 'new' constructor but it does not give you what you expect.

This query returns t.id,t.param1,t.param2,t.param3, [one entity from steps table] on each line and duplicates your results due to left join to steps table.

My recommendation is, firstly create a query that gets data from TestTable. Something like 

List<TestTable> resultList = "select new TestTable(t.id,t.param1,t.param2,t.param3) from TestTable where
                      t.someObj.id in (:someObjId)"

Then, create queries that gets data from step table for each TestTable.

for (TestTable tt : resultList) {
    List<Steps> stepList = "select st from Steps st where st.testTable.id = :ttId";
    tt.setSteps(stepList);
}

Upvotes: 3

Related Questions