Preventing Duplicate Table Names in SQL Queries Using SQLAlchemy

[neatek]

Thank you for the recent update! We appreciate the enhancements and improvements made. It's not critical, but I think it's worth discussing.

Describe the bug or question The problem lies in encountering an error due to duplicate table names when writing SQL queries using SQLAlchemy. This occurs when there's ambiguity in the query because the same table name is used multiple times without explicitly specifying aliases. As a result, SQLAlchemy fails to process the query correctly, leading to a query execution error.

To Reproduce

booking_join_config = [
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.owner_id == models.UserModel.id,
        join_prefix="owner_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.user_id == models.UserModel.id,
        join_prefix="user_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
]

Description Output: table name "users" specified more than once

FROM bookings 
JOIN users ON bookings.owner_id = users.id 
JOIN users ON bookings.user_id = users.id 

Additional context

FROM bookings 
JOIN users AS owner_users ON bookings.owner_id = owner_users.id 
JOIN users AS user_users ON bookings.user_id = user_users.id 

[igorbenav]

Thanks for the issue! I forgot to test for this indeed, will fix as soon as possible

[igorbenav]

Fix for this is basically ready, I'll just write the docs and upload later today

[igorbenav]

Hey, @neatek, check the docs for aliases here