dealing with ambiguous column names

[mountainpath9]

With these two tables:

#[derive(Iden, Clone, Copy)]
pub enum TaskType {
    #[iden = "task_type"]
    Table,
    Id,
    TenantId,
    RunnerId,
    Name,
    Kind,
    Active,
    Paused,
}

#[derive(Iden, Clone, Copy)]
pub enum TaskRunner {
    #[iden = "task_runner"]
    Table,
    Id,
    TenantId,
    Name,
    HashedSecret,
    Active,
    DashboardUrl,
}

I'd like to be able to build and execute a query like this:

    type TT = schema::TaskType;
    type TR = schema::TaskRunner;

    let (sql, values) = Query::select()
        .column(TT::Id)
        .column(TT::Name)
        .column(TR::Id)
        .column(TR::Name)
        .column(TT::Kind)
        .column(TT::Paused)
        .column(TT::Active)
        .from(TT::Table)
        .inner_join(TR::Table, Expr::col(TR::Id).eq(Expr::col(TT::RunnerId)))
        .and_where(Expr::col(TT::TenantId).eq(tenant_id.to_db()))
        .build_sqlx(PostgresQueryBuilder);

on postgres, this fails on execution with the following error:

column reference "id" is ambiguous at character 108

I can fix this by using tuples for all the columns with ambiguous names:

    let (sql, values) = Query::select()
        .column((TT::Table, TT::Id))
        .column((TT::Table, TT::Name))
        .column((TR::Table, TR::Id))
        .column((TR::Table, TR::Name))
        .column(TT::Kind)
        .column(TT::Paused)
        .column((TT::Table, TT::Active))
        .from(TT::Table)
        .inner_join(
            TR::Table,
            Expr::col((TR::Table, TR::Id)).eq(Expr::col((TT::Table, TT::RunnerId))),
        )
        .and_where(Expr::col((TT::Table, TT::TenantId)).eq(tenant_id.to_db()))
        .build_sqlx(PostgresQueryBuilder);

but I had to discover all of these by debugging runtime failures. So I've got into the habit of using tuples everywhere to avoid column name ambiguities.

Is this what others do? It makes for very verbose query code.

[tyt2y3]

Well this is the use case to recommend SeaORM for:

1. you can use Entity and .column(task_runner::Column::Id) automatically includes the Table Name as a tuple in select statements
2. there are helpers like https://www.sea-ql.org/SeaORM/docs/next/advanced-query/custom-select/#handling-select-results
3. you don't have to repeat the join condition as well, you can join via pre-defined relations https://www.sea-ql.org/SeaORM/docs/next/relation/custom-join-condition/#relation

Hope this helps!

[mountainpath9]

Thanks for the reply.

I'm not using SeaORM. Whilst I haven't looked into it in detail, my project doesn't need an ORM. Is sea-query primarily intended as a support layer for SeaORM, rather than for wider use?

[mountainpath9]

It looks like this unresolved discussion from 2023 is dealing with the same issue:

#681

I've tried to create my own metadata pattern for the db schema in lieu of the enum based approach, but I've run into the same problem where a single type representing a column can't implement both:

• IntoIden , required for passing to methods like: UpdateStatement::value
• IntoColumnRef, required for passing to methods like: Expr::col

Hence I'm also interested in the answer to this question:

Can this general impl for InfoColumnRef be replaced with more specific ones?