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