Bidirectional left join to main entity in HQL (projection DTO)

This is my simplified entity model:

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, from A a1 join B b on a1.exid = b.exid left join A a2 on =

: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


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

{,,, 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, from X1 a1 join B b on a1.bId = left join X2 a2 on = b.exid

thank you for help.

b.exid is not foreign key, there is no exid in a. I cant do, 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 = and 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.