query_as! macro error: error returned from database: column reference "x" is ambiguous

[maromcik]

I have three tables that all contain a field called name. When I want to join them, I get an error returned from database, saying that the name field is ambiguous, even though I gave aliases to the columns in question. Is there anyway I can make this work with the query_as macro, or do I have to resort to manual parsing of rows?

Here's the query

        let audiobooks = sqlx::query_as!(
            AudiobookDetail,
            r#"
            SELECT
                a.id,
                a.name AS "name_book",
                a.description,
                a.length,
                a.file_path,
                a.thumbnail,
                a.overall_rating,
                a.stream_count,
                a.like_count,
                a.created_at,
                a.edited_at,

                a.author_id,
                u.name AS "name_user",
                u.surname,
                u.username,
                u.email,
                u.profile_picture,
                u.bio,

                a.genre_id,
                g.name AS "name_genre"

            FROM
                "User" AS u
                    INNER JOIN
                "Audiobook" AS a
                    ON a.author_id = u.id
                    INNER JOIN
                "Genre" AS g
                    ON a.genre_id = g.id
            WHERE
                a.deleted_at IS NULL
                AND u.deleted_at IS NULL
                AND g.deleted_at IS NULL
                AND (name = $1 OR $1 IS NULL)
                AND (author_id = $2 OR $2 IS NULL)
                AND (genre_id = $3 OR $3 IS NULL)
                AND (like_count >= $4 OR $4 IS NULL)
                AND (like_count <= $5 OR $5 IS NULL)
                AND (length >= $6 OR $6 IS NULL)
                AND (length <= $7 OR $7 IS NULL)
                AND (stream_count >= $8 OR $8 IS NULL)
                AND (stream_count <= $9 OR $9 IS NULL)
                AND (overall_rating >= $10 OR $10 IS NULL)
                AND (overall_rating <= $11 OR $11 IS NULL)
            ORDER BY
                a.created_at
                    DESC;
            "#,
            params.name,
            params.author_id,
            params.genre_id,
            params.min_like_count,
            params.max_like_count,
            params.min_length,
            params.max_length,
            params.min_stream_count,
            params.max_stream_count,
            params.min_overall_rating,
            params.max_overall_rating,
        )
            .fetch_all(&self.pool_handler.pool)
            .await?;

Here is the struct in question:

#[derive(sqlx::FromRow, Debug, Clone, PartialEq, Eq)]
pub struct AudiobookDetail {
    pub id: Id,
    // --------------
    pub name_book: String,
    pub author_id: Id,
    pub genre_id: Id,
    pub length: PgInterval,
    pub file_path: String,
    pub stream_count: i64,
    pub like_count: i64,
    pub overall_rating: i16,
    pub thumbnail: String,
    pub description: String,
    pub created_at: DateTime<Utc>,
    pub edited_at: DateTime<Utc>,

    pub username: String,
    pub email: String,
    pub name_user: String,
    pub surname: String,
    pub bio: String,
    pub profile_picture: String,

    pub name_genre: String
}

And here are the tables:

CREATE TABLE IF NOT EXISTS "User"
(
    id              bigserial PRIMARY KEY,
    ---------------------------------------------
    username        text UNIQUE NOT NULL,
    email           text UNIQUE NOT NULL,
    name            text        NOT NULL,
    surname         text        NOT NULL,
    bio             text        NOT NULL,
    profile_picture text        NOT NULL,
    password_hash   text        NOT NULL,
    password_salt   text        NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT now(),
    edited_at       timestamptz NOT NULL DEFAULT now(),
    deleted_at      timestamptz
);


CREATE TABLE IF NOT EXISTS "Genre"
(
    id           bigserial PRIMARY KEY,
    ---------------------------------------------
    name         text        NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    edited_at    timestamptz NOT NULL DEFAULT now(),
    deleted_at   timestamptz
);



CREATE TABLE IF NOT EXISTS "Audiobook"
(
    id         bigserial PRIMARY KEY,
    ---------------------------------------------
    genre_id            bigserial       NOT NULL,
    author_id           bigserial       NOT NULL,
    name                text            NOT NULL,
    length              interval        NOT NULL DEFAULT make_interval(0,0,0,0,0,0,0),
    file_path           text            NOT NULL,
    thumbnail           text            NOT NULL,
    description         text            NOT NULL,
    stream_count        bigint          NOT NULL DEFAULT 0,
    like_count          bigint          NOT NULL DEFAULT 0,
    overall_rating      smallint        NOT NULL DEFAULT 0,
    created_at   timestamptz NOT NULL DEFAULT now(),
    edited_at    timestamptz NOT NULL DEFAULT now(),
    deleted_at   timestamptz,

    FOREIGN KEY (genre_id)          REFERENCES "Genre" (id),
    FOREIGN KEY (author_id)      REFERENCES "User" (id)
);

[abonander]

It's likely this predicate that's causing the error: AND (name = $1 OR $1 IS NULL)

[maromcik]

Embarrassing, that was it. Thank you very much. I was banging my head against the wall for a couple of hours and I didn't notice that.