Rust的Axum+sqlx封装一个简单的分页查询函数

封装

// 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> {
        let mut query_builder: QueryBuilder<Postgres> = QueryBuilder::new(r#"SELECT "#);
        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("created_at DESC");

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

        query_builder.push(" ORDER BY ");
        query_builder.push_bind(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| {
    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);

    if let Some(user_name) = &payload.user_name {
        if !user_name.is_empty() {
            qb.push(" AND u.user_name = ");
            qb.push_bind(user_name);
        }
    }
};

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

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