I am fetching all entities A of type I, i fetch its choosen fields into projection DTO. Now i need new field in DTO. I need to additionaly fetch entities A of type II IF THEY EXISTS AND ARE RELATED TO entity B with EXID: 1. So: i need to fetch all entities A of type I and associated types B and their EXID, this has not changed. But if there exists entity A of type II, that is related to entity B of EXID x, then i shall fetch A II number into new DTO field. If such A of type II does not exists (in image: second B is related to entity A of type I but not to entity A of type II, then value of this field should be null, but row should be fetched. In general there are much more entities A of type I, and they all should be fetched, while entity A of type II related to B with EXID: x is optional. I dont know how to write that in HQL. I know how to write that in pure SQL, but afaik i cant map pure SQL to projection DTO and i cant use setTransformer (library version is too old). Please help. Here is SQL that is doing what i want
I’m not sure I fully understand your model, but it sounds like what you are looking for is a left join, just like in SQL. Something like this in HQL select a1.id, a2.id from A a1 join B b on a1.exid = b.exid left join A a2 on a2.id = b.id.
:a1.exid - No. a1 has no exid, A entity only has b_id, foreign key to B, and B entities has exid
So i am interested in entities a1 and a2 of different type (lets say type is x), that share B with same exid, but exid is not foreign key. A does not know exid, it only knows b_id
So i if i have entity a1 of type x1 that has b_id=1 and entity a2 of type x2, that has b_id=2, and B both entities B with id 1 and B with id 2 has same exid than it should fetch
{
a1.id, a2.id
}
But lets say we have another entity a3 of type x1 that has b_id =3. B with id 3 has some exid that no other a of type x2 share. Then i should return row for a3, and value of second column should be null
It really doesn’t matter much how your model or your data looks like. What you are looking for is what a left join is doing, so your existing SQL can be translated almost 1:1 to HQL. The query would look something like the following:
select a1.id, a2.id from X1 a1 join B b on a1.bId = b.id left join X2 a2 on a2.id = b.exid
b.exid is not foreign key, there is no exid in a. I cant do a.id=b.exid, because exid is not id of a
also small edit: query in opening post is wrong: it returns duplicates. However idea is same: return all A a1 of types x, and for them: if there exists A a2 of type y such that a1.b_id = b1.id and a2.b_id=b2.id and b2.exid=b1.exid then also return such a2. Also there may be many rows B with the same exid
Also A of type x is much more than A of type y. I need to select all A of type x, if for some row there is no a2 that satisfies above conditions then return null in this column
I think I gave you enough information so you can resolve this on your own now. If you need further help, you would have to share a proper entity model and at least the SQL that you would like to achieve.
