Description

When joining several foreign keys referencing the same key in the same table, the generated SQL query fails.

Steps to Reproduce

1. Set up a new cargo project

cargo new sea-orm-issue

2. Create a similar SQL model wherre one table has two foreign keys referencing the same table (here football example ⚽ ) :

CREATE TABLE CLUB(
  id INT PRIMARY KEY NOT NULL,
  name STRING
);

CREATE TABLE GAME (
 id INT PRIMARY KEY NOT NULL,
   home_team INT,
   away_team INT,
   FOREIGN KEY (home_team) REFERENCES CLUB(id),
   FOREIGN KEY (away_team) REFERENCES CLUB(id)
);

Schema visualized :

3. Generate the rust models with sea-orm-cli

sea-orm-cli generate entity --database-url sqlite://db.sqlite3 -o src/entities

4. And request for a join table :

Cargo.toml

[package]
name = "sea-orm-issue-several-join"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
sea-orm = { version ="0.6" , features = ["runtime-async-std-native-tls", "sqlx-sqlite"]}
tokio = { version = "1.17.0", features = ["full"] }
env_logger = "0.9.0"

src/main.rs

pub mod entities;
use entities::{game};
use sea_orm::{entity::*, error::*, query::*, Database, DbConn};

#[tokio::main]
async fn main() -> Result<(), DbErr> {
    env_logger::init();
    let db: DbConn = Database::connect("sqlite://db.sqlite3").await.unwrap();
    game::Entity::find()
        .join(JoinType::InnerJoin, game::Relation::Club1.def())
        .join(JoinType::InnerJoin, game::Relation::Club2.def())
        .all(&db)
        .await?;
    Ok(())
}

5. There an error is returned

cargo r
    Finished dev [unoptimized + debuginfo] target(s) in 0.09s
     Running `target/debug/sea-orm-issue-several-join`
Error: Query("error returned from database: ambiguous column name: CLUB.id")

With more logs (attached here : log.txt ) , we indeed see that the request fails because the request is ambiguous on the Inner JOIN :

sql: "SELECT \"GAME\".\"id\", \"GAME\".\"home_team\", \"GAME\".\"away_team\" FROM \"GAME\" INNER JOIN \"CLUB\" ON \"GAME\".\"home_team\" = \"CLUB\".\"id\" INNER JOIN \"CLUB\" ON \"GAME\".\"away_team\" = \"CLUB\".\"id\""

Expected Behavior

The Inner Join works correclty

Actual Behavior

Two inner joins fails

Reproduces How Often

Always reproducible (tested MySQL and SQLITE3)

Versions

sea-orm = { version ="0.6" , features = ["runtime-async-std-native-tls", "sqlx-sqlite"]}

Additional Information

Maybe it would be a nice addition to add a " join_as( ) " method in order to pass the alias as second argument, so that the generated SQL query doesn't fail.

Sorry if there are some grammatical mistakes in my text, I am not a native english speaker and I tried my best to sum up the matter.

I appreciate all the works you did on this project and enjoy using it for my owns 😄 👍

Don't hesitate to ask for further details if needed