Question:
I am using MySQL as a backend so how would:
-- MySQL, H2, and others
SELECT t.a, t.b
FROM (
SELECT 1 a, 2 b
) t
translate to jooq? I have tried multiple approaches yet I always end up
with org.jooq.exception.DataAccessException which complains about the
syntax of the aliased table. (I am using MySQL)
Also, you suggest to inline the in and out tables, however in the semi-join
condition:
.and(out.getField(A.VERB)
.eq(in.getField(A.VERB)))
.groupBy(in.getField(A.VERB)))
how would I express the condition that out.A.VERB equals in.A.VERB
Thanks!
On Thursday, September 5, 2013 5:32:02 AM UTC-7, Lukas Eder wrote:
>
> Hello Kyle,
>
> I'm sorry about the delay, and thanks for the detailed message.
>
>
> 2013/8/30 <[email protected] <javascript:>>
>
>> Here is my jOOQ query:
>>
>> Table<Record> out = A.join(B)
>> .on(A.ID.eq(B.OID))
>> .and(A.WORD.eq(B.NEW_WORD)).asTable("out");
>>
>> Table<Record> in = A.join(B)
>> .on(A.ID.eq(B.OID))
>> .and(A.WORD.eq(B.NEW_WORD)).asTable("in");
>>
>> Condition inCondition = in.getField(A.ADJECTIVE).eq(adj)
>> .and(in.getField(A.WORD).in(listofwords))
>> .and(in.getField(A.VERB).notIn(listofverbs));
>> .and(in.getField(B.ID).lessOrEqual(id));
>>
>> Table<Record> nested = factory.select(
>> out.getField(A.ID),
>> out.getField(A.WORD),
>> out.getField(A.VERB),
>> out.getField(B.ID),
>> out.getField(B.TIME))
>> .from(out)
>> .where(out.getField(B.ID)
>> .eq(factory.select(max(in.getField(B.ID)))
>> .from(in)
>> .where(inCondition)
>> .and(out.getField(A.VERB)
>> .eq(in.getField(A.VERB)))
>> .groupBy(in.getField(A.VERB)))
>> ).asTable("nested");
>>
>> Result<Record> records = factory.select(nested.getFields())
>> .from(nested)
>> .orderBy(nested.getField(B.ID).desc())
>>
>> I did the semi join referencing:
>> https://groups.google.com/forum/#!topic/jooq-user/S3uTIMGgQ0w
>>
>> I want the rows of the join of A and B corresponding to the max B.ID for
>> each A.VERB. I did the semi join referencing:
>> https://groups.google.com/forum/#!topic/jooq-user/S3uTIMGgQ0w
>> I believe the error comes from doing out.getField(A.ID) and in.getField(
>> B.ID) which both get aliased as nested.id
>>
>
> Yes, your analysis is correct. There is a lot of ambiguity in your SQL
> statement with respect to ID columns. As a matter of fact, I doubt that
> your statement would be non-ambiguous in SQL itself. "out", for instance
> contains two references called "out.ID", which create an ambiguity between
> them. This is not resolved when you rename the SELECT to "nested", which
> contains two references named "nested.ID".
>
> I'm not sure how I can alias one of the ID fields, eg. B.ID so that I can
>> reference them separately in the select for the nested table. A.ID and
>> B.ID do not have any relationships together
>>
>
> While I can see your intent with nesting joined tables, I think this will
> also be tricky to solve in SQL. When renaming
>
> (A join B on ...) AS out
>
> You should really be renaming the joined table using derived column lists
>
> (A join B on ...) AS out(A_ID, A_VERB, B_ID, B_XXX)
>
> jOOQ supports derived column lists and simulates them where they aren't
> natively supported in SQL. This is explained here:
> http://blog.jooq.org/2013/01/07/simulating-the-sql-standard/
>
> In the mean time, I can offer you these workarounds:
>
> - Create a database view to express the reusable relation A join B on ...
> This view will be available from jOOQ's code generator output
> - Inline "in" and "out" instead of creating nested joined tables. While
> being a bit more verbose at programming time, it is certainly more readable
> later on.
> - Rename ID columns in your SQL statement, to resolve ambiguities.
>
> I hope this helps. Please, let me know if / how you resolved this issue.
> I'm always curious about how people deal with complex SQL and jOOQ.
>
> Cheers
> Lukas
>
>
>>
>> On Friday, August 30, 2013 7:12:39 AM UTC-7, Lukas Eder wrote:
>>
>>> Hello,
>>>
>>> I'm not quite sure what you're trying to do. Nesting joins I suppose?
>>> Can you express the SQL statement that you're trying to create, and we'll
>>> see how it can be translated to the jOOQ API
>>>
>>> Cheers
>>> Lukas
>>>
>>>
>>> 2013/8/30 <[email protected]>
>>>
>>>> I have 2 Tables both the same column name, id. However, these column
>>>> names do not have any foreign key relationship.
>>>>
>>>> Table A:
>>>> id,
>>>> other,
>>>> stuff
>>>>
>>>> Table B:
>>>> id,
>>>> more,
>>>> things
>>>>
>>>> I use the join of both these tables and alias it so I can do a semi
>>>> join.
>>>> A.join(B).asTable("inner");
>>>> A.join(B).asTable("outer");
>>>>
>>>> I would like to use both id columns, however when I use inner.getField(
>>>> A.ID) and inner.getField(B.ID), they both go to same aliased field
>>>> inner.id
>>>>
>>>> How can I change the B.ID column of the joined table to have a
>>>> different alias?
>>>>
>>>> --
>>>> 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<https://groups.google.com/groups/opt_out>
>>>> .
>>>>
>>>
>>> --
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
--
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.
