Rust+Diesel左连接SQL查询列名歧义致结构体映射错误

我正在使用Rust、actix-web、Diesel与PostgreSQL开发商店管理API，遇到了sql_query查询结果的映射问题。

定义的数据库表结构体及表定义：

#[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,
    }
}

在get_many函数中编写了自定义左连接SQL查询，期望返回Vec<(Product, Option<Store>)>类型结果，但实际映射时Store的id、name、created_at字段错误覆盖到Product结构体的对应字段：

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);
...
}

原因分析 #

Product和Store结构体存在同名字段（id、name、created_at），手写SQL查询时返回的字段名重复，Diesel无法区分这些字段属于哪个结构体，导致映射混乱。

方法1：手写SQL时给冲突字段加别名，通过临时结构体中转 #

1. 修改SQL查询，给Store的字段添加唯一别名

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 AS store_id, s.name AS store_name, s.created_at AS store_created_at, s.is_holiday AS store_is_holiday 
    from products p left join stores s on s.id = p.store_id"
);

2. 定义临时结构体接收查询结果
   用QueryableByName标注，给每个字段指定对应SQL列名：

use diesel::sql_types::{Bool, Int4, Numeric, Text, Timestamp};
use chrono::NaiveDateTime;
use bigdecimal::BigDecimal;

#[derive(QueryableByName, Debug)]
struct ProductWithStoreRow {
    // Product字段
    #[diesel(sql_type = Int4, column_name = "id")]
    product_id: i32,
    #[diesel(sql_type = Text, column_name = "name")]
    product_name: String,
    #[diesel(sql_type = Text, column_name = "i18n_name")]
    i18n_name: Option<String>,
    #[diesel(sql_type = Text, column_name = "description")]
    description: Option<String>,
    #[diesel(sql_type = Text, column_name = "i18n_description")]
    i18n_description: Option<String>,
    #[diesel(sql_type = Numeric, column_name = "price")]
    price: BigDecimal,
    #[diesel(sql_type = Int4, column_name = "store_id")]
    product_store_id: Option<i32>,
    #[diesel(sql_type = Timestamp, column_name = "created_at")]
    product_created_at: NaiveDateTime,

    // Store字段（带别名）
    #[diesel(sql_type = Int4, column_name = "store_id")]
    store_id: Option<i32>,
    #[diesel(sql_type = Text, column_name = "store_name")]
    store_name: Option<String>,
    #[diesel(sql_type = Timestamp, column_name = "store_created_at")]
    store_created_at: Option<NaiveDateTime>,
    #[diesel(sql_type = Bool, column_name = "store_is_holiday")]
    store_is_holiday: Option<bool>,
}

3. 转换临时结构体为目标类型

let rows = 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::<ProductWithStoreRow>(&mut conn)?;

let result: Vec<(Product, Option<Store>)> = rows.into_iter().map(|row| {
    let product = Product {
        id: row.product_id,
        name: row.product_name,
        i18n_name: row.i18n_name,
        price: row.price,
        description: row.description,
        i18n_description: row.i18n_description,
        created_at: row.product_created_at,
        store_id: row.product_store_id,
    };

    let store = row.store_id.map(|id| Store {
        id,
        name: row.store_name.unwrap(),
        is_holiday: row.store_is_holiday.unwrap(),
        created_at: row.store_created_at.unwrap(),
    });

    (product, store)
}).collect();

方法2：使用Diesel查询DSL构建查询（更推荐） #

避免手写SQL，让Diesel自动处理字段映射和冲突：

use diesel::prelude::*;

let query = products::table
    .left_join(stores::table.on(products::store_id.eq(stores::id)))
    .left_join(products_categories::table.on(products::id.eq(products_categories::product_id)))
    .distinct()
    .filter(
        products::name.ilike(search.get_name())
        .or(products::description.ilike(search.get_description()))
    )
    .order_by(match order.stringify().as_str() {
        "name" => products::name.asc(),
        "created_at" => products::created_at.asc(),
        _ => products::id.asc() // 默认排序
    })
    .limit(pagination.get_per_page())
    .offset((pagination.get_page() - 1) * pagination.get_per_page())
    .load::<(Product, Option<Store>)>(&mut conn)?;

这种方式不需要处理字段别名，Diesel会自动生成正确的SQL并完成映射，减少手动出错的概率。

内容的提问来源于stack exchange，提问作者tarek.seba