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 2016-02-07 21:08 GMT+01:00 Denis Miorandi <[email protected]>: > *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 > > > > -- > 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/d/optout. > -- 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/d/optout.
