search

ufoss-org / kotysa

The idiomatic way to write type-safe SQL in Kotlin
The Unlicense
117 stars 1 forks source link

Support all join types and "cascading" join on others tables #142

Closed guitcastro closed 7 months ago

guitcastro commented 1 year ago

Hello, I have the following query who is not working by two reason.

1) I don't think leftJoin is implemented, or I am missing something? 2) I want to join a table using a field from the previous join (AddressSchema.cityId ) it's seems that it only accept the root table schema for joins 

  client selectAndBuild {} from PersonSchema
          innerJoin AddressSchema on PersonSchema.mainAddressId eq AddressSchema.id
          leftJoin CitySchema  on AddressSchema.cityId eq CitySchema.id

is there any workaround for it?

pull-vert commented 1 year ago

Hi @guitcastro I changed the roadmap to include both of these new features in the next 3.1.0 , that have no workarount for now, they have to be implemented !

guitcastro commented 1 year ago

@pull-vert Glad to know! I am using subqueries for now and is working for my use case :)

pull-vert commented 1 year ago

Hi @guitcastro

This feature is now released in Kotysa 3.1.0, doc is here

guitcastro commented 7 months ago

@pull-vert Sorry to bother you again, but I came across another scenario witch seems like a regression for this issue:

While this MR enabled to JOIN tables based on the last join, now it's not possible to perform multiple joins from the same table:

  client selectAndBuild {} from PersonSchema
          innerJoin AddressSchema on PersonSchema.mainAddressId eq AddressSchema.id
          leftJoin CitySchema  on AddressSchema.cityId eq CitySchema.id
          leftJoin AnotherSchema  on PersonSchema.id eq AnotherSchema.id <- This line now expected CitySchema

This is kind a hard problem to solve from the API perspective, because we might want to allow any table for create join clauses, but this kind of break the type safety api.

Do you have any idea on how could this be solved?

pull-vert commented 7 months ago

Hi @guitcastro

Thanks for this update, I think you are right I have to allow any table, if we have a lot of joins with several of them referring to a given intermediate table, I cannot do otherwise.

I am reopening this issue and include it to the next release !

pull-vert commented 7 months ago

Hi (again :wink: ) b@joseluisgs This fix is included in the latest 3.2.2 version that was released yesterday