selectFrom() and select().from()

[gerald....@gmail.com]

Hi,

I can write

Result<BookRecord> bookRecords=jooq.selectFrom(BOOK)

   .where(BOOK.TITLE.eq("Les Misérables"))

   .fetch();

But I can't add a join, I can't write:

Result<BookRecord> bookRecords=jooq.selectFrom(BOOK)

    .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

    .where(AUTHOR.NAME.eq("Victor Hugo"))

    .fetch();

I had to write:

List<BookRecord> bookRecords=jooq.select(BOOK.fields()).from(BOOK)

    .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

    .where(AUTHOR.NAME.eq("Victor Hugo"))

    .fetchInto(BookRecord.class);

Is there an easier way to do the same? How can I tell JOOQ to return Result<BookRecord> instead of Result<Record>/List<BookRecord>?

Gérald

[Lukas Eder]

Hello,

2013/11/10 <gerald....@gmail.com>

Instead of 

    .fetchInto(BookRecord.class);

You could write

    .fetchInto(BOOK);

See: http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#fetchInto(org.jooq.Table)

You could also write a semi-join using an IN or EXISTS predicate, instead of joining.

[Gérald Quintana]

Hello,

Actually, the question was: why can't I use joins when using selectFrom statement?

At first, I thought that "selectFrom(SOURCE).fetchInto(TARGET)" meant "insert into TARGET (...) select .. from SOURCE".

Thanks for your help.

Gérald

[Lukas Eder]

Hi Gérald,

2013/11/16 Gérald Quintana <gerald....@gmail.com>

Hello,

Actually, the question was: why can't I use joins when using selectFrom statement?

 

Hmm, yes, I was going to explain that but somehow I forgot...

The idea is simple (although debatable). The idea behind selectFrom() is to provide jOOQ with a clearly defined record type, which may be a more specific type than for instance Record2<Integer, String>. Once you join other tables, that specific table type would get lost (i.e. denormalised). Since selectFrom() offers no way to redefine the projection (SELECT clause), the result would inevitably be the type-unsafe Record.

There's certainly room for API improvement in that area. I'm open to suggestions.

At first, I thought that "selectFrom(SOURCE).fetchInto(TARGET)" meant "insert into TARGET (...) select .. from SOURCE".

Aha. No that would be insertInto(TARGET).select(...)

Cheers

Lukas 

[Aurélien Manteaux]

Hi Lucas,

I don't understand when you say that once you join other table the specific table type would get lost.

For example :

- SELECT t.* FROM Table1 t => the result type is Table1

- SELECT t.* FROM Table1 t JOIN Table2 k ON t.id=k.tid => the result type is still Table1

Am I missing something ?

Cheers,

Aurélien

[Lukas Eder]

Hi Aurélien,

Aha, I can see the point of confusion. The problem here is that jOOQ currently doesn't really support selecting t.*. By writing select().from(t.join(u).on(...)), you're implicitly selecting t.*, u.*. Now even if jOOQ checked the explicit projection at runtime to see if t.* is being completely selected, this would not lead to generic typesafety in Java, thus you'd still get a Result<Record> or maybe, a Result<Record[N]<T1, T2, ..., T[N]>>

In other words, this is an API limitation / flaw of jOOQ. I currently don't know how this could be resolved. As always, I'm very open to suggestions.

Cheers

Lukas

2013/11/30 Aurélien Manteaux <aman...@gmail.com>

--

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/groups/opt_out.

[Gérald Quintana]

Hello,

The logical syntax may be useful here:

Result<BookRecord> bookRecords=jooq.from(BOOK)

    .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

    .where(AUTHOR.NAME.eq("Victor Hugo"))
    .select(BOOK) // Meaning select book.*

    .fetch();

Since select() is close to fetch(), it may be easier to keep type information.
Is there a technical reason (in API design or whatever) preventing logical syntax?

It's only an idea, probably a blind one.

Gérald

[Lukas Eder]

Hi Gérald,

I know that many other frameworks (e.g. LINQ, Slick, QueryDSL) try to solve this SQL "problem" by inverting the order of clauses to what should have been done in SQL from the beginning. Unfortunately, that particular solution is usually incorrect. I've described this here (item 2):

http://tech.pro/tutorial/1555/10-easy-steps-to-a-complete-understanding-of-sql

SELECT is actually not the last clause in a SQL statement, which is "logically" ordered as such (only most important clauses only):

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY
• OFFSET / FETCH

By artificially moving SELECT to the end, many other frameworks will diverge from actual SQL in edge-cases.

In any case, the inversion wouldn't change anything here. As I mentioned before, the only missing part is to be able to select BOOK.*. This feature is on the roadmap for jOOQ 3.3:

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

With that implemented, it might be possible to fetch typesafe BookRecords even if joins are present.

Hope this helps,

Lukas

2013/12/2 Gérald Quintana <gerald....@gmail.com>

[Gérald Quintana]

Thanks for this explanation,

I thought the problem was the selected record type was lost after joining other tables. When I say "lost", I mean from type safety (java generics) point of view.

I knew that select wasn't the last part in logical order, but most of remaining parts (distinct/order/offset) do not change the record type, the exception is probably union here. From my Java developer point of view, getting select (=projection) and fetch (=row mapping) close is interesting because both are related: when there is a lot of code between select an fetch, the code gets less readable. I'll try to stop arguing about this since I am not relevant ;-) .

Gérald

[Lukas Eder]

2013/12/5 Gérald Quintana <gerald....@gmail.com>

Thanks for this explanation,

I thought the problem was the selected record type was lost after joining other tables. When I say "lost", I mean from type safety (java generics) point of view.

Well, it isn't lost, but there is the table reference type (in the FROM clause) and the projection (in the SELECT) clause. The two are contradictory, and even a GROUP BY, or a PIVOT or some other vendor-specific clause can transform the row type in between.

If, however, a new select() overload can be added that takes an "asterisk type" (or whatever the name will be), then the BookRecord type information won't be lost. In fact, we could even imagine selecting BOOK.*, a, b, c, AUTHOR.* into something like <BookRecord, Integer, String, Integer, AuthorRecord>. I haven't gone through the API design exercise on that yet, though.

 

I knew that select wasn't the last part in logical order, but most of remaining parts (distinct/order/offset) do not change the record type, the exception is probably union here. From my Java developer point of view, getting select (=projection) and fetch (=row mapping) close is interesting because both are related: when there is a lot of code between select an fetch, the code gets less readable.

Yes, it certainly is more readable. I guess that the early SQL designers regret this language design decision. And some other things, too :-)

 

I'll try to stop arguing about this since I am not relevant ;-) .

Hehe, well, you can always try :-)

Cheers

Lukas