sea_orm/query/helper.rs
1use crate::{
2 ColumnTrait, EntityTrait, Identity, IntoIdentity, IntoSimpleExpr, Iterable, ModelTrait,
3 PrimaryKeyToColumn, RelationDef,
4};
5use sea_query::{
6 Alias, ConditionType, Expr, Iden, IntoCondition, IntoIden, LockBehavior, LockType,
7 NullOrdering, SeaRc, SelectExpr, SelectStatement, SimpleExpr, TableRef,
8};
9pub use sea_query::{Condition, ConditionalStatement, DynIden, JoinType, Order, OrderedStatement};
10
11use sea_query::IntoColumnRef;
12
13// LINT: when the column does not appear in tables selected from
14// LINT: when there is a group by clause, but some columns don't have aggregate functions
15// LINT: when the join table or column does not exists
16/// Abstract API for performing queries
17pub trait QuerySelect: Sized {
18 #[allow(missing_docs)]
19 type QueryStatement;
20
21 /// Add the select SQL statement
22 fn query(&mut self) -> &mut SelectStatement;
23
24 /// Clear the selection list
25 fn select_only(mut self) -> Self {
26 self.query().clear_selects();
27 self
28 }
29
30 /// Add a select column
31 /// ```
32 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
33 ///
34 /// assert_eq!(
35 /// cake::Entity::find()
36 /// .select_only()
37 /// .column(cake::Column::Name)
38 /// .build(DbBackend::Postgres)
39 /// .to_string(),
40 /// r#"SELECT "cake"."name" FROM "cake""#
41 /// );
42 /// ```
43 ///
44 /// Enum column will be casted into text (PostgreSQL only)
45 ///
46 /// ```
47 /// use sea_orm::{entity::*, query::*, tests_cfg::lunch_set, DbBackend};
48 ///
49 /// assert_eq!(
50 /// lunch_set::Entity::find()
51 /// .select_only()
52 /// .column(lunch_set::Column::Tea)
53 /// .build(DbBackend::Postgres)
54 /// .to_string(),
55 /// r#"SELECT CAST("lunch_set"."tea" AS text) FROM "lunch_set""#
56 /// );
57 /// assert_eq!(
58 /// lunch_set::Entity::find()
59 /// .select_only()
60 /// .column(lunch_set::Column::Tea)
61 /// .build(DbBackend::MySql)
62 /// .to_string(),
63 /// r#"SELECT `lunch_set`.`tea` FROM `lunch_set`"#
64 /// );
65 /// ```
66 fn column<C>(mut self, col: C) -> Self
67 where
68 C: ColumnTrait,
69 {
70 self.query().expr(col.select_as(col.into_expr()));
71 self
72 }
73
74 /// Add a select column with alias
75 /// ```
76 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
77 ///
78 /// assert_eq!(
79 /// cake::Entity::find()
80 /// .select_only()
81 /// .column_as(cake::Column::Id.count(), "count")
82 /// .build(DbBackend::Postgres)
83 /// .to_string(),
84 /// r#"SELECT COUNT("cake"."id") AS "count" FROM "cake""#
85 /// );
86 /// ```
87 fn column_as<C, I>(mut self, col: C, alias: I) -> Self
88 where
89 C: IntoSimpleExpr,
90 I: IntoIdentity,
91 {
92 self.query().expr(SelectExpr {
93 expr: col.into_simple_expr(),
94 alias: Some(SeaRc::new(alias.into_identity())),
95 window: None,
96 });
97 self
98 }
99
100 /// Select columns
101 ///
102 /// ```
103 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
104 ///
105 /// assert_eq!(
106 /// cake::Entity::find()
107 /// .select_only()
108 /// .columns([cake::Column::Id, cake::Column::Name])
109 /// .build(DbBackend::Postgres)
110 /// .to_string(),
111 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake""#
112 /// );
113 /// ```
114 ///
115 /// Conditionally select all columns expect a specific column
116 ///
117 /// ```
118 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
119 ///
120 /// assert_eq!(
121 /// cake::Entity::find()
122 /// .select_only()
123 /// .columns(cake::Column::iter().filter(|col| match col {
124 /// cake::Column::Id => false,
125 /// _ => true,
126 /// }))
127 /// .build(DbBackend::Postgres)
128 /// .to_string(),
129 /// r#"SELECT "cake"."name" FROM "cake""#
130 /// );
131 /// ```
132 ///
133 /// Enum column will be casted into text (PostgreSQL only)
134 ///
135 /// ```
136 /// use sea_orm::{entity::*, query::*, tests_cfg::lunch_set, DbBackend};
137 ///
138 /// assert_eq!(
139 /// lunch_set::Entity::find()
140 /// .select_only()
141 /// .columns([lunch_set::Column::Name, lunch_set::Column::Tea])
142 /// .build(DbBackend::Postgres)
143 /// .to_string(),
144 /// r#"SELECT "lunch_set"."name", CAST("lunch_set"."tea" AS text) FROM "lunch_set""#
145 /// );
146 /// assert_eq!(
147 /// lunch_set::Entity::find()
148 /// .select_only()
149 /// .columns([lunch_set::Column::Name, lunch_set::Column::Tea])
150 /// .build(DbBackend::MySql)
151 /// .to_string(),
152 /// r#"SELECT `lunch_set`.`name`, `lunch_set`.`tea` FROM `lunch_set`"#
153 /// );
154 /// ```
155 fn columns<C, I>(mut self, cols: I) -> Self
156 where
157 C: ColumnTrait,
158 I: IntoIterator<Item = C>,
159 {
160 for col in cols.into_iter() {
161 self = self.column(col);
162 }
163 self
164 }
165
166 /// Add an offset expression. Passing in None would remove the offset.
167 ///
168 /// ```
169 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
170 ///
171 /// assert_eq!(
172 /// cake::Entity::find()
173 /// .offset(10)
174 /// .build(DbBackend::MySql)
175 /// .to_string(),
176 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` OFFSET 10"
177 /// );
178 ///
179 /// assert_eq!(
180 /// cake::Entity::find()
181 /// .offset(Some(10))
182 /// .offset(Some(20))
183 /// .build(DbBackend::MySql)
184 /// .to_string(),
185 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` OFFSET 20"
186 /// );
187 ///
188 /// assert_eq!(
189 /// cake::Entity::find()
190 /// .offset(10)
191 /// .offset(None)
192 /// .build(DbBackend::MySql)
193 /// .to_string(),
194 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake`"
195 /// );
196 /// ```
197 fn offset<T>(mut self, offset: T) -> Self
198 where
199 T: Into<Option<u64>>,
200 {
201 if let Some(offset) = offset.into() {
202 self.query().offset(offset);
203 } else {
204 self.query().reset_offset();
205 }
206 self
207 }
208
209 /// Add a limit expression. Passing in None would remove the limit.
210 ///
211 /// ```
212 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
213 ///
214 /// assert_eq!(
215 /// cake::Entity::find()
216 /// .limit(10)
217 /// .build(DbBackend::MySql)
218 /// .to_string(),
219 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` LIMIT 10"
220 /// );
221 ///
222 /// assert_eq!(
223 /// cake::Entity::find()
224 /// .limit(Some(10))
225 /// .limit(Some(20))
226 /// .build(DbBackend::MySql)
227 /// .to_string(),
228 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` LIMIT 20"
229 /// );
230 ///
231 /// assert_eq!(
232 /// cake::Entity::find()
233 /// .limit(10)
234 /// .limit(None)
235 /// .build(DbBackend::MySql)
236 /// .to_string(),
237 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake`"
238 /// );
239 /// ```
240 fn limit<T>(mut self, limit: T) -> Self
241 where
242 T: Into<Option<u64>>,
243 {
244 if let Some(limit) = limit.into() {
245 self.query().limit(limit);
246 } else {
247 self.query().reset_limit();
248 }
249 self
250 }
251
252 /// Add a group by column
253 /// ```
254 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
255 ///
256 /// assert_eq!(
257 /// cake::Entity::find()
258 /// .select_only()
259 /// .column(cake::Column::Name)
260 /// .group_by(cake::Column::Name)
261 /// .build(DbBackend::Postgres)
262 /// .to_string(),
263 /// r#"SELECT "cake"."name" FROM "cake" GROUP BY "cake"."name""#
264 /// );
265 ///
266 /// assert_eq!(
267 /// cake::Entity::find()
268 /// .select_only()
269 /// .column_as(cake::Column::Id.count(), "count")
270 /// .column_as(cake::Column::Id.sum(), "sum_of_id")
271 /// .group_by(cake::Column::Name)
272 /// .build(DbBackend::Postgres)
273 /// .to_string(),
274 /// r#"SELECT COUNT("cake"."id") AS "count", SUM("cake"."id") AS "sum_of_id" FROM "cake" GROUP BY "cake"."name""#
275 /// );
276 /// ```
277 fn group_by<C>(mut self, col: C) -> Self
278 where
279 C: IntoSimpleExpr,
280 {
281 self.query().add_group_by([col.into_simple_expr()]);
282 self
283 }
284
285 /// Add an AND HAVING expression
286 /// ```
287 /// use sea_orm::{sea_query::{Alias, Expr}, entity::*, query::*, tests_cfg::cake, DbBackend};
288 ///
289 /// assert_eq!(
290 /// cake::Entity::find()
291 /// .having(cake::Column::Id.eq(4))
292 /// .having(cake::Column::Id.eq(5))
293 /// .build(DbBackend::MySql)
294 /// .to_string(),
295 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` HAVING `cake`.`id` = 4 AND `cake`.`id` = 5"
296 /// );
297 ///
298 /// assert_eq!(
299 /// cake::Entity::find()
300 /// .select_only()
301 /// .column_as(cake::Column::Id.count(), "count")
302 /// .column_as(cake::Column::Id.sum(), "sum_of_id")
303 /// .group_by(cake::Column::Name)
304 /// .having(Expr::col(Alias::new("count")).gt(6))
305 /// .build(DbBackend::MySql)
306 /// .to_string(),
307 /// "SELECT COUNT(`cake`.`id`) AS `count`, SUM(`cake`.`id`) AS `sum_of_id` FROM `cake` GROUP BY `cake`.`name` HAVING `count` > 6"
308 /// );
309 /// ```
310 fn having<F>(mut self, filter: F) -> Self
311 where
312 F: IntoCondition,
313 {
314 self.query().cond_having(filter.into_condition());
315 self
316 }
317
318 /// Add a DISTINCT expression
319 /// ```
320 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
321 /// struct Input {
322 /// name: Option<String>,
323 /// }
324 /// let input = Input {
325 /// name: Some("cheese".to_owned()),
326 /// };
327 /// assert_eq!(
328 /// cake::Entity::find()
329 /// .filter(
330 /// Condition::all().add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
331 /// )
332 /// .distinct()
333 /// .build(DbBackend::MySql)
334 /// .to_string(),
335 /// "SELECT DISTINCT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
336 /// );
337 /// ```
338 fn distinct(mut self) -> Self {
339 self.query().distinct();
340 self
341 }
342
343 /// Add a DISTINCT ON expression
344 /// NOTE: this function is only supported by `sqlx-postgres`
345 /// ```
346 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
347 /// struct Input {
348 /// name: Option<String>,
349 /// }
350 /// let input = Input {
351 /// name: Some("cheese".to_owned()),
352 /// };
353 /// assert_eq!(
354 /// cake::Entity::find()
355 /// .filter(
356 /// Condition::all().add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
357 /// )
358 /// .distinct_on([(cake::Entity, cake::Column::Name)])
359 /// .build(DbBackend::Postgres)
360 /// .to_string(),
361 /// r#"SELECT DISTINCT ON ("cake"."name") "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."name" LIKE '%cheese%'"#
362 /// );
363 /// ```
364 fn distinct_on<T, I>(mut self, cols: I) -> Self
365 where
366 T: IntoColumnRef,
367 I: IntoIterator<Item = T>,
368 {
369 self.query().distinct_on(cols);
370 self
371 }
372
373 #[doc(hidden)]
374 fn join_join(mut self, join: JoinType, rel: RelationDef, via: Option<RelationDef>) -> Self {
375 if let Some(via) = via {
376 self = self.join(join, via)
377 }
378 self.join(join, rel)
379 }
380
381 #[doc(hidden)]
382 fn join_join_rev(mut self, join: JoinType, rel: RelationDef, via: Option<RelationDef>) -> Self {
383 self = self.join_rev(join, rel);
384 if let Some(via) = via {
385 self = self.join_rev(join, via)
386 }
387 self
388 }
389
390 /// Join via [`RelationDef`].
391 fn join(mut self, join: JoinType, rel: RelationDef) -> Self {
392 self.query()
393 .join(join, rel.to_tbl.clone(), join_condition(rel));
394 self
395 }
396
397 /// Join via [`RelationDef`] but in reverse direction.
398 /// Assume when there exist a relation A to B.
399 /// You can reverse join B from A.
400 fn join_rev(mut self, join: JoinType, rel: RelationDef) -> Self {
401 self.query()
402 .join(join, rel.from_tbl.clone(), join_condition(rel));
403 self
404 }
405
406 /// Join via [`RelationDef`] with table alias.
407 fn join_as<I>(mut self, join: JoinType, mut rel: RelationDef, alias: I) -> Self
408 where
409 I: IntoIden,
410 {
411 let alias = alias.into_iden();
412 rel.to_tbl = rel.to_tbl.alias(SeaRc::clone(&alias));
413 self.query()
414 .join(join, rel.to_tbl.clone(), join_condition(rel));
415 self
416 }
417
418 /// Join via [`RelationDef`] with table alias but in reverse direction.
419 /// Assume when there exist a relation A to B.
420 /// You can reverse join B from A.
421 fn join_as_rev<I>(mut self, join: JoinType, mut rel: RelationDef, alias: I) -> Self
422 where
423 I: IntoIden,
424 {
425 let alias = alias.into_iden();
426 rel.from_tbl = rel.from_tbl.alias(SeaRc::clone(&alias));
427 self.query()
428 .join(join, rel.from_tbl.clone(), join_condition(rel));
429 self
430 }
431
432 /// Select lock
433 fn lock(mut self, lock_type: LockType) -> Self {
434 self.query().lock(lock_type);
435 self
436 }
437
438 /// Select lock shared
439 fn lock_shared(mut self) -> Self {
440 self.query().lock_shared();
441 self
442 }
443
444 /// Select lock exclusive
445 fn lock_exclusive(mut self) -> Self {
446 self.query().lock_exclusive();
447 self
448 }
449
450 /// Row locking with behavior (if supported).
451 ///
452 /// See [`SelectStatement::lock_with_behavior`](https://docs.rs/sea-query/*/sea_query/query/struct.SelectStatement.html#method.lock_with_behavior).
453 fn lock_with_behavior(mut self, r#type: LockType, behavior: LockBehavior) -> Self {
454 self.query().lock_with_behavior(r#type, behavior);
455 self
456 }
457
458 /// Add an expression to the select expression list.
459 /// ```
460 /// use sea_orm::sea_query::Expr;
461 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
462 ///
463 /// assert_eq!(
464 /// cake::Entity::find()
465 /// .select_only()
466 /// .expr(Expr::col((cake::Entity, cake::Column::Id)))
467 /// .build(DbBackend::MySql)
468 /// .to_string(),
469 /// "SELECT `cake`.`id` FROM `cake`"
470 /// );
471 /// ```
472 fn expr<T>(mut self, expr: T) -> Self
473 where
474 T: Into<SelectExpr>,
475 {
476 self.query().expr(expr);
477 self
478 }
479
480 /// Add select expressions from vector of [`SelectExpr`].
481 /// ```
482 /// use sea_orm::sea_query::Expr;
483 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
484 ///
485 /// assert_eq!(
486 /// cake::Entity::find()
487 /// .select_only()
488 /// .exprs([
489 /// Expr::col((cake::Entity, cake::Column::Id)),
490 /// Expr::col((cake::Entity, cake::Column::Name)),
491 /// ])
492 /// .build(DbBackend::MySql)
493 /// .to_string(),
494 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake`"
495 /// );
496 /// ```
497 fn exprs<T, I>(mut self, exprs: I) -> Self
498 where
499 T: Into<SelectExpr>,
500 I: IntoIterator<Item = T>,
501 {
502 self.query().exprs(exprs);
503 self
504 }
505
506 /// Select column.
507 /// ```
508 /// use sea_orm::sea_query::{Alias, Expr, Func};
509 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
510 ///
511 /// assert_eq!(
512 /// cake::Entity::find()
513 /// .expr_as(
514 /// Func::upper(Expr::col((cake::Entity, cake::Column::Name))),
515 /// "name_upper"
516 /// )
517 /// .build(DbBackend::MySql)
518 /// .to_string(),
519 /// "SELECT `cake`.`id`, `cake`.`name`, UPPER(`cake`.`name`) AS `name_upper` FROM `cake`"
520 /// );
521 /// ```
522 fn expr_as<T, A>(mut self, expr: T, alias: A) -> Self
523 where
524 T: Into<SimpleExpr>,
525 A: IntoIdentity,
526 {
527 self.query().expr_as(expr, alias.into_identity());
528 self
529 }
530
531 /// Same as `expr_as`. Here for legacy reasons.
532 ///
533 /// Select column.
534 ///
535 /// ```
536 /// use sea_orm::sea_query::{Alias, Expr, Func};
537 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
538 ///
539 /// assert_eq!(
540 /// cake::Entity::find()
541 /// .expr_as(
542 /// Func::upper(Expr::col((cake::Entity, cake::Column::Name))),
543 /// "name_upper"
544 /// )
545 /// .build(DbBackend::MySql)
546 /// .to_string(),
547 /// "SELECT `cake`.`id`, `cake`.`name`, UPPER(`cake`.`name`) AS `name_upper` FROM `cake`"
548 /// );
549 /// ```
550 fn expr_as_<T, A>(mut self, expr: T, alias: A) -> Self
551 where
552 T: Into<SimpleExpr>,
553 A: IntoIdentity,
554 {
555 self.query().expr_as(expr, alias.into_identity());
556 self
557 }
558
559 /// Shorthand of `expr_as(Expr::col((T, C)), A)`.
560 ///
561 /// ```
562 /// use sea_orm::sea_query::{Alias, Expr, Func};
563 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
564 ///
565 /// assert_eq!(
566 /// cake::Entity::find()
567 /// .select_only()
568 /// .tbl_col_as((cake::Entity, cake::Column::Name), "cake_name")
569 /// .build(DbBackend::MySql)
570 /// .to_string(),
571 /// "SELECT `cake`.`name` AS `cake_name` FROM `cake`"
572 /// );
573 /// ```
574 fn tbl_col_as<T, C, A>(mut self, (tbl, col): (T, C), alias: A) -> Self
575 where
576 T: IntoIden + 'static,
577 C: IntoIden + 'static,
578 A: IntoIdentity,
579 {
580 self.query()
581 .expr_as(Expr::col((tbl, col)), alias.into_identity());
582 self
583 }
584}
585
586// LINT: when the column does not appear in tables selected from
587/// Performs ORDER BY operations
588pub trait QueryOrder: Sized {
589 #[allow(missing_docs)]
590 type QueryStatement: OrderedStatement;
591
592 /// Add the query to perform an ORDER BY operation
593 fn query(&mut self) -> &mut SelectStatement;
594
595 /// Add an order_by expression
596 /// ```
597 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
598 ///
599 /// assert_eq!(
600 /// cake::Entity::find()
601 /// .order_by(cake::Column::Id, Order::Asc)
602 /// .order_by(cake::Column::Name, Order::Desc)
603 /// .build(DbBackend::MySql)
604 /// .to_string(),
605 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC, `cake`.`name` DESC"
606 /// );
607 /// ```
608 fn order_by<C>(mut self, col: C, ord: Order) -> Self
609 where
610 C: IntoSimpleExpr,
611 {
612 self.query().order_by_expr(col.into_simple_expr(), ord);
613 self
614 }
615
616 /// Add an order_by expression (ascending)
617 /// ```
618 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
619 ///
620 /// assert_eq!(
621 /// cake::Entity::find()
622 /// .order_by_asc(cake::Column::Id)
623 /// .build(DbBackend::MySql)
624 /// .to_string(),
625 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC"
626 /// );
627 /// ```
628 fn order_by_asc<C>(mut self, col: C) -> Self
629 where
630 C: IntoSimpleExpr,
631 {
632 self.query()
633 .order_by_expr(col.into_simple_expr(), Order::Asc);
634 self
635 }
636
637 /// Add an order_by expression (descending)
638 /// ```
639 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
640 ///
641 /// assert_eq!(
642 /// cake::Entity::find()
643 /// .order_by_desc(cake::Column::Id)
644 /// .build(DbBackend::MySql)
645 /// .to_string(),
646 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` DESC"
647 /// );
648 /// ```
649 fn order_by_desc<C>(mut self, col: C) -> Self
650 where
651 C: IntoSimpleExpr,
652 {
653 self.query()
654 .order_by_expr(col.into_simple_expr(), Order::Desc);
655 self
656 }
657
658 /// Add an order_by expression with nulls ordering option
659 /// ```
660 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
661 /// use sea_query::NullOrdering;
662 ///
663 /// assert_eq!(
664 /// cake::Entity::find()
665 /// .order_by_with_nulls(cake::Column::Id, Order::Asc, NullOrdering::First)
666 /// .build(DbBackend::Postgres)
667 /// .to_string(),
668 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" ORDER BY "cake"."id" ASC NULLS FIRST"#
669 /// );
670 /// ```
671 fn order_by_with_nulls<C>(mut self, col: C, ord: Order, nulls: NullOrdering) -> Self
672 where
673 C: IntoSimpleExpr,
674 {
675 self.query()
676 .order_by_expr_with_nulls(col.into_simple_expr(), ord, nulls);
677 self
678 }
679}
680
681// LINT: when the column does not appear in tables selected from
682/// Perform a FILTER opertation on a statement
683pub trait QueryFilter: Sized {
684 #[allow(missing_docs)]
685 type QueryStatement: ConditionalStatement;
686
687 /// Add the query to perform a FILTER on
688 fn query(&mut self) -> &mut Self::QueryStatement;
689
690 /// Add an AND WHERE expression
691 /// ```
692 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
693 ///
694 /// assert_eq!(
695 /// cake::Entity::find()
696 /// .filter(cake::Column::Id.eq(4))
697 /// .filter(cake::Column::Id.eq(5))
698 /// .build(DbBackend::MySql)
699 /// .to_string(),
700 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 AND `cake`.`id` = 5"
701 /// );
702 /// ```
703 ///
704 /// Add a condition tree.
705 /// ```
706 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
707 ///
708 /// assert_eq!(
709 /// cake::Entity::find()
710 /// .filter(
711 /// Condition::any()
712 /// .add(cake::Column::Id.eq(4))
713 /// .add(cake::Column::Id.eq(5))
714 /// )
715 /// .build(DbBackend::MySql)
716 /// .to_string(),
717 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 OR `cake`.`id` = 5"
718 /// );
719 /// ```
720 ///
721 /// Like above, but using the `IN` operator.
722 ///
723 /// ```
724 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
725 ///
726 /// assert_eq!(
727 /// cake::Entity::find()
728 /// .filter(cake::Column::Id.is_in([4, 5]))
729 /// .build(DbBackend::MySql)
730 /// .to_string(),
731 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` IN (4, 5)"
732 /// );
733 /// ```
734 ///
735 /// Like above, but using the `ANY` operator. Postgres only.
736 ///
737 /// ```
738 /// use sea_orm::sea_query::{extension::postgres::PgFunc, Expr};
739 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
740 ///
741 /// assert_eq!(
742 /// cake::Entity::find()
743 /// .filter(Expr::col((cake::Entity, cake::Column::Id)).eq(PgFunc::any(vec![4, 5])))
744 /// .build(DbBackend::Postgres)
745 /// .to_string(),
746 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."id" = ANY(ARRAY [4,5])"#
747 /// );
748 /// ```
749 ///
750 /// Add a runtime-built condition tree.
751 /// ```
752 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
753 /// struct Input {
754 /// name: Option<String>,
755 /// }
756 /// let input = Input {
757 /// name: Some("cheese".to_owned()),
758 /// };
759 ///
760 /// let mut conditions = Condition::all();
761 /// if let Some(name) = input.name {
762 /// conditions = conditions.add(cake::Column::Name.contains(&name));
763 /// }
764 ///
765 /// assert_eq!(
766 /// cake::Entity::find()
767 /// .filter(conditions)
768 /// .build(DbBackend::MySql)
769 /// .to_string(),
770 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
771 /// );
772 /// ```
773 ///
774 /// Add a runtime-built condition tree, functional-way.
775 /// ```
776 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
777 /// struct Input {
778 /// name: Option<String>,
779 /// }
780 /// let input = Input {
781 /// name: Some("cheese".to_owned()),
782 /// };
783 ///
784 /// assert_eq!(
785 /// cake::Entity::find()
786 /// .filter(
787 /// Condition::all().add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
788 /// )
789 /// .build(DbBackend::MySql)
790 /// .to_string(),
791 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
792 /// );
793 /// ```
794 ///
795 /// A slightly more complex example.
796 /// ```
797 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, sea_query::Expr, DbBackend};
798 ///
799 /// assert_eq!(
800 /// cake::Entity::find()
801 /// .filter(
802 /// Condition::all()
803 /// .add(
804 /// Condition::all()
805 /// .not()
806 /// .add(Expr::val(1).eq(1))
807 /// .add(Expr::val(2).eq(2))
808 /// )
809 /// .add(
810 /// Condition::any()
811 /// .add(Expr::val(3).eq(3))
812 /// .add(Expr::val(4).eq(4))
813 /// )
814 /// )
815 /// .build(DbBackend::Postgres)
816 /// .to_string(),
817 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE (NOT (1 = 1 AND 2 = 2)) AND (3 = 3 OR 4 = 4)"#
818 /// );
819 /// ```
820 /// Use a sea_query expression
821 /// ```
822 /// use sea_orm::{entity::*, query::*, sea_query::Expr, tests_cfg::fruit, DbBackend};
823 ///
824 /// assert_eq!(
825 /// fruit::Entity::find()
826 /// .filter(Expr::col(fruit::Column::CakeId).is_null())
827 /// .build(DbBackend::MySql)
828 /// .to_string(),
829 /// "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `cake_id` IS NULL"
830 /// );
831 /// ```
832 fn filter<F>(mut self, filter: F) -> Self
833 where
834 F: IntoCondition,
835 {
836 self.query().cond_where(filter.into_condition());
837 self
838 }
839
840 /// Apply a where condition using the model's primary key
841 fn belongs_to<M>(mut self, model: &M) -> Self
842 where
843 M: ModelTrait,
844 {
845 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
846 let col = key.into_column();
847 self = self.filter(col.eq(model.get(col)));
848 }
849 self
850 }
851
852 /// Perform a check to determine table belongs to a Model through it's name alias
853 fn belongs_to_tbl_alias<M>(mut self, model: &M, tbl_alias: &str) -> Self
854 where
855 M: ModelTrait,
856 {
857 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
858 let col = key.into_column();
859 let expr = Expr::col((Alias::new(tbl_alias), col)).eq(model.get(col));
860 self = self.filter(expr);
861 }
862 self
863 }
864}
865
866pub(crate) fn join_condition(mut rel: RelationDef) -> Condition {
867 // Use table alias (if any) to construct the join condition
868 let from_tbl = match unpack_table_alias(&rel.from_tbl) {
869 Some(alias) => alias,
870 None => unpack_table_ref(&rel.from_tbl),
871 };
872 let to_tbl = match unpack_table_alias(&rel.to_tbl) {
873 Some(alias) => alias,
874 None => unpack_table_ref(&rel.to_tbl),
875 };
876 let owner_keys = rel.from_col;
877 let foreign_keys = rel.to_col;
878
879 let mut condition = match rel.condition_type {
880 ConditionType::All => Condition::all(),
881 ConditionType::Any => Condition::any(),
882 };
883
884 condition = condition.add(join_tbl_on_condition(
885 SeaRc::clone(&from_tbl),
886 SeaRc::clone(&to_tbl),
887 owner_keys,
888 foreign_keys,
889 ));
890 if let Some(f) = rel.on_condition.take() {
891 condition = condition.add(f(from_tbl, to_tbl));
892 }
893
894 condition
895}
896
897pub(crate) fn join_tbl_on_condition(
898 from_tbl: SeaRc<dyn Iden>,
899 to_tbl: SeaRc<dyn Iden>,
900 owner_keys: Identity,
901 foreign_keys: Identity,
902) -> Condition {
903 let mut cond = Condition::all();
904 for (owner_key, foreign_key) in owner_keys.into_iter().zip(foreign_keys.into_iter()) {
905 cond = cond.add(
906 Expr::col((SeaRc::clone(&from_tbl), owner_key))
907 .equals((SeaRc::clone(&to_tbl), foreign_key)),
908 );
909 }
910 cond
911}
912
913pub(crate) fn unpack_table_ref(table_ref: &TableRef) -> DynIden {
914 match table_ref {
915 TableRef::Table(tbl)
916 | TableRef::SchemaTable(_, tbl)
917 | TableRef::DatabaseSchemaTable(_, _, tbl)
918 | TableRef::TableAlias(tbl, _)
919 | TableRef::SchemaTableAlias(_, tbl, _)
920 | TableRef::DatabaseSchemaTableAlias(_, _, tbl, _)
921 | TableRef::SubQuery(_, tbl)
922 | TableRef::ValuesList(_, tbl)
923 | TableRef::FunctionCall(_, tbl) => SeaRc::clone(tbl),
924 }
925}
926
927pub(crate) fn unpack_table_alias(table_ref: &TableRef) -> Option<DynIden> {
928 match table_ref {
929 TableRef::Table(_)
930 | TableRef::SchemaTable(_, _)
931 | TableRef::DatabaseSchemaTable(_, _, _)
932 | TableRef::SubQuery(_, _)
933 | TableRef::ValuesList(_, _) => None,
934 TableRef::TableAlias(_, alias)
935 | TableRef::SchemaTableAlias(_, _, alias)
936 | TableRef::DatabaseSchemaTableAlias(_, _, _, alias)
937 | TableRef::FunctionCall(_, alias) => Some(SeaRc::clone(alias)),
938 }
939}