Inner Queries (Selects into selects)

About currently available functionalities
Post Reply
User avatar
Klaudiusz
Site Admin
Posts: 29
Joined: Tue Aug 07, 2018 9:55 pm
Contact:

Inner Queries (Selects into selects)

Post by Klaudiusz » Tue May 07, 2019 11:56 am

Making inner queries into Daobab is very easy.
Just put another select on the right side into where condition.
Only proper query will be accepted.

Ofcourse, you can put on the right site correct field values, or whole entities:

Inner select with field values:

Code: Select all

        Select.from(db, tabUser.colLastName())
            .where(tabUser.colID(), IN ,Select.from(db,tabUserAvatar.colUserId()))
            .result();
Inner select with whole entities:

Code: Select all

        Select.from(db, tabUser.colLastName())
            .where(tabUser.colID(), IN ,Select.from(db,tabUserAvatar))
            .result();
Don't be affraid, both queries will be optimised by Daobab and will have the same SQL content:

Code: Select all

select ihs1.LAST_NAME
 from USER1 ihs1 
 where ihs1.USER_ID in  (
select ihs2.USER_ID
 from USER1 ihs1, USER_AVATAR ihs2 )

Just remember, do not use result() method into inner queries:

Code: Select all

Select.from(db, tabUser.colLastName())
            .where(tabUser.colID(), IN ,Select.from(db,tabUserAvatar).result())
            .result();
Because in this scenario, inner query will be executed at the beggining and the result will be inserted into primary query:

Code: Select all

select ihs1.LAST_NAME
 from USER1 ihs1 
 where ihs1.USER_ID in ('1','2','5')
In that scenario, your database will be called twice and your query can exceed the length limit.

If your second query is executed into another target, differend from target of primary query (BufferedTarget for example), Daobab will also insert the result no matter if you called result() method or not.

Tags:
Post Reply