查询过滤

快速开始

#[derive(Entity)]
#[entity(table = "products")]
pub struct Product {
 #[id]
 pub id: Uuid,
 #[field(create, update, response)]
 #[filter]
 pub name: String,
 #[field(create, update, response)]
 #[filter(like)]
 pub description: String,
 #[field(create, update, response)]
 #[filter(range)]
 pub price: i64,
 #[field(create, response)]
 #[filter]
 pub category_id: Uuid,
 #[field(response)]
 #[auto]
 #[filter(range)]
 pub created_at: DateTime<Utc>,
}

生成的代码

查询结构体

/// 用于过滤Product实体的查询参数。
#[derive(Debug, Clone, Default)]
pub struct ProductQuery {
 /// 按精确name匹配过滤。
 pub name: Option<String>,
 /// 按description模式过滤(ILIKE)。
 pub description: Option<String>,
 /// 按最低价格过滤。
 pub price_from: Option<i64>,
 /// 按最高价格过滤。
 pub price_to: Option<i64>,
 /// 按精确category_id匹配过滤。
 pub category_id: Option<Uuid>,
 /// 按最早created_at过滤。
 pub created_at_from: Option<DateTime<Utc>>,
 /// 按最晚created_at过滤。
 pub created_at_to: Option<DateTime<Utc>>,
 /// 最大结果数。
 pub limit: Option<i64>,
 /// 跳过的结果数。
 pub offset: Option<i64>,
}

Repository方法

#[async_trait]
pub trait ProductRepository: Send + Sync {
 // ... 标准CRUD方法
 /// 使用过滤器查询产品。
 async fn query(&self, query: ProductQuery) -> Result<Vec<Product>, Self::Error>;
}

生成的SQL

SELECT id, name, description, price, category_id, created_at
FROM products
WHERE ($1 IS NULL OR name = $1)
 AND ($2 IS NULL OR description ILIKE $2)
 AND ($3 IS NULL OR price >= $3)
 AND ($4 IS NULL OR price <= $4)
 AND ($5 IS NULL OR category_id = $5)
 AND ($6 IS NULL OR created_at >= $6)
 AND ($7 IS NULL OR created_at <= $7)
ORDER BY created_at DESC
LIMIT $8 OFFSET $9

过滤器类型

精确匹配 (#[filter] 或 #[filter(eq)])

#[filter]
pub status: String,
#[filter(eq)] // 同上
pub category_id: Uuid,

pub status: Option<String>,
pub category_id: Option<Uuid>,

WHERE status = $1
 AND category_id = $2

模式匹配 (#[filter(like)])

#[filter(like)]
pub name: String,
#[filter(like)]
pub description: String,

pub name: Option<String>,
pub description: Option<String>,

WHERE name ILIKE $1
 AND description ILIKE $2

let query = ProductQuery {
 name: Some("%widget%".into()), // 包含 "widget"
 description: Some("premium%".into()), // 以 "premium" 开头
 ..Default::default()
};

范围过滤 (#[filter(range)])

#[filter(range)]
pub price: i64,
#[filter(range)]
pub created_at: DateTime<Utc>,

pub price_from: Option<i64>,
pub price_to: Option<i64>,
pub created_at_from: Option<DateTime<Utc>>,
pub created_at_to: Option<DateTime<Utc>>,

WHERE price >= $1 AND price <= $2
 AND created_at >= $3 AND created_at <= $4

使用示例

基本过滤

// 按类别查找产品
let query = ProductQuery {
 category_id: Some(electronics_category_id),
 ..Default::default()
};
let products = repo.query(query).await?;

分页

// 获取第2页(每页20项)
let query = ProductQuery {
 limit: Some(20),
 offset: Some(20),
 ..Default::default()
};
let products = repo.query(query).await?;

组合过滤器

// 搜索价格实惠的电子产品
let query = ProductQuery {
 category_id: Some(electronics_category_id),
 price_from: Some(0),
 price_to: Some(10000), // 100ドル.00
 name: Some("%phone%".into()),
 limit: Some(50),
 ..Default::default()
};
let products = repo.query(query).await?;

日期范围

// 获取本月创建的产品
let now = Utc::now();
let month_start = now.with_day(1).unwrap().date_naive().and_hms_opt(0, 0, 0).unwrap();
let query = ProductQuery {
 created_at_from: Some(month_start.and_utc()),
 created_at_to: Some(now),
 ..Default::default()
};
let products = repo.query(query).await?;

API端点集成

use axum::{extract::Query, Json};
#[derive(Deserialize)]
pub struct ProductQueryParams {
 pub name: Option<String>,
 pub category_id: Option<Uuid>,
 pub min_price: Option<i64>,
 pub max_price: Option<i64>,
 pub page: Option<i64>,
 pub per_page: Option<i64>,
}
async fn list_products(
 Query(params): Query<ProductQueryParams>,
 pool: Extension<PgPool>,
) -> Result<Json<Vec<ProductResponse>>, AppError> {
 let page = params.page.unwrap_or(1);
 let per_page = params.per_page.unwrap_or(20).min(100);
 let query = ProductQuery {
 name: params.name.map(|n| format!("%{}%", n)),
 category_id: params.category_id,
 price_from: params.min_price,
 price_to: params.max_price,
 limit: Some(per_page),
 offset: Some((page - 1) * per_page),
 ..Default::default()
 };
 let products = pool.query(query).await?;
 let responses: Vec<_> = products.into_iter().map(ProductResponse::from).collect();
 Ok(Json(responses))
}

与软删除配合

#[derive(Entity)]
#[entity(table = "documents", soft_delete)]
pub struct Document {
 #[id]
 pub id: Uuid,
 #[field(create, response)]
 #[filter(like)]
 pub title: String,
 #[field(skip)]
 pub deleted_at: Option<DateTime<Utc>>,
}

SELECT * FROM documents
WHERE deleted_at IS NULL
 AND ($1 IS NULL OR title ILIKE $1)
LIMIT $2 OFFSET $3

async fn query_with_deleted(&self, query: DocumentQuery) -> Result<Vec<Document>, Self::Error>;

自定义查询扩展

#[derive(Entity)]
#[entity(table = "products", sql = "trait")]
pub struct Product { /* ... */ }
pub trait ProductQueryExt {
 async fn search_fulltext(&self, term: &str, limit: i64) -> Result<Vec<Product>, sqlx::Error>;
 async fn find_by_tags(&self, tags: &[String]) -> Result<Vec<Product>, sqlx::Error>;
}
#[async_trait]
impl ProductQueryExt for PgPool {
 async fn search_fulltext(&self, term: &str, limit: i64) -> Result<Vec<Product>, sqlx::Error> {
 let rows: Vec<ProductRow> = sqlx::query_as(
 r#"
 SELECT * FROM products
 WHERE to_tsvector('english', name || ' ' || description)
 @@ plainto_tsquery('english', 1ドル)
 ORDER BY ts_rank(to_tsvector('english', name || ' ' || description),
 plainto_tsquery('english', 1ドル)) DESC
 LIMIT 2ドル
 "#
 )
 .bind(term)
 .bind(limit)
 .fetch_all(self)
 .await?;
 Ok(rows.into_iter().map(Product::from).collect())
 }
 async fn find_by_tags(&self, tags: &[String]) -> Result<Vec<Product>, sqlx::Error> {
 let rows: Vec<ProductRow> = sqlx::query_as(
 "SELECT * FROM products WHERE tags && 1ドル"
 )
 .bind(tags)
 .fetch_all(self)
 .await?;
 Ok(rows.into_iter().map(Product::from).collect())
 }
}

最佳实践

另见