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