sea_orm/executor/
select_ext.rs

1use crate::{
2    ConnectionTrait, DbErr, EntityTrait, Select, SelectFive, SelectFour, SelectSix, SelectThree,
3    SelectTwo, Selector, SelectorRaw, SelectorTrait, Topology,
4};
5use sea_query::{Expr, SelectStatement};
6
7// TODO: Move count here
8#[async_trait::async_trait]
9/// Helper trait for selectors with convenient methods
10pub trait SelectExt {
11    /// This method is unstable and is only used for internal testing.
12    /// It may be removed in the future.
13    #[doc(hidden)]
14    fn exists_query(self) -> SelectStatement;
15    /// Check if any records exist
16    async fn exists<C>(self, db: &C) -> Result<bool, DbErr>
17    where
18        C: ConnectionTrait,
19        Self: Send + Sized,
20    {
21        let stmt = self.exists_query();
22        Ok(db.query_one(&stmt).await?.is_some())
23    }
24}
25
26fn into_exists_query(mut stmt: SelectStatement) -> SelectStatement {
27    stmt.clear_selects();
28    // Expr::Custom has fewer branches, but this may not have any significant impact on performance.
29    stmt.expr(Expr::cust("1"));
30    stmt.reset_limit();
31    stmt.reset_offset();
32    stmt.clear_order_by();
33    stmt
34}
35
36impl<S> SelectExt for Selector<S>
37where
38    S: SelectorTrait,
39{
40    fn exists_query(self) -> SelectStatement {
41        into_exists_query(self.query)
42    }
43}
44
45#[async_trait::async_trait]
46impl<S> SelectExt for SelectorRaw<S>
47where
48    S: SelectorTrait,
49{
50    fn exists_query(self) -> SelectStatement {
51        let stmt = self.stmt;
52        let sub_query_sql = stmt.sql.trim().trim_end_matches(';').trim();
53        let exists_sql = format!("1 FROM ({sub_query_sql}) AS sub_query LIMIT 1");
54
55        let mut query = SelectStatement::new();
56        query.expr(if let Some(values) = stmt.values {
57            Expr::cust_with_values(exists_sql, values.0)
58        } else {
59            Expr::cust(exists_sql)
60        });
61        query
62    }
63}
64
65impl<E> SelectExt for Select<E>
66where
67    E: EntityTrait,
68{
69    fn exists_query(self) -> SelectStatement {
70        into_exists_query(self.query)
71    }
72}
73
74impl<E, F> SelectExt for SelectTwo<E, F>
75where
76    E: EntityTrait,
77    F: EntityTrait,
78{
79    fn exists_query(self) -> SelectStatement {
80        into_exists_query(self.query)
81    }
82}
83
84impl<E, F, G, TOP> SelectExt for SelectThree<E, F, G, TOP>
85where
86    E: EntityTrait,
87    F: EntityTrait,
88    G: EntityTrait,
89    TOP: Topology,
90{
91    fn exists_query(self) -> SelectStatement {
92        into_exists_query(self.query)
93    }
94}
95
96impl<E, F, G, H, TOP> SelectExt for SelectFour<E, F, G, H, TOP>
97where
98    E: EntityTrait,
99    F: EntityTrait,
100    G: EntityTrait,
101    H: EntityTrait,
102    TOP: Topology,
103{
104    fn exists_query(self) -> SelectStatement {
105        into_exists_query(self.query)
106    }
107}
108
109impl<E, F, G, H, I, TOP> SelectExt for SelectFive<E, F, G, H, I, TOP>
110where
111    E: EntityTrait,
112    F: EntityTrait,
113    G: EntityTrait,
114    H: EntityTrait,
115    I: EntityTrait,
116    TOP: Topology,
117{
118    fn exists_query(self) -> SelectStatement {
119        into_exists_query(self.query)
120    }
121}
122
123impl<E, F, G, H, I, J, TOP> SelectExt for SelectSix<E, F, G, H, I, J, TOP>
124where
125    E: EntityTrait,
126    F: EntityTrait,
127    G: EntityTrait,
128    H: EntityTrait,
129    I: EntityTrait,
130    J: EntityTrait,
131    TOP: Topology,
132{
133    fn exists_query(self) -> SelectStatement {
134        into_exists_query(self.query)
135    }
136}
137
138#[cfg(test)]
139mod tests {
140    use super::SelectExt;
141    use crate::entity::prelude::*;
142    use crate::{DbBackend, QueryOrder, QuerySelect, Statement, tests_cfg::*};
143
144    #[test]
145    fn exists_query_select_basic() {
146        let stmt = fruit::Entity::find().exists_query();
147        let sql = DbBackend::Postgres.build(&stmt).to_string();
148        assert_eq!(sql, r#"SELECT 1 FROM "fruit""#);
149    }
150
151    #[test]
152    fn exists_query_select_strips_limit_offset_order() {
153        let stmt = fruit::Entity::find()
154            .filter(fruit::Column::Id.gt(1))
155            .order_by_asc(fruit::Column::Id)
156            .limit(2)
157            .offset(4)
158            .exists_query();
159
160        let sql = DbBackend::Postgres.build(&stmt).to_string();
161        assert_eq!(sql, r#"SELECT 1 FROM "fruit" WHERE "fruit"."id" > 1"#);
162    }
163
164    #[test]
165    fn exists_query_selector_basic() {
166        let stmt = fruit::Entity::find()
167            .into_model::<fruit::Model>()
168            .exists_query();
169
170        let sql = DbBackend::Postgres.build(&stmt).to_string();
171        assert_eq!(sql, r#"SELECT 1 FROM "fruit""#);
172    }
173
174    #[test]
175    fn exists_query_selector_complex() {
176        let stmt = fruit::Entity::find()
177            .filter(fruit::Column::Id.gt(1))
178            .order_by_desc(fruit::Column::Id)
179            .limit(2)
180            .offset(4)
181            .into_model::<fruit::Model>()
182            .exists_query();
183
184        let sql = DbBackend::Postgres.build(&stmt).to_string();
185        assert_eq!(sql, r#"SELECT 1 FROM "fruit" WHERE "fruit"."id" > 1"#);
186    }
187
188    #[test]
189    fn exists_query_selector_raw_simple() {
190        let raw_stmt =
191            Statement::from_string(DbBackend::Postgres, r#"SELECT "fruit"."id" FROM "fruit""#);
192        let stmt = fruit::Entity::find().from_raw_sql(raw_stmt).exists_query();
193
194        let sql = DbBackend::Postgres.build(&stmt).to_string();
195        assert_eq!(
196            sql,
197            r#"SELECT 1 FROM (SELECT "fruit"."id" FROM "fruit") AS sub_query LIMIT 1"#
198        );
199    }
200
201    #[test]
202    fn exists_query_selector_raw_complex() {
203        let raw_stmt = Statement::from_string(
204            DbBackend::Postgres,
205            r#"SELECT "fruit"."id" FROM "fruit" WHERE "fruit"."id" > 1 ORDER BY "fruit"."id" DESC LIMIT 5 OFFSET 2"#,
206        );
207        let stmt = fruit::Entity::find().from_raw_sql(raw_stmt).exists_query();
208
209        let sql = DbBackend::Postgres.build(&stmt).to_string();
210        assert_eq!(
211            sql,
212            r#"SELECT 1 FROM (SELECT "fruit"."id" FROM "fruit" WHERE "fruit"."id" > 1 ORDER BY "fruit"."id" DESC LIMIT 5 OFFSET 2) AS sub_query LIMIT 1"#
213        );
214    }
215
216    #[test]
217    fn exists_query_select_two_simple() {
218        let stmt = cake::Entity::find()
219            .find_also_related(fruit::Entity)
220            .exists_query();
221
222        let sql = DbBackend::Postgres.build(&stmt).to_string();
223        assert_eq!(
224            sql,
225            r#"SELECT 1 FROM "cake" LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#
226        );
227    }
228
229    #[test]
230    fn exists_query_select_two_complex() {
231        let stmt = cake::Entity::find()
232            .find_also_related(fruit::Entity)
233            .filter(cake::Column::Id.gt(1))
234            .order_by_desc(cake::Column::Id)
235            .limit(2)
236            .offset(4)
237            .exists_query();
238
239        let sql = DbBackend::Postgres.build(&stmt).to_string();
240        assert_eq!(
241            sql,
242            [
243                r#"SELECT 1 FROM "cake""#,
244                r#"LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#,
245                r#"WHERE "cake"."id" > 1"#,
246            ]
247            .join(" ")
248        );
249    }
250
251    #[test]
252    fn exists_query_select_three_simple() {
253        let stmt = cake_filling::Entity::find()
254            .find_also_related(cake::Entity)
255            .find_also(cake_filling::Entity, filling::Entity)
256            .exists_query();
257
258        let sql = DbBackend::Postgres.build(&stmt).to_string();
259        assert_eq!(
260            sql,
261            [
262                r#"SELECT 1 FROM "cake_filling""#,
263                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
264                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
265            ]
266            .join(" ")
267        );
268    }
269
270    #[test]
271    fn exists_query_select_three_complex() {
272        let stmt = cake_filling::Entity::find()
273            .find_also_related(cake::Entity)
274            .find_also(cake_filling::Entity, filling::Entity)
275            .filter(cake_filling::Column::CakeId.gt(1))
276            .order_by_desc(cake_filling::Column::CakeId)
277            .limit(2)
278            .offset(4)
279            .exists_query();
280
281        let sql = DbBackend::Postgres.build(&stmt).to_string();
282        assert_eq!(
283            sql,
284            [
285                r#"SELECT 1 FROM "cake_filling""#,
286                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
287                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
288                r#"WHERE "cake_filling"."cake_id" > 1"#,
289            ]
290            .join(" ")
291        );
292    }
293
294    #[test]
295    fn exists_query_select_four_simple() {
296        let stmt = cake_filling::Entity::find()
297            .find_also_related(cake::Entity)
298            .find_also(cake_filling::Entity, filling::Entity)
299            .find_also(filling::Entity, ingredient::Entity)
300            .exists_query();
301
302        let sql = DbBackend::Postgres.build(&stmt).to_string();
303        assert_eq!(
304            sql,
305            [
306                r#"SELECT 1 FROM "cake_filling""#,
307                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
308                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
309                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
310            ]
311            .join(" ")
312        );
313    }
314
315    #[test]
316    fn exists_query_select_four_complex() {
317        let stmt = cake_filling::Entity::find()
318            .find_also_related(cake::Entity)
319            .find_also(cake_filling::Entity, filling::Entity)
320            .find_also(filling::Entity, ingredient::Entity)
321            .filter(cake_filling::Column::CakeId.gt(1))
322            .order_by_desc(cake_filling::Column::CakeId)
323            .limit(2)
324            .offset(4)
325            .exists_query();
326
327        let sql = DbBackend::Postgres.build(&stmt).to_string();
328        assert_eq!(
329            sql,
330            [
331                r#"SELECT 1 FROM "cake_filling""#,
332                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
333                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
334                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
335                r#"WHERE "cake_filling"."cake_id" > 1"#,
336            ]
337            .join(" ")
338        );
339    }
340
341    #[test]
342    fn exists_query_select_five_simple() {
343        let stmt = cake_filling::Entity::find()
344            .find_also_related(cake::Entity)
345            .find_also(cake_filling::Entity, filling::Entity)
346            .find_also(filling::Entity, ingredient::Entity)
347            .find_also(cake_filling::Entity, cake_filling_price::Entity)
348            .exists_query();
349
350        let sql = DbBackend::Postgres.build(&stmt).to_string();
351        assert_eq!(
352            sql,
353            [
354                r#"SELECT 1 FROM "cake_filling""#,
355                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
356                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
357                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
358                r#"LEFT JOIN "public"."cake_filling_price" ON "cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND "cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
359            ]
360            .join(" ")
361        );
362    }
363
364    #[test]
365    fn exists_query_select_five_complex() {
366        let stmt = cake_filling::Entity::find()
367            .find_also_related(cake::Entity)
368            .find_also(cake_filling::Entity, filling::Entity)
369            .find_also(filling::Entity, ingredient::Entity)
370            .find_also(cake_filling::Entity, cake_filling_price::Entity)
371            .filter(cake_filling::Column::CakeId.gt(1))
372            .order_by_desc(cake_filling::Column::CakeId)
373            .limit(2)
374            .offset(4)
375            .exists_query();
376
377        let sql = DbBackend::Postgres.build(&stmt).to_string();
378        assert_eq!(
379            sql,
380            [
381                r#"SELECT 1 FROM "cake_filling""#,
382                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
383                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
384                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
385                r#"LEFT JOIN "public"."cake_filling_price" ON "cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND "cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
386                r#"WHERE "cake_filling"."cake_id" > 1"#,
387            ]
388            .join(" ")
389        );
390    }
391
392    #[test]
393    fn exists_query_select_six_simple() {
394        let stmt = cake_filling::Entity::find()
395            .find_also_related(cake::Entity)
396            .find_also(cake_filling::Entity, filling::Entity)
397            .find_also(filling::Entity, ingredient::Entity)
398            .find_also(cake_filling::Entity, cake_filling_price::Entity)
399            .find_also(filling::Entity, cake_compact::Entity)
400            .exists_query();
401
402        let sql = DbBackend::Postgres.build(&stmt).to_string();
403        assert_eq!(
404            sql,
405            [
406                r#"SELECT 1 FROM "cake_filling""#,
407                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
408                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
409                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
410                r#"LEFT JOIN "public"."cake_filling_price" ON "cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND "cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
411                r#"LEFT JOIN "cake_filling" ON "filling"."id" = "cake_filling"."filling_id""#,
412                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
413            ]
414            .join(" ")
415        );
416    }
417
418    #[test]
419    fn exists_query_select_six_complex() {
420        let stmt = cake_filling::Entity::find()
421            .find_also_related(cake::Entity)
422            .find_also(cake_filling::Entity, filling::Entity)
423            .find_also(filling::Entity, ingredient::Entity)
424            .find_also(cake_filling::Entity, cake_filling_price::Entity)
425            .find_also(filling::Entity, cake_compact::Entity)
426            .filter(cake_filling::Column::CakeId.gt(1))
427            .order_by_desc(cake_filling::Column::CakeId)
428            .limit(2)
429            .offset(4)
430            .exists_query();
431
432        let sql = DbBackend::Postgres.build(&stmt).to_string();
433        assert_eq!(
434            sql,
435            [
436                r#"SELECT 1 FROM "cake_filling""#,
437                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
438                r#"LEFT JOIN "filling" ON "cake_filling"."filling_id" = "filling"."id""#,
439                r#"LEFT JOIN "ingredient" ON "filling"."id" = "ingredient"."filling_id""#,
440                r#"LEFT JOIN "public"."cake_filling_price" ON "cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND "cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
441                r#"LEFT JOIN "cake_filling" ON "filling"."id" = "cake_filling"."filling_id""#,
442                r#"LEFT JOIN "cake" ON "cake_filling"."cake_id" = "cake"."id""#,
443                r#"WHERE "cake_filling"."cake_id" > 1"#,
444            ]
445            .join(" ")
446        );
447    }
448}
sea_orm/executor/select_ext.rs

sea_orm/executor/
select_ext.rs
