Can't use join/onKey() with FK keys when there are table aliases...

[Denis Miorandi]

Table aliases

ShipCompanies customerCompany = SHIP_COMPANIES.as("CUSTOMER_COMPANY");

ShipCompaniesseats customerCompanySeat = SHIP_COMPANIESSEATS.as("CUSTOMER_COMPANYSEAT");

ShipCompanies inChargeOfCompany = SHIP_COMPANIES.as("INCHARGEOFF_COMPANY");

ShipCompaniesseats inChargeOfCompanySeat = SHIP_COMPANIESSEATS.as("INCHARGEOFF_COMPANYSEAT");

JOIN on table aliases

.innerJoin(customerCompanySeat).onKey(Keys.FK_SHIP_ORDERS_SHIP_COMPANIESSEATS_CUSTOMER)

.innerJoin(customerCompany).onKey(Keys.FK_SHIP_COMPANIESSEATS_SHIP_COMPANIES)

.innerJoin(inChargeOfCompanySeat).onKey(Keys.FK_SHIP_ORDERS_SHIP_COMPANIESSEATS_INCHARGEOF)

.innerJoin(inChargeOfCompany).onKey(Keys.FK_SHIP_COMPANIESSEATS_SHIP_COMPANIES)

the 2nd bold part generate same sql code of 1st bold part on a side of the join.

Is it a know behavior? Does it means that i can't use FK Keys on join when table compare more than one time (via table aliases)?

I was save by warning....

21:00:34.081 [main] INFO  org.jooq.impl.Fields - Ambiguous match found for ID_Company. Both "CUSTOMER_COMPANYSEAT"."ID_Company" and "CUSTOMER_COMPANY"."ID_Company" match.

21:00:34.081 [main] INFO  org.jooq.impl.Fields - Ambiguous match found for ID_Company. Both "CUSTOMER_COMPANYSEAT"."ID_Company" and "INCHARGEOFF_COMPANYSEAT"."ID_Company" match.

Here there are right SQL

from [dbo].[Ship_Orders]

  join [dbo].[Ship_CompaniesSeats] [CUSTOMER_COMPANYSEAT]

  on [dbo].[Ship_Orders].[ID_CompanySeat_Customer] = [CUSTOMER_COMPANYSEAT].[ID_CompanySeat]

  join [dbo].[Ship_Companies] [CUSTOMER_COMPANY]

  on [CUSTOMER_COMPANYSEAT].[ID_Company] = [CUSTOMER_COMPANY].[ID_Company]

  join [dbo].[Ship_CompaniesSeats] [INCHARGEOFF_COMPANYSEAT]

  on [dbo].[Ship_Orders].[ID_CompanySeat_InChargeOf] = [INCHARGEOFF_COMPANYSEAT].[ID_CompanySeat]

  join [dbo].[Ship_Companies] [INCHARGEOFF_COMPANY]

  on [INCHARGEOFF_COMPANY].[ID_Company] = [INCHARGEOFF_COMPANYSEAT].[ID_Company]

and here wrong one

from [dbo].[Ship_Orders]

  join [dbo].[Ship_CompaniesSeats] [CUSTOMER_COMPANYSEAT]

  on [dbo].[Ship_Orders].[ID_CompanySeat_Customer] = [CUSTOMER_COMPANYSEAT].[ID_CompanySeat]

  join [dbo].[Ship_Companies] [CUSTOMER_COMPANY]

  on [CUSTOMER_COMPANYSEAT].[ID_Company] = [CUSTOMER_COMPANY].[ID_Company]

  join [dbo].[Ship_CompaniesSeats] [INCHARGEOFF_COMPANYSEAT]

  on [dbo].[Ship_Orders].[ID_CompanySeat_InChargeOf] = [INCHARGEOFF_COMPANYSEAT].[ID_CompanySeat]

  join [dbo].[Ship_Companies] [INCHARGEOFF_COMPANY]

  on [CUSTOMER_COMPANYSEAT].[ID_Company] = [INCHARGEOFF_COMPANY].[ID_Company] 

taken from first tables alias instead of second one

Denis

[Lukas Eder]

Hi Denis,

I'm sorry for the delay in this issue, and thank you very much for reporting this.

I believe this is because of:

- https://github.com/jOOQ/jOOQ/issues/5050

- http://stackoverflow.com/q/35091409/521799

Currently, the foreign key reference in generated code is hard-coded to the unaliased representation of a table. It probably makes sense to rewire the generated table name to the table alias in the presence of the same, but the side-effects of this change are not yet fully known.

For the time being, I'm afraid that the ON KEY synthetic join syntax is not save to be used in the context of self-joins.

Best Regards,

Lukas

Denis

--
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/d/optout.

[Lukas Eder]

Let me add the following. Indeed, if you use the same table multiple times, then a ON KEY clause is going to be ambiguous. While intuitively, you might think that the last matching table should be chosen by a ON KEY match, this choice would be surprising in other situations where the join tree is composed dynamically.

I keep wondering if ON KEY is a good idea at all. It does help in simple cases, but it keeps frustrating users as queries get more complex, simply because it only works for simple joins that do not contain the same tables multiple times...

[Denis Miorandi]

ok Lukas, so i will not use onKeys on that situations. Is just important to have clear what to do.

Tks.

Denis

2016-02-16 16:37 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/imfTQE1H3nA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Dott. Denis Miorandi
via dei Vignai 27
38060 - Nogaredo (TN)