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