Skip to main content

yang_base/table/table_query/
read.rs

1//! 读取执行:select/all/optional/one/count/paginate 及事务内变体。
2//!
3//! 公共入口按连接池后端运行时分派:MySQL 分支保持原有 `yang_db::QueryBuilder`
4//! 行为,PostgreSQL 分支由 `read_pg.rs` 提供(`postgres` feature)。
5
6#![cfg(any(feature = "mysql", feature = "postgres"))]
7
8use super::TableQuery;
9use crate::database::AnyTransaction;
10use crate::error::BaseError;
11
12/// 分页窗口计数在结果行中的别名;读取后从 `Record` 中剥离。
13#[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    // 不能用 into_map() + Record::from 重建:那会丢弃 JSON null sidecar。
19    record.remove_column(TOTAL_COUNT_ALIAS);
20    record
21}
22
23/// 数据库执行方法(需要启用 `mysql` 或 `postgres` feature)
24impl TableQuery {
25    /// 为分页查询补齐默认页码/每页大小并写回查询参数。
26    ///
27    /// 同时为分页结果追加主键 tiebreaker:非唯一排序键(或默认排序)下,仅靠
28    /// 业务排序无法保证跨页稳定,主键提供确定性的最终排序。
29    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        // 未显式排序时沿用表定义默认排序,再追加主键。
38        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    /// 执行分页查询并返回 schema-first [`Record`](crate::table::Record)。
58    ///
59    /// 未显式设置分页时会使用默认页码和默认每页大小,并确保数据查询带有 `LIMIT/OFFSET`。
60    pub async fn paginate_records(
61        self,
62    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
63        self.paginate().await
64    }
65
66    /// 执行分页查询(内部方法,供 [`TableQuery::paginate_records`] 使用)。
67    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    /// MySQL 分页实现:COUNT → LIMIT/OFFSET 数据查询。
82    #[cfg(feature = "mysql")]
83    async fn paginate_mysql(
84        self,
85    ) -> Result<crate::table::PaginatedResult<crate::table::Record>, BaseError> {
86        // 1. 检查数据库连接池是否存在
87        self.pool
88            .as_ref()
89            .ok_or(BaseError::DatabaseNotInitialized)?;
90
91        // 2. 获取分页参数,如果未设置则使用默认值,并写回数据查询
92        let (query, page, page_size) = self.with_effective_pagination()?;
93
94        // 3. 执行 COUNT(*) 查询获取总记录数
95        let total = query.count_internal().await?;
96
97        // 4. 如果总记录数为 0,直接返回空结果
98        if total == 0 {
99            return Ok(crate::table::PaginatedResult::empty(page, page_size));
100        }
101
102        // 5. 执行数据查询
103        let data = query.select_mysql().await?;
104
105        // 6. 构建并返回 PaginatedResult
106        Ok(crate::table::PaginatedResult::new(
107            data, total, page, page_size,
108        ))
109    }
110
111    /// PostgreSQL 分页实现:单查询窗口 `COUNT(*) OVER()` 同时返回当前页与总数。
112    ///
113    /// 窗口计数在同一条语句内完成,`total` 与 `items` 天然处于同一快照;仅当
114    /// 当前页为空(OFFSET 越界)无法从结果行取到 total 时,才回退到额外 COUNT。
115    #[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            // OFFSET 越界时结果为空,窗口计数也随之丢失,回退到 COUNT。
148            None => query.count_internal().await?,
149        };
150
151        Ok(crate::table::PaginatedResult::new(
152            data, total, page, page_size,
153        ))
154    }
155
156    /// 执行 COUNT 查询获取总记录数(内部方法,供 paginate 使用)
157    ///
158    /// COUNT 会泄露表基数,因此与 SELECT 使用相同的可读权限守卫;分页只约束当前页
159    /// 数据,不改变总记录数,因此先清除分页参数再计数。
160    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    /// MySQL COUNT 实现。
178    #[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    /// PostgreSQL COUNT 实现。
193    ///
194    /// 只应用 WHERE/软删守卫:PostgreSQL 的 `SELECT COUNT(*) ... ORDER BY 非分组列`
195    /// 会被拒绝,且计数本身不需要投影、排序与分页。
196    #[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    /// 执行 COUNT 查询获取总记录数
212    ///
213    /// # 返回值
214    ///
215    /// - `Ok(u64)`:查询成功,返回总记录数
216    /// - `Err(BaseError)`:查询失败,返回错误
217    pub async fn count(self) -> Result<u64, BaseError> {
218        self.count_internal().await.map(|n| n as u64)
219    }
220
221    /// 判断是否存在至少一条匹配记录(`SELECT 1 ... LIMIT 1` 语义)。
222    ///
223    /// 与 `count() == 0` 相比不扫描全部匹配行,适合权限/去重前置判断。
224    pub async fn exists(self) -> Result<bool, BaseError> {
225        // 与 COUNT 一样,存在性也会泄露表基数,必须经过同一可读权限守卫。
226        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    /// 执行 SELECT 查询操作(内部方法)
258    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    /// MySQL SELECT 实现。
271    #[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    /// MySQL 读计划(含 NULLS FIRST/LAST 模拟)。
285    ///
286    /// `yang-db` 的排序入口无法表达 `field IS NULL` 表达式,因此仅在有 nulls 排序
287    /// 需求时重建一次读计划,与 [`super::plan::TableQuery::apply_read_plan`] 的
288    /// 投影/守卫/分页语义保持一致。
289    #[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                // MySQL 没有 NULLS 关键字:`field IS NULL`(1/0)前置到主排序键。
329                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    /// PostgreSQL SELECT 实现(`Record` 实现 `PgRow` 解码)。
352    #[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    /// 查询全部匹配记录。
362    pub async fn all(self) -> Result<Vec<crate::table::Record>, BaseError> {
363        self.select().await
364    }
365
366    /// 在事务中执行 SELECT 查询并返回多条记录
367    ///
368    /// 与 [`TableQuery::all`] 完全一致的建句与权限/软删逻辑,但在调用方提供的
369    /// [`AnyTransaction`] 内执行,使「读-改-写」场景的读取与后续写入处于同一
370    /// 事务、看到一致快照。
371    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    /// 在事务中查询全部匹配记录。
396    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    /// 执行查询并返回可选的单条记录(内部方法)
404    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    /// MySQL 单条查询实现。
417    #[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    /// PostgreSQL 单条查询实现。
431    #[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    /// 查询可选单条记录。
441    pub async fn optional(self) -> Result<Option<crate::table::Record>, BaseError> {
442        self.fetch_optional().await
443    }
444
445    /// 查询单条记录;没有匹配记录时返回 [`BaseError::RecordNotFound`]。
446    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    /// MySQL 没有 `NULLS FIRST/LAST`:用 `field IS NULL` 排序表达式模拟。
473    #[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}