之前封装了一个简单分页查询方法，使用起来不够灵活。经 @謎龗、@YaYa 两位指导，改成使用QueryBuilder实现更加灵活，现修改成第二版。再次感谢二位的指导！

封装

// src/utils/pagination.rs

use std::marker::PhantomData;

use serde::Serialize;
use sqlx::{FromRow, PgPool, Postgres, QueryBuilder};

use crate::utils::log;

use super::env;

#[derive(Serialize, Debug)]
pub struct PaginatedResult<T> {
    pub records: Vec<T>,
    pub limit: i64,
    pub page: i64,
    pub total: i64,
}

/// 通用分页请求器
pub struct PaginatedQueryBuilder<'a, T> {
    pool: &'a PgPool,
    select: &'a str,
    page: Option<i64>,
    limit: Option<i64>,
    sort_field: Option<&'a str>,
    _marker: PhantomData<T>, // 引入泛型类型 T
}

/// 请求器类型
pub enum BuilderType {
    RECORDS,
    TOTAL,
}

impl<'a, T> PaginatedQueryBuilder<'a, T>
where
    T: for<'r> FromRow<'r, sqlx::postgres::PgRow> + Unpin + Send + 'static + std::fmt::Debug,
{
    pub fn new(
        pool: &'a PgPool,
        select: &'a str,
        page: Option<i64>,
        limit: Option<i64>,
        sort_field: Option<&'a str>,
    ) -> Self {
        Self {
            pool,
            select,
            page,
            limit,
            sort_field,
            _marker: PhantomData, // 初始化 PhantomData
        }
    }

    pub async fn paginate(
        &self,
        apply_filter: impl Fn(&mut QueryBuilder<Postgres>, BuilderType),
    ) -> Result<PaginatedResult<T>, sqlx::Error> {
        // 这里只初始化头部FROM之前的，后部SQL自行拼接。
        let mut query_builder: QueryBuilder<Postgres> = QueryBuilder::new(r#"SELECT "#);
        // 需要一个select入参，硬编码可以直接push，不建议通过用户接口传入
        query_builder.push(self.select);
        query_builder.push(" FROM ");

        let mut query_builder_total: QueryBuilder<Postgres> =
            QueryBuilder::new(r#"SELECT COUNT(*) FROM "#);

        let page_size = self
            .limit
            .unwrap_or(env::APP_PAGE_LIMIT.parse::<i64>().unwrap_or(10));
        let offset = (self.page.unwrap_or(1) - 1) * page_size;

        // 默认排序
        let order_by = self.sort_field.unwrap_or("updated_at DESC");

        apply_filter(&mut query_builder, BuilderType::RECORDS);
        apply_filter(&mut query_builder_total, BuilderType::TOTAL);

        // 此处ORDER BY无法通过bind传参，需要业务确认order_by的安全性
        query_builder.push(format!(" ORDER BY {} ", order_by));
        query_builder.push(" DESC LIMIT ");
        query_builder.push_bind(page_size);
        query_builder.push(" OFFSET ");
        query_builder.push_bind(offset);

        // 日志，改成自己的日志或者删除
        log::info("SQL", query_builder.sql());
        log::info("SQL", query_builder_total.sql());

        let total = query_builder_total
            .build_query_scalar()
            .fetch_one(&*self.pool)
            .await?;

        let records: Vec<T> = query_builder
            .build_query_as()
            .fetch_all(&*self.pool)
            .await?;

        Ok(PaginatedResult {
            records,
            limit: page_size,
            page: self.page.unwrap_or(1),
            total,
        })
    }
}

使用

// src/modules/user/mod.rs

let query_builder = PaginatedQueryBuilder::<PageQsoRes>::new(
        &pool,
        "u.*, a.address as address", // 这块是select模块
        payload.page,
        payload.limit,
        None,
    );

    // 添加通用过滤条件
    let apply_filter = |qb: &mut QueryBuilder<Postgres>, builder_type: BuilderType| {
        // 查询users表
        qb.push(" users u ");

        // 如果是查询列表，则添加关联表
        if let BuilderType::RECORDS = builder_type {
            qb.push(" LEFT JOIN address a ON u.address_id = a.id ");
        }

        // 根据条件查询
        qb.push(" WHERE u.user_id =");
        qb.push_bind(user_id);

        // 可选参数查询，payload为request入参，user_name类型为Option
        if let Some(user_name) = &payload.user_name {
            if !callsign.is_empty() {
                qb.push(" AND .user_name = ");
                qb.push_bind(user_name);
            }
        }

        // 添加其他SQL语句
    };

    let res = query_builder.paginate(apply_filter).await?;
    println!("res: {:?}", res);

    //{
    //  "records": [
    //      {
    //         // ...
    //      }
    //  ],
    //  "limit": 10,
    //  "page": 1,
    //  "total": 1
    //},