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