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