Hello,
here is a better example:
>>>
>>> Table<ARecord> a1 = create.select(A.ID, max(A.SOME_DATE).as("max")).**
>>> from(A).groupBy(A.ID).asTable(**"a1");
>>> create.select(A.fields())
>>> .from(A, a1)
>>> .where(A.ID.eq(a1.field("id"))**.and(A.SOME_DATE.eq(a1.field("**
>>> max"))));
>>>
>>
>> I see what you mean. Well, if you insist on an actual self-join, then
>> yes, your query is about correct. When it comes to typesafety, you have two
>> options:
>> - Unsafely "Java-cast" A.ID or a1.field("id") to another type, such as
>> Field<Object> or the raw type Field: ((Field) a1.field("id"))
>> - Safely "SQL-cast" a1.field("id") to another type, e.g.
>> a1.field("id").cast(A.ID):
>>
>> The second option might be prefered here, using:
>> http://www.jooq.org/javadoc/**latest/org/jooq/Field.html#**
>> cast(org.jooq.Field)<http://www.jooq.org/javadoc/latest/org/jooq/Field.html#cast(org.jooq.Field)>
>>
>> If a cast is not necessary, jOOQ won't render it (e.g. casting
>> Field<Integer> to Integer.class).
>>
>
>
> Ah! .cast() makes a lot of sense to me now. I simply did not think of that
> way - being used to writing SQL directly.
>
Yes. Some functions are also available through a more object-oriented
"postfix" notation. I'm still undecided whether I should leave these in the
API, as they aren't "SQL-style". But A.TEXT.trim() might feal more natural
than trim(A.TEXT) to some users.
> Anyway, your query is probably better expressed using an anti-join, as in:
>>
>> A outer = A.as("outer");
>> A inner = A.as("inner");
>>
>> create.select()
>> .from(outer)
>> .whereNotExists(
>> selectOne()
>> .from(inner)
>> .where(inner.ID.eq(outer.ID))
>> .and(inner.SOME_DATE.gt(outer.**SOME_DATE))
>> );
>>
>> The cast didn't even make it into the generated SQL - it was only used to
>>> get jOOQ to understand the types of the fields.
>>>
>>
>> Yes, jOOQ doesn't render "unnecessary" casts
>>
>
> I think this makes sense now, I know there are already 3 examples in the
> subquery page, but maybe a few more wouldn't be a bad thing. I would not
> have thought to search for the term "anti-join" but there aren't a lot of
> good search results for "jooq anti-join" either.
>
Yes, no doubt about that
> Maybe a field feature could be to cast a type - something like T
>>> field(String name, Class<T> type) just to deal with the generics.
>>>
>>
>> Yes, Field.coerce(Class<?>) methods are on the roadmap for precisely this
>> reason:
>> https://github.com/jOOQ/jOOQ/**issues/1373<https://github.com/jOOQ/jOOQ/issues/1373>
>>
>> But your idea sounds reasonable to me. Instead of coercing a field, it
>> would also make sense to provide the <T> type when dereferencing the field
>> from the table. I have registered #2306 for this:
>> https://github.com/jOOQ/jOOQ/**issues/2306<https://github.com/jOOQ/jOOQ/issues/2306>
>>
>> Probably, there are 1-2 other places in the API, where "convenient
>> coercion" would make sense.
>>
>>
>>> Is there a better solution?
>>>
>>
>> Yes, the anti-join (as mentioned before), or optionally, a semi-join if
>> your database supports row value expressions and subselects (HSQLDB, MySQL,
>> Oracle, Postgres):
>>
>> create.select()
>> .from(A)
>> .where(row(A.ID, A.SOME_DATE).eq(
>> select(A.ID, max(A.SOME_DATE))
>> .from(A)
>> .groupBy(A.ID)
>> ));
>>
>>
> I like this last solution - though you can't use the "equals" operator
> here unless you also do selectOne(). Otherwise you'll get this exception at
> run-time:
>
That's because I forgot the semi-join condition on A.ID :-(. Here's the
correct query
A inner = A.as("inner");
A outer = A.as("outer");
create.select()
.from(outer)
.where(row(outer.ID <http://a.id/>, outer.SOME_DATE).eq(
select(inner.ID <http://a.id/>, max(inner.SOME_DATE))
.from(inner)
.groupBy(inner.ID <http://a.id/>)
.where(outer.ID.eq(inner.ID))
));
This would now correspond to your original intent. Of course, with such a
semi-join condition, there's no point in using row value expressions
anymore... So this might be a bit more readable
A inner = A.as("inner");
A outer = A.as("outer");
create.select()
.from(outer)
.where(outer.SOME_DATE.eq(
select(max(inner.SOME_DATE))
.from(inner)
.groupBy(inner.ID <http://a.id/>)
.where(outer.ID.eq(inner.ID))
));
You can use the "in" operator though which is what you expect.
>
Yes, you can. But unless Postgres has a good CBO, this seems quite
suboptimal. Without very sophisticated CBO's, the anti-join is probably
really the fastest solution.
On a side note, this works in jOOQ 3:
>
> create.selectFrom(A).where(A.ID.eq(any(select(A.ID).from(A))));
>
> Though this does not:
>
> create.selectFrom(A).where(row(A.ID, A.SOME_DATE).eq(any(select(A.ID,
> A.SOME_DATE).from(A))));
>
Yes, quantifiers aren't available yet for row value expressions:
https://github.com/jOOQ/jOOQ/issues/2054
Cheers
Lukas
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
