Try to convert an qouted query back to a more readable query, but I get now an error?

[Nico van de Kamp]

I hava converted with the JooQ converter from SQL to JooQ. After some struggling it is working. But I don't like it with, it hard and a lot to read like:

table(unquotedName("tablename")).`as`(unquotedName("zz")),

and

field(name("zai2", "a_id")).eq(field(name("zai", "a_id")))

So I have rewritten this like:

override fun inconsistentCheck(zaakId: ZaakId): Boolean = usingDSL { context ->

val ZaakMainAlias = ZAAK.`as`("ZAAK_MAIN")
val AgendaMainAlias = AGENDA.`as`("AGENDA_MAIN")
val AgendaItemMainAlias = AGENDA_ITEM.`as`("AGENDA_ITEM_MAIN")

context
.select(
`when`(count().gt(0), `val`(true)).otherwise(`val`(false))
)
.from(ZaakMainAlias
,AgendaMainAlias
,AgendaItemMainAlias
)
.where(ZaakMainAlias.ID.eq(zaakId.value)
.and(AgendaMainAlias.ZAAK_ID.eq(ZaakMainAlias.ID))
.and(AgendaMainAlias.ACTUEEL.eq(inline(true)))
.and(AgendaItemMainAlias.AGENDA_ID.eq(AgendaMainAlias.ID))

).fetchOne(0, Boolean.class) == true
}

But query itself has not been changed, just removing the quoted text.

Now I get an error "None of the following functions can be called with the arguments supplied." (see screenshot.) on

).fetchOne(0, Boolean.class) == true

Why? Ok, the query is transformed from qouted to non-qouted, but still the same further!??

What kind of mistake do I maken or do I not understand?

If I look to the screenshot, There is now not a Boolean expected?

[Lukas Eder]

We have a flag governing the identifier style, if this is what's troubling you in terms of "hard to read":

https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style

Boolean.class isn't valid kotlin. Did you intend to write Boolean::class? But with the jOOQ API, you'll have to pass Boolean::class.java (in order to pass a java.lang.Class<?> reference)

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/74a1c2bf-f53c-4bb7-a624-fcff90103f07n%40googlegroups.com.

[Nico van de Kamp]

The flag "...flag governing the identifier style...", if understand it, this is about rendering query in the console log. How is shown in the console log of IntelliJ, right?  In the settings.java file I found that it is set as:

protected RenderQuotedNames renderQuotedNames = RenderQuotedNames.EXPLICIT_DEFAULT_QUOTED;

But what I mean is at the time of coding. The JooQ converter is so far as I know qoutes everthing. And I have that qouted query working.

Now I'm trying to transform the same query and remove the qoutes, so that it is easier to read and to follow. When I write this, I'm suddenly thinking does the compiler use the 'RenderQoutedNames' flag?

I believe when I'm writing this, I slowly understand what's happening. 

My function will return an Kotlin "Boolean". My query result is true or false and I expect simply that this result is returned by the function. But that is an misunderstood I suppose. The result of the query is a JooQ boolean... or at least not a Kotlin Boolean? Or has this nothing todo with ift? I have tried this:

.fetchOne()?.let { true } ?: false                 // this doesn't work either

My expection is: the query result is true or false. That result needs to be returned by the function, to the caller function.

What for me hard to understand is, I have written the exact same query without qoutes, but now the query without the qoutes, I get an error on fetchOne?And it is the same fetchOne as the query with qoutes!

(BTW all the queries in this project are unqouted, I'm now a few months on this project, somebody else has started this project 5 years ago, but has left)

Op maandag 2 september 2024 om 08:04:34 UTC+2 schreef lukas...@gmail.com:

[Lukas Eder]

On Mon, Sep 2, 2024 at 2:34 PM Nico van de Kamp <nicova...@gmail.com> wrote:

The flag "...flag governing the identifier style...", if understand it, this is about rendering query in the console log. How is shown in the console log of IntelliJ, right?  In the settings.java file I found that it is set as:

protected RenderQuotedNames renderQuotedNames = RenderQuotedNames.EXPLICIT_DEFAULT_QUOTED;

It affects all SQL rendering, including that of the console log.

 

But what I mean is at the time of coding. The JooQ converter is so far as I know qoutes everthing. And I have that qouted query working.

Now I'm trying to transform the same query and remove the qoutes, so that it is easier to read and to follow. When I write this, I'm suddenly thinking does the compiler use the 'RenderQoutedNames' flag?

I don't really know what you mean by "compiler" using the flag, or "jOOQ converter" in general. jOOQ renders SQL, irrespective of how you're using it, or where you're sending / executing this SQL. This flag affects how jOOQ renders SQL. Anyway, I don't think that's the question here. I was just trying to help given that you thought things were "unreadable." Let's focus on the actual problem.

 

I believe when I'm writing this, I slowly understand what's happening. 

My function will return an Kotlin "Boolean". My query result is true or false and I expect simply that this result is returned by the function. But that is an misunderstood I suppose. The result of the query is a JooQ boolean... or at least not a Kotlin Boolean? Or has this nothing todo with ift? I have tried this:

.fetchOne()?.let { true } ?: false                 // this doesn't work either

jOOQ is an API written in Java. When you're using it from Kotlin, you will occasionally have to know about how Kotlin interoperates with Java. The Kotlin "Boolean?" to "java.lang.Boolean" mapping is straightforward, in my opinion.

Now, fetchOne() returns an "org.jooq.Record?" As you can see e.g. the Javadoc:

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/ResultQuery.html#fetchOne()

Your IDE probably helps you with this as well. Do look at the type hints from your IDE, or assign expressions to local variables with explicit type declarations to better understand what's going on. With this in mind, you obviously can't just make this expression true or false like that. The expression is of type Record! (possibly containing 1 row and 1 column, in your case). I think this suffices to help you figure out what you're going to do?

 

My expection is: the query result is true or false. That result needs to be returned by the function, to the caller function.

What for me hard to understand is, I have written the exact same query without qoutes, but now the query without the qoutes, I get an error on fetchOne?And it is the same fetchOne as the query with qoutes!

I don't know what you mean by "with quotes and without quotes," the discussion starts being a bit confusing for me. But I already explained to you why you can't pass Boolean.class there (you probably took a Java example off the web). You have to pass the Kotlin equivalent Boolean::class.java expression instead.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f00e8df3-f88d-45e5-ade5-90d0f9be168bn%40googlegroups.com.

[Lukas Eder]

I guess the confusion here is this:

ResultQuery.fetchOne() returns a Record

ResultQuery.fetchOne(int) returns the value of type java.lang.Object at column index "int". This is just convenience API for ResultQuery.fetchOne()?.get(int)

ResultQuery.fetchOne(int, Class<U>) returns the value of type U at column index "int". This is just convenience API for ResultQuery.fetchOne()?.get(int, Class<U>)

jOOQ has a lot of convenience API like this. Does this help?

[Nico van de Kamp]

Maybe I make it to difficult and maybe I express myself not always good due that english is not my native language, sorry. One step back:

step 1. I have made an SQL query which is working.

step 2. next, I have put this query in the JOOQ converter, with this result. I call this "query with qoutes", table name, field name, aliases are between quotes etc. But is hard to read.

fun InconsistencyCheck(zaakId: ZaakId): Boolean = usingDSL { context ->
  context.select(
    `when`(count().gt(0), `val`(true) ).otherwise(`val`(false))              // result of the query true or false, and I'm expecting that this is
  )                                                                          // returned to the caller  function
  .from(table(unquotedName("zaak")).`as`(unquotedName("zz")),
        table(unquotedName("agenda")).`as`(unquotedName("za")),
        table(unquotedName("agenda_item")).`as`(unquotedName("zai"))
  )
  .where(field(name("zz", "id")).eq(zaakId.value)
    .and(field(name("za", "zaak_id")).eq(field(name("zz", "id"))))
    .and(field(name("za", "actueel")).eq(inline(true)))
    .and(field(name("zai", "agenda_id")).eq(field(name("za", "id"))))

  ).fetchOne(0, Boolean.class) == true                                          // Although this is Java, it is working and '== true' was suggested by IntelliJ
}

step 3: I try to transform the query of step 2 into a "query without quotes". For me it is the same query technically, it turns into an error on fetchOne.

override fun InconsistencyCheck(zaakId: ZaakId): Boolean = usingDSL { context ->

  val ZaakMainAlias = ZAAK.`as`("ZAAK_MAIN")
  val AgendaMainAlias = AGENDA.`as`("AGENDA_MAIN")
  val AgendaItemMainAlias = AGENDA_ITEM.`as`("AGENDA_ITEM_MAIN")

  context.select(
     `when`(count().gt(0), `val`(true)).otherwise(`val`(false))
  )
  .from(ZaakMainAlias
       ,AgendaMainAlias
       ,AgendaItemMainAlias
  )
  .where(ZaakMainAlias.ID.eq(zaakId.value)
    .and(AgendaMainAlias.ZAAK_ID.eq(ZaakMainAlias.ID))
    .and(AgendaMainAlias.ACTUEEL.eq(inline(true)))
    .and(AgendaItemMainAlias.AGENDA_ID.eq(AgendaMainAlias.ID))

  ).fetchOne(0, Boolean.class) == true                                                    // this gives the error.
}

Why is the query of step 2 working and step 3 not, with fetchOne?
And understand now, fetchOne() is returning a Record instead of just returning the Boolean true or false.But the query of step 2 is working!

And I'm asking myself, how do others this, just returning true or false. Thats not a weird idea, is it?

I have also tried the Kotlin Boolean:

.fetchOne(0, Boolean::class.javaPrimitiveType) == true

or

.fetchOne(0, Boolean::class.javaObjectType) == true

This is not given an compiler error, but when run this, it is not working in the way it does in step 2. And it gives an error at run-time.

This my last attempt, otherwise I will leave the query as in step2, which is working, but I would like to understand why the query of step 3 is not working?

Op maandag 2 september 2024 om 15:07:30 UTC+2 schreef lukas...@gmail.com:

[Lukas Eder]

Hi Nico,

I'm afraid I don't know what could be the problem. I don't believe you can write "Boolean.class" in Kotlin. Try this, e.g. here https://play.kotlinlang.org:

fun main() {
    println(Boolean.class)
}

You'll get:

The expression cannot be a selector (cannot occur after a dot).

Syntax error: Name expected.

So I don't know what we're really talking about here. The correct approach IMO is to use Boolean::class.java. I don't know what possible runtime exception you could be getting from this. You haven't shared it.

I don't believe you need to compare a boolean to true, that seems redundant (just like the when(x, true).otherwise(false) mapping, which is just x. In fact, I haven't thought about your query much, but you could also just use EXISTS() instead of the COUNT(*) expression, see here:

https://www.jooq.org/doc/latest/manual/reference/dont-do-this/dont-do-this-count-instead-of-exists/

If you use the approach from the manual there, that might work around your boolean/kotlin hassles.

I hope this helps

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b121c580-5a9a-4d82-ac73-39bde29f78e6n%40googlegroups.com.

[Nico van de Kamp]

Hi Lukas,

Thanks for you're help. The most strange thing for me is that the query (with the quotes) in step 2 works and the query in step 3 (query without quotes) gives an error while it is the technically the same query.

But ok, I have a query which works, and maybe I will find out later what the issue is, when I have more Kotlin\JooQ knowledge!

Again thanks!!!!

Op dinsdag 3 september 2024 om 08:15:22 UTC+2 schreef lukas...@gmail.com:

[Nico van de Kamp]

Hi Lukas,

I've found the issue:

.fetchOne(0, Boolean::class.java) ?: false             // But is strange for me that in one situation works == true and in the other situation as here need to be ?:false, while it is still Kotlin???

and now it is working. What you mentioned is right it is due: Kotlin.

(I've not been all the time busy with it, but I saw different code snippets and suddenly I thought is this not the issue!?)

Only what keeps me thinking is why do I have to declare val with the alias to use that in query:

val ZaakMainAlias = ZAAK.`as`("ZAAK_MAIN")   // and to use that in the query:

...

.from(ZaakMainAlias)

Why not like SQL feels more natural:

.from(zknZaakMainAlias.`as`("zaak_Main")

But ok, maybe in near future I will find it why it needs to be done width 'val'

Op dinsdag 3 september 2024 om 08:15:22 UTC+2 schreef lukas...@gmail.com:

Hi Nico,

[Lukas Eder]

On Wed, Sep 11, 2024 at 2:37 PM Nico van de Kamp <nicova...@gmail.com> wrote:

Only what keeps me thinking is why do I have to declare val with the alias to use that in query:

val ZaakMainAlias = ZAAK.`as`("ZAAK_MAIN")   // and to use that in the query:

...

.from(ZaakMainAlias)

Why not like SQL feels more natural:

.from(zknZaakMainAlias.`as`("zaak_Main")

But ok, maybe in near future I will find it why it needs to be done width 'val'

Of course you can inline the aliasing if you want. But then, how do you dereference a column from the aliased table?