On Tuesday, March 5, 2013 5:29:17 PM UTC-5, Lukas Eder wrote:
>
> Hi,
>
> 2013/3/5 Peter Cooner <[email protected] <javascript:>>
>
>> I think I may have confused the issue - I have multiple columns in the
>> subquery to match - so asField() use is out I'm afraid.
>>
>
> That changes everything :-)
>
>
>> 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)
>
> 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.
>
> 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.
>
> 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
>
> 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
>
> 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:
PSQLException: ERROR: more than one row returned by a subquery used as an
expression
You can use the "in" operator though which is what you expect.
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))));
The "= any()" operator is equivalent of the "in" operator in Postgres, so
its all good.
--
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.
