yang_base/table/table_query/
read.rs1#![cfg(any(feature = "mysql", feature = "postgres"))]
7
8use super::TableQuery;
9use crate::database::AnyTransaction;
10use crate::error::BaseError;
11
12#[cfg(feature = "postgres")]
14const TOTAL_COUNT_ALIAS: &str = "__yang_total";
15
16#[cfg(feature = "postgres")]
17fn strip_total_count(mut record: crate::table::Record) -> crate::table::Record {
18 record.remove_column(TOTAL_COUNT_ALIAS);
20 record
21}
22
23impl TableQuery {
25 pub(crate) fn with_effective_pagination(self) -> Result<(Self, usize, usize), BaseError> {
30 let page = self.query_params.page.unwrap_or(1);
31 let page_size = self
32 .query_params
33 .page_size
34 .unwrap_or(crate::table::query_params::DEFAULT_QUERY_PAGE_SIZE);
35 let mut query = self.page(page, page_size)?;
36
37 if query.query_params.order_by.is_empty() {
39 query.query_params.order_by = query.table_config.default_order.clone();
40 }
41 let primary_key = query.table_config.primary_key.clone();
42 if !query
43 .query_params
44 .order_by
45 .iter()
46 .any(|(field, _)| field == &primary_key)
47 {
48 query
49 .query_params
50 .order_by
51 .push((primary_key, crate::table::SortOrder::Asc));
52 }
53
54 Ok((query, page, page_size))
55 }
56
57 pub async fn paginate_records(
61 self,
62 ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
63 self.paginate().await
64 }
65
66 pub(crate) async fn paginate(
68 self,
69 ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
70 match self.backend()? {
71 #[cfg(feature = "mysql")]
72 yang_db::BackendKind::MySql => self.paginate_mysql().await,
73 #[cfg(feature = "postgres")]
74 yang_db::BackendKind::PostgreSql => self.paginate_pg().await,
75 other => Err(BaseError::UnsupportedCapability(format!(
76 "TableQuery 不支持当前数据库后端: {other:?}"
77 ))),
78 }
79 }
80
81 #[cfg(feature = "mysql")]
83 async fn paginate_mysql(
84 self,
85 ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
86 self.pool
88 .as_ref()
89 .ok_or(BaseError::DatabaseNotInitialized)?;
90
91 let (query, page, page_size) = self.with_effective_pagination()?;
93
94 let total = query.count_internal().await?;
96
97 if total == 0 {
99 return Ok(crate::table::PaginatedResult::empty(page, page_size));
100 }
101
102 let data = query.select_mysql().await?;
104
105 Ok(crate::table::PaginatedResult::new(
107 data, total, page, page_size,
108 ))
109 }
110
111 #[cfg(feature = "postgres")]
116 async fn paginate_pg(
117 self,
118 ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
119 self.pool
120 .as_ref()
121 .ok_or(BaseError::DatabaseNotInitialized)?;
122
123 let (query, page, page_size) = self.with_effective_pagination()?;
124 let database = query.pg_database()?;
125 let total_alias = yang_db::FieldRef::new(TOTAL_COUNT_ALIAS)
126 .map_err(|error| BaseError::ConfigError(format!("窗口计数别名不合法: {error}")))?;
127 let builder = query
128 .apply_pg_read_plan(database.table(&query.table_config.table_ref))?
129 .window_expr(
130 yang_db::postgres::query_builder::WindowFunction::count_all(),
131 yang_db::postgres::query_builder::WindowSpec::new(),
132 &total_alias,
133 );
134 let rows = builder
135 .select::<crate::table::Record>()
136 .await
137 .map_err(BaseError::DatabaseQueryFailed)?;
138
139 let total = rows
140 .first()
141 .and_then(|row| row.get(TOTAL_COUNT_ALIAS))
142 .and_then(serde_json::Value::as_u64)
143 .map(|value| value as usize);
144 let data = rows.into_iter().map(strip_total_count).collect();
145 let total = match total {
146 Some(total) => total,
147 None => query.count_internal().await?,
149 };
150
151 Ok(crate::table::PaginatedResult::new(
152 data, total, page, page_size,
153 ))
154 }
155
156 async fn count_internal(&self) -> Result<usize, BaseError> {
161 self.ensure_readable_projection()?;
162 let mut count_query = self.clone();
163 count_query.query_params.page = None;
164 count_query.query_params.page_size = None;
165
166 match count_query.backend()? {
167 #[cfg(feature = "mysql")]
168 yang_db::BackendKind::MySql => count_query.count_mysql().await,
169 #[cfg(feature = "postgres")]
170 yang_db::BackendKind::PostgreSql => count_query.count_pg().await,
171 other => Err(BaseError::UnsupportedCapability(format!(
172 "TableQuery 不支持当前数据库后端: {other:?}"
173 ))),
174 }
175 }
176
177 #[cfg(feature = "mysql")]
179 async fn count_mysql(&self) -> Result<usize, BaseError> {
180 let count = self
181 .compile_db_query()?
182 .count()
183 .await
184 .map_err(BaseError::DatabaseQueryFailed)?;
185 usize::try_from(count).map_err(|_| {
186 BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
187 "COUNT 结果超出 usize 范围".to_string(),
188 ))
189 })
190 }
191
192 #[cfg(feature = "postgres")]
197 async fn count_pg(&self) -> Result<usize, BaseError> {
198 let database = self.pg_database()?;
199 let count = self
200 .apply_pg_where_guards(database.table(&self.table_config.table_ref))?
201 .count()
202 .await
203 .map_err(BaseError::DatabaseQueryFailed)?;
204 usize::try_from(count).map_err(|_| {
205 BaseError::DatabaseQueryFailed(yang_db::DbError::QueryError(
206 "COUNT 结果为负数,超出 usize 范围".to_string(),
207 ))
208 })
209 }
210
211 pub async fn count(self) -> Result<u64, BaseError> {
218 self.count_internal().await.map(|n| n as u64)
219 }
220
221 pub async fn exists(self) -> Result<bool, BaseError> {
225 self.ensure_readable_projection()?;
227 match self.backend()? {
228 #[cfg(feature = "mysql")]
229 yang_db::BackendKind::MySql => Ok(self
230 .compile_db_query()?
231 .limit(1)
232 .find::<crate::table::Record>()
233 .await
234 .map_err(BaseError::DatabaseQueryFailed)?
235 .is_some()),
236 #[cfg(feature = "postgres")]
237 yang_db::BackendKind::PostgreSql => {
238 let database = self.pg_database()?;
239 let primary_key = self.table_config.primary_key.clone();
240 let key = self.field_ref(&primary_key)?;
241 let query = self
242 .apply_pg_where_guards(database.table(&self.table_config.table_ref))?
243 .field(key);
244 Ok(query
245 .limit(1)
246 .find::<crate::table::Record>()
247 .await
248 .map_err(BaseError::DatabaseQueryFailed)?
249 .is_some())
250 }
251 other => Err(BaseError::UnsupportedCapability(format!(
252 "TableQuery 不支持当前数据库后端: {other:?}"
253 ))),
254 }
255 }
256
257 async fn select(self) -> Result<Vec<crate::table::Record>, BaseError> {
259 match self.backend()? {
260 #[cfg(feature = "mysql")]
261 yang_db::BackendKind::MySql => self.select_mysql().await,
262 #[cfg(feature = "postgres")]
263 yang_db::BackendKind::PostgreSql => self.select_pg().await,
264 other => Err(BaseError::UnsupportedCapability(format!(
265 "TableQuery 不支持当前数据库后端: {other:?}"
266 ))),
267 }
268 }
269
270 #[cfg(feature = "mysql")]
272 async fn select_mysql(self) -> Result<Vec<crate::table::Record>, BaseError> {
273 let query = if self.nulls_orders.is_empty() {
274 self.compile_db_query()?
275 } else {
276 self.compile_mysql_query_with_nulls()?
277 };
278 query
279 .select::<crate::table::Record>()
280 .await
281 .map_err(BaseError::DatabaseQueryFailed)
282 }
283
284 #[cfg(feature = "mysql")]
290 fn compile_mysql_query_with_nulls(&self) -> Result<yang_db::QueryBuilder<'_>, BaseError> {
291 let pool = self
292 .pool
293 .as_ref()
294 .and_then(|pool| pool.as_mysql())
295 .ok_or(BaseError::DatabaseNotInitialized)?;
296 let mut query = yang_db::QueryBuilder::from_pool(pool, &self.table_config.table_ref);
297 let selected = self.query_params.fields.as_ref().map_or_else(
298 || {
299 self.default_read_fields()
300 .map(|values| values.into_iter().map(str::to_string).collect())
301 },
302 |values| Ok(values.clone()),
303 )?;
304 for name in selected {
305 query = query.field(self.field_ref(&name)?);
306 }
307 for condition in &self.query_params.where_conditions {
308 query = query
309 .where_predicate(&self.compile_predicate(condition)?)
310 .map_err(BaseError::DatabaseQueryFailed)?;
311 }
312 if !self.include_trashed {
313 if let Some(name) = &self.table_config.soft_delete_field {
314 query = query.where_null(self.field_ref(name)?);
315 }
316 }
317 let orders = if self.query_params.order_by.is_empty() {
318 &self.table_config.default_order
319 } else {
320 &self.query_params.order_by
321 };
322 for (name, order) in orders {
323 let db_order = match order {
324 crate::table::SortOrder::Asc => yang_db::SortOrder::Asc,
325 crate::table::SortOrder::Desc => yang_db::SortOrder::Desc,
326 };
327 if let Some(nulls_first) = self.nulls_orders.get(name) {
328 let nulls_dir = if *nulls_first { "DESC" } else { "ASC" };
330 let expr = yang_db::FieldRef::__from_validated_owned(format!(
331 "{name}` IS NULL {nulls_dir}, `{name}"
332 ));
333 query = query.order(&expr, db_order);
334 } else {
335 query = query.order(self.field_ref(name)?, db_order);
336 }
337 }
338 if let Some(page_size) = self.query_params.page_size {
339 let page = self.query_params.page.unwrap_or(1).max(1);
340 let limit = u64::try_from(page_size).map_err(|_| {
341 BaseError::ParamInvalid("page_size".to_string(), "分页大小超出范围".to_string())
342 })?;
343 let offset = u64::try_from((page - 1).saturating_mul(page_size)).map_err(|_| {
344 BaseError::ParamInvalid("page".to_string(), "分页偏移超出范围".to_string())
345 })?;
346 query = query.limit(limit).offset(offset);
347 }
348 Ok(query)
349 }
350
351 #[cfg(feature = "postgres")]
353 async fn select_pg(self) -> Result<Vec<crate::table::Record>, BaseError> {
354 let database = self.pg_database()?;
355 self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
356 .select::<crate::table::Record>()
357 .await
358 .map_err(BaseError::DatabaseQueryFailed)
359 }
360
361 pub async fn all(self) -> Result<Vec<crate::table::Record>, BaseError> {
363 self.select().await
364 }
365
366 pub(crate) async fn select_in_tx(
372 self,
373 tx: &mut AnyTransaction,
374 ) -> Result<Vec<crate::table::Record>, BaseError> {
375 match tx {
376 #[cfg(feature = "mysql")]
377 AnyTransaction::MySql(tx) => {
378 let query = tx.table(&self.table_config.table_ref);
379 self.apply_read_plan(query)?
380 .select::<crate::table::Record>()
381 .await
382 .map_err(BaseError::DatabaseQueryFailed)
383 }
384 #[cfg(feature = "postgres")]
385 AnyTransaction::Postgres(tx) => {
386 let query = tx.table(&self.table_config.table_ref);
387 self.apply_pg_read_plan(query)?
388 .select::<crate::table::Record>()
389 .await
390 .map_err(BaseError::DatabaseQueryFailed)
391 }
392 }
393 }
394
395 pub async fn all_in_tx(
397 self,
398 tx: &mut AnyTransaction,
399 ) -> Result<Vec<crate::table::Record>, BaseError> {
400 self.select_in_tx(tx).await
401 }
402
403 async fn fetch_optional(self) -> Result<Option<crate::table::Record>, BaseError> {
405 match self.backend()? {
406 #[cfg(feature = "mysql")]
407 yang_db::BackendKind::MySql => self.fetch_optional_mysql().await,
408 #[cfg(feature = "postgres")]
409 yang_db::BackendKind::PostgreSql => self.fetch_optional_pg().await,
410 other => Err(BaseError::UnsupportedCapability(format!(
411 "TableQuery 不支持当前数据库后端: {other:?}"
412 ))),
413 }
414 }
415
416 #[cfg(feature = "mysql")]
418 async fn fetch_optional_mysql(self) -> Result<Option<crate::table::Record>, BaseError> {
419 let query = if self.nulls_orders.is_empty() {
420 self.compile_db_query()?
421 } else {
422 self.compile_mysql_query_with_nulls()?
423 };
424 query
425 .find::<crate::table::Record>()
426 .await
427 .map_err(BaseError::DatabaseQueryFailed)
428 }
429
430 #[cfg(feature = "postgres")]
432 async fn fetch_optional_pg(self) -> Result<Option<crate::table::Record>, BaseError> {
433 let database = self.pg_database()?;
434 self.apply_pg_read_plan(database.table(&self.table_config.table_ref))?
435 .find::<crate::table::Record>()
436 .await
437 .map_err(BaseError::DatabaseQueryFailed)
438 }
439
440 pub async fn optional(self) -> Result<Option<crate::table::Record>, BaseError> {
442 self.fetch_optional().await
443 }
444
445 pub async fn one(self) -> Result<crate::table::Record, BaseError> {
447 let table_name = self.table_config.table_name.clone();
448 self.optional()
449 .await?
450 .ok_or_else(|| BaseError::RecordNotFound(format!("表 {table_name} 中没有匹配记录")))
451 }
452}
453
454#[cfg(all(test, feature = "mysql"))]
455mod tests {
456 use super::*;
457 use crate::database::AnyPool;
458 use crate::table::{Field, SortOrder, Table};
459 use std::sync::Arc;
460
461 fn lazy_mysql_query(table: Table) -> TableQuery {
462 let definition = table.build().expect("测试表定义应有效");
463 let pool = sqlx::MySqlPool::connect_lazy("mysql://root@127.0.0.1:3306/test")
464 .expect("惰性 MySQL 连接池应创建成功");
465 TableQuery::new(
466 definition.shared_config(),
467 Arc::from(vec!["admin".to_string()]),
468 Some(AnyPool::MySql(Arc::new(pool))),
469 )
470 }
471
472 #[tokio::test]
474 async fn mysql_nulls_ordering_uses_is_null_expression() {
475 let nulls_last = lazy_mysql_query(
476 Table::new("tq_mysql_nulls").fields([Field::id("id"), Field::integer("bucket")]),
477 )
478 .order_nulls("bucket", SortOrder::Asc, false)
479 .expect("order_nulls 应通过校验")
480 .page(1, 10)
481 .expect("分页参数应合法");
482 let sql = nulls_last
483 .compile_mysql_query_with_nulls()
484 .expect("MySQL 读计划应编译成功")
485 .try_to_sql()
486 .expect("MySQL 读计划应能渲染 SQL");
487 assert!(
488 sql.contains("`bucket` IS NULL ASC, `bucket` ASC"),
489 "NULLS LAST 模拟缺失: {sql}"
490 );
491
492 let nulls_first = lazy_mysql_query(
493 Table::new("tq_mysql_nulls2").fields([Field::id("id"), Field::integer("bucket")]),
494 )
495 .order_nulls("bucket", SortOrder::Desc, true)
496 .expect("order_nulls 应通过校验")
497 .page(1, 10)
498 .expect("分页参数应合法");
499 let sql = nulls_first
500 .compile_mysql_query_with_nulls()
501 .expect("MySQL 读计划应编译成功")
502 .try_to_sql()
503 .expect("MySQL 读计划应能渲染 SQL");
504 assert!(
505 sql.contains("`bucket` IS NULL DESC, `bucket` DESC"),
506 "NULLS FIRST 模拟缺失: {sql}"
507 );
508 }
509}