Column reference is ambiguous error occurs when two tables have the same field

[rcxid]

Description

I encountered a Query Error: column reference "code" is ambiguous when using the pagination feature on a query that involves multiple LEFT JOINs and a LIKE filter on a column that exists in more than one of the joined tables.
It appears that the SQL generated by sea-orm does not correctly qualify the column names in the WHERE clause, resulting in an ambiguity error.

Steps to Reproduce

pub async fn find_pagination<Entity, Column, Model, C, F>(
    select: Select<Entity>,
    default_order_columns: Vec<OrderColumn<Column>>,
    page_index: u64,
    page_size: u64,
    sort: Vec<SortField>,
    db: &C,
    handle: F,
) -> anyhow::Result<PaginationResult<Model>>
where
    Entity: EntityTrait<Model = Model>,
    Model: FromQueryResult + Sized + Send + Sync + JsonSchema,
    Column: ColumnTrait,
    C: ConnectionTrait,
    F: AsyncFnOnce(Select<Entity>) -> anyhow::Result<Select<Entity>>,
{
    let select = if sort.is_empty() {
        add_default_order(select, default_order_columns)
    } else {
        select
    };
    let select = add_sorter::<Entity, Column>(select, sort)?;
    let select = handle(select).await?;
    let paginator = select.paginate(db, page_size);
    let items_pages_num = paginator.num_items_and_pages().await?;
    let (items, info) = paginator
        .fetch_page(page_index)
        .await
        .map(|p| (p, items_pages_num))?;
    Ok(PaginationResult::from(page_index, page_size, items, info))
}

pub async fn find_pagination(
    db: &DbConn,
    user_id: i64,
    params: PaginationParams<JobFilter>,
) -> anyhow::Result<PaginationResult<entity::job::Model>> {
    find_pagination(
        entity::job::Entity::find()
            .join(
                JoinType::LeftJoin,
                crate::entity::job::Relation::SqlJob.def(),
            )
            .join(
                JoinType::LeftJoin,
                crate::entity::job::Relation::DataSyncJob.def(),
            )
            .join(
                JoinType::LeftJoin,
                crate::entity::job::Relation::ShellJob.def(),
            ),
        vec![OrderColumn {
            column: entity::job::Column::CreatedAt,
            order_desc: true,
        }],
        params.page_index,
        params.page_size,
        params.sort_query.sort,
        db,
        async |mut selector| {
            selector = selector.filter(Expr::col(entity::job::Column::Current).eq(true));
            if let Some(id) = params.filter.id {
                let id: i64 = id.parse()?;
                selector = selector.filter(Expr::col(entity::job::Column::JobId).eq(id));
            }
            selector = selector.filter(
                Self::extra_params_condition(db, user_id, params.filter.perm_type).await?,
            );
            if let Some(kind) = params.filter.kind {
                selector =
                    selector.filter(Expr::col(entity::job::Column::Kind).eq(kind.as_ref()));
            }
            if let Some(name) = params.filter.name {
                selector = selector
                    .filter(Expr::col(entity::job::Column::Name).like(format!("%{}%", name)));
            }
            if let Some(code) = params.filter.code {
                selector = selector.filter(
                    Condition::any()
                        .add(
                            Expr::col(entity::sql_job::Column::Code)
                                .like(format!("%{}%", code)),
                        )
                        .add(
                            Expr::col(entity::data_sync_job::Column::SelectSql)
                                .like(format!("%{}%", code)),
                        )
                        .add(
                            Expr::col(entity::data_sync_job::Column::PreInsertSql)
                                .like(format!("%{}%", code)),
                        )
                        .add(
                            Expr::col(entity::shell_job::Column::Code)
                                .like(format!("%{}%", code)),
                        ),
                );
            }
            Ok(selector)
        },
    )
    .await
}

Both the "sql_job" and "shell_job" tables have a "code" field. The program will report an error when the query conditions include a code filter condition：
Query Error: error returned from database: column reference "code" is ambiguous at line 846

Expected Behavior

Return job paginations

Actual Behavior

Query Error: error returned from database: column reference "code" is ambiguous at line 846

Reproduces How Often

100%

Workarounds

None known.

Versions

rustc 1.97.0 (2d8144b78 2026-07-07)
sea-orm v2.0.0
PostgreSQL 18.4-alpine
The image packaged based on alpine3.24.1 runs on Debian13

│ │ ├── sea-orm v2.0.0
│ │ │ ├── sea-orm-macros v2.0.0 (proc-macro)
│ │ │ │ ├── sea-bae v0.2.1 (proc-macro)
│ │ │ ├── sea-query v1.0.1
│ │ │ │ ├── sea-query-derive v1.0.0 (proc-macro)
│ ├── sea-orm v2.0.0
│ │ ├── sea-orm-macros v2.0.0 (proc-macro) ()
│ │ ├── sea-query v1.0.1
│ │ │ ├── sea-query-derive v1.0.0 (proc-macro) ()
│ │ ├── sea-query-sqlx v0.9.1
│ │ │ ├── sea-query v1.0.1 ()
├── sea-orm v2.0.0 ()

[rcxid]

let sql = entity::job::Entity::find()
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::SqlJob.def(),
    )
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::DataSyncJob.def(),
    )
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::ShellJob.def(),
    )
    .filter(
        Condition::any()
            .add(Expr::col(entity::sql_job::Column::Code).like(format!("%{}%", code)))
            .add(
                Expr::col(entity::data_sync_job::Column::SelectSql)
                    .like(format!("%{}%", code)),
            )
            .add(
                Expr::col(entity::data_sync_job::Column::PreInsertSql)
                    .like(format!("%{}%", code)),
            )
            .add(Expr::col(entity::shell_job::Column::Code).like(format!("%{}%", code))),
    )
    .build(DbBackend::Postgres)
    .to_string();
println!("{}", sql);

Generated SQL: The filter condition field in the generated SQL does not have a table prefix added.

SELECT "job"."id", "job"."job_id", "job"."user_id", "job"."name", "job"."description", "job"."kind", "job"."language", "job"."schedule", "job"."timezone", "job"."parallelism", "job"."parallelism_pending_strategy", "job"."timeout", "job"."retry_count", "job"."enable", "job"."current", "job"."released", "job"."released_description", "job"."created_at", "job"."released_at" FROM "job" LEFT JOIN "sql_job" ON "job"."id" = "sql_job"."job_version_id" LEFT JOIN "data_sync_job" ON "job"."id" = "data_sync_job"."job_version_id" LEFT JOIN "shell_job" ON "job"."id" = "shell_job"."job_version_id" WHERE "code" LIKE '%1%' OR "select_sql" LIKE '%1%' OR "pre_insert_sql" LIKE '%1%' OR "code" LIKE '%1%'

[rcxid]

let sql = entity::job::Entity::find()
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::SqlJob.def(),
    )
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::DataSyncJob.def(),
    )
    .join(
        JoinType::LeftJoin,
        crate::entity::job::Relation::ShellJob.def(),
    )
    .filter(
        Condition::any()
            .add(
                Expr::col((entity::sql_job::Entity, entity::sql_job::Column::Code))
                    .like(format!("%{}%", code)),
            )
            .add(
                Expr::col((
                    entity::data_sync_job::Entity,
                    entity::data_sync_job::Column::SelectSql,
                ))
                .like(format!("%{}%", code)),
            )
            .add(
                Expr::col((
                    entity::data_sync_job::Entity,
                    entity::data_sync_job::Column::PreInsertSql,
                ))
                .like(format!("%{}%", code)),
            )
            .add(
                Expr::col((entity::shell_job::Entity, entity::shell_job::Column::Code))
                    .like(format!("%{}%", code)),
            ),
    )
    .build(DbBackend::Postgres)
    .to_string();
println!("{}", sql);

Using Expr::col((Entity, Column)) can generate correct SQL:

SELECT "job"."id", "job"."job_id", "job"."user_id", "job"."name", "job"."description", "job"."kind", "job"."language", "job"."schedule", "job"."timezone", "job"."parallelism", "job"."parallelism_pending_strategy", "job"."timeout", "job"."retry_count", "job"."enable", "job"."current", "job"."released", "job"."released_description", "job"."created_at", "job"."released_at" FROM "job" LEFT JOIN "sql_job" ON "job"."id" = "sql_job"."job_version_id" LEFT JOIN "data_sync_job" ON "job"."id" = "data_sync_job"."job_version_id" LEFT JOIN "shell_job" ON "job"."id" = "shell_job"."job_version_id" WHERE "sql_job"."code" LIKE '%1%' OR "data_sync_job"."select_sql" LIKE '%1%' OR "data_sync_job"."pre_insert_sql" LIKE '%1%' OR "shell_job"."code" LIKE '%1%'

[Huliiiiii]

You are using the general sea query API, so you need to manually specify the table name.
You can use ColumnTrait::like for this case.