Hi,
2013/3/5 Peter Cooner <[email protected]>
> 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).
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
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'd prefer the anti-join as it is:
- Probably the fastest solution
- The most readable solution
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.
