what is the idiomatic way to translate "select exists( select 1 from contact where id=12 )" to jooq

[Marcel Huber]

referring to http://stackoverflow.com/a/16467634/426834 "select exists" returns "true" or "false" for the subquery. What is the idiomatic equivalent in jooq?

[Lukas Eder]

jOOQ has DSLContext.fetchExists(Select<?>) if you want to make that a standalone call:

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#fetchExists-org.jooq.Select-

This was added in jOOQ 3.5

If you want to embed your EXISTS predicate in a bigger SELECT, you can combine:

- http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#exists-org.jooq.Select-

- http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#field-org.jooq.Condition-

As in:

Field<Boolean> field = field(

    exists(

        selectOne().from(CONTACT).where(CONTACT.ID.eq(12))

    )

);

Hope this helps,

Lukas

2014-12-16 9:56 GMT+01:00 Marcel Huber <mhub...@googlemail.com>:

referring to http://stackoverflow.com/a/16467634/426834 "select exists" returns "true" or "false" for the subquery. What is the idiomatic equivalent in jooq?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcel Huber]

Hi Lukas,

this helps a lot. I was playing with "selectOne()" getting back a "Condition", but couldn't find a suitable "method" to get the desired result.

And I was pretty sure, that there is an idiomatic solution!  

Marcel

[Lukas Eder]

Yes, that one was introduced in jOOQ 3.3 after realising that the PostgreSQL (and the SQL standard) treat BOOLEAN types and predicates as exactly the same thing, which is extremely useful...

Which makes me wonder if we should let org.jooq.Condition extend org.jooq.Field<Boolean>, in fact! I'll think about that:

[Lukas Eder]

2014-12-16 10:45 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

I'll think about that:

https://github.com/jOOQ/jOOQ/issues/3867