Ambiguous column names on left join with sql_query results in mapping wrong columns to struct

Question

I'm working on a store management API with rust, actix-web and diesel with postgres and i have an issue with the mapping of the result of my sql_query.

I have the following structs representing my tables :

#[derive(Identifiable, Queryable, Serialize, Deserialize, Debug, Clone, QueryableByName)]
#[diesel(table_name = stores)]
pub struct Store {
    pub id: i32,
    pub name: String,
    pub is_holiday: bool,
    pub created_at: NaiveDateTime,
}

#[derive(Identifiable, Queryable, Validate, Associations, Serialize, Deserialize, Debug, Clone, QueryableByName)]
#[diesel(table_name = products, belongs_to(Store))]
pub struct Product {
    pub id: i32,
    pub name: String,
    pub i18n_name: Option<String>,
    pub price: BigDecimal,
    pub description: Option<String>,
    pub i18n_description: Option<String>,
    pub created_at: NaiveDateTime,
    pub store_id: Option<i32>,
}


diesel::table! {
    products (id) {
        id -> Int4,
        name -> Varchar,
        i18n_name -> Nullable<Varchar>,
        price -> Numeric,
        description -> Nullable<Text>,
        i18n_description -> Nullable<Text>,
        created_at -> Timestamp,
        store_id -> Nullable<Int4>,
    }
}

diesel::table! {
    stores (id) {
        id -> Int4,
        name -> Varchar,
        is_holiday -> Bool,
        created_at -> Timestamp,
    }
}

and the following function (i'll just put the relevant code snippet because the function is rather long) :

fn get_many() {
...
        let mut db_query_one = String::from("SELECT distinct p.id, p.name, p.i18n_name, p.description, p.i18n_description, p.price, p.store_id, p.created_at, s.id, s.created_at, s.is_holiday, s.name from products p left join stores s on s.id = p.store_id");
        let db_query_two = format!(" left join products_categories pc on pc.product_id = p.id WHERE p.name ILIKE $1 OR p.description ILIKE  $2 ORDER BY p.{} LIMIT $3 OFFSET $4", order.stringify());
        db_query_one.push_str(&db_query_two);
        let res = sql_query(db_query_one)
            .bind::<Text,_>(search.get_name())
            .bind::<Text,_>(search.get_description())
            .bind::<Integer,_>(pagination.get_per_page())
            .bind::<Integer,_>((pagination.get_page() - 1) * pagination.get_per_page())
            .load::<(Product, Option<Store>)>(&mut conn);
...
}

I expect this function to return a vector of tuples of products and the store each product is attached to if any. I almost get that, except that when a product is attached to a store, the store id, name and created_at fields are gonna be mapped to the products table respective id, name and created_at columns. how do i fix that and make the stores table columns map to the Store struct?

I'm sorry if i missed anything important i'm relatively new to rust.

Answer 1

For diesel::sql_query diesel loads fields by column name. This implies that the column name must be unique. You can assign such a unique column name in your query by using expr as your_name. You need to apply these column name in your type via the #[diesel(column_name = "your_name")] attribute on the corresponding field as well.