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/// 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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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::{DbBackend, entity::*, query::*, tests_cfg::lunch_set};
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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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: ColumnAsExpr,
90 I: IntoIdentity,
91 {
92 self.query().expr(SelectExpr {
93 expr: col.into_column_as_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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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::{DbBackend, entity::*, query::*, tests_cfg::lunch_set};
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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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, ExprTrait}, 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("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::{DbBackend, entity::*, query::*, tests_cfg::cake};
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().join(join, rel.to_tbl.clone(), rel);
393 self
394 }
395
396 /// Join via [`RelationDef`] but in reverse direction.
397 /// Assume when there exist a relation A to B.
398 /// You can reverse join B from A.
399 fn join_rev(mut self, join: JoinType, rel: RelationDef) -> Self {
400 self.query().join(join, rel.from_tbl.clone(), rel);
401 self
402 }
403
404 /// Join via [`RelationDef`] with table alias.
405 fn join_as<I>(mut self, join: JoinType, mut rel: RelationDef, alias: I) -> Self
406 where
407 I: IntoIden,
408 {
409 let alias = alias.into_iden();
410 rel.to_tbl = rel.to_tbl.alias(alias.clone());
411 self.query().join(join, rel.to_tbl.clone(), rel);
412 self
413 }
414
415 /// Join via [`RelationDef`] with table alias but in reverse direction.
416 /// Assume when there exist a relation A to B.
417 /// You can reverse join B from A.
418 fn join_as_rev<I>(mut self, join: JoinType, mut rel: RelationDef, alias: I) -> Self
419 where
420 I: IntoIden,
421 {
422 let alias = alias.into_iden();
423 rel.from_tbl = rel.from_tbl.alias(alias.clone());
424 self.query().join(join, rel.from_tbl.clone(), rel);
425 self
426 }
427
428 /// Select lock
429 fn lock(mut self, lock_type: LockType) -> Self {
430 self.query().lock(lock_type);
431 self
432 }
433
434 /// Select lock shared
435 fn lock_shared(mut self) -> Self {
436 self.query().lock_shared();
437 self
438 }
439
440 /// Select lock exclusive
441 fn lock_exclusive(mut self) -> Self {
442 self.query().lock_exclusive();
443 self
444 }
445
446 /// Row locking with behavior (if supported).
447 ///
448 /// See [`SelectStatement::lock_with_behavior`](https://docs.rs/sea-query/*/sea_query/query/struct.SelectStatement.html#method.lock_with_behavior).
449 fn lock_with_behavior(mut self, r#type: LockType, behavior: LockBehavior) -> Self {
450 self.query().lock_with_behavior(r#type, behavior);
451 self
452 }
453
454 /// Add an expression to the select expression list.
455 /// ```
456 /// use sea_orm::sea_query::Expr;
457 /// use sea_orm::{DbBackend, QuerySelect, QueryTrait, entity::*, tests_cfg::cake};
458 ///
459 /// assert_eq!(
460 /// cake::Entity::find()
461 /// .select_only()
462 /// .expr(Expr::col((cake::Entity, cake::Column::Id)))
463 /// .build(DbBackend::MySql)
464 /// .to_string(),
465 /// "SELECT `cake`.`id` FROM `cake`"
466 /// );
467 /// ```
468 fn expr<T>(mut self, expr: T) -> Self
469 where
470 T: Into<SelectExpr>,
471 {
472 self.query().expr(expr);
473 self
474 }
475
476 /// Add select expressions from vector of [`SelectExpr`].
477 /// ```
478 /// use sea_orm::sea_query::Expr;
479 /// use sea_orm::{DbBackend, QuerySelect, QueryTrait, entity::*, tests_cfg::cake};
480 ///
481 /// assert_eq!(
482 /// cake::Entity::find()
483 /// .select_only()
484 /// .exprs([
485 /// Expr::col((cake::Entity, cake::Column::Id)),
486 /// Expr::col((cake::Entity, cake::Column::Name)),
487 /// ])
488 /// .build(DbBackend::MySql)
489 /// .to_string(),
490 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake`"
491 /// );
492 /// ```
493 fn exprs<T, I>(mut self, exprs: I) -> Self
494 where
495 T: Into<SelectExpr>,
496 I: IntoIterator<Item = T>,
497 {
498 self.query().exprs(exprs);
499 self
500 }
501
502 /// Select column.
503 /// ```
504 /// use sea_orm::sea_query::{Alias, Expr, Func};
505 /// use sea_orm::{DbBackend, QuerySelect, QueryTrait, entity::*, tests_cfg::cake};
506 ///
507 /// assert_eq!(
508 /// cake::Entity::find()
509 /// .expr_as(
510 /// Func::upper(Expr::col((cake::Entity, cake::Column::Name))),
511 /// "name_upper"
512 /// )
513 /// .build(DbBackend::MySql)
514 /// .to_string(),
515 /// "SELECT `cake`.`id`, `cake`.`name`, UPPER(`cake`.`name`) AS `name_upper` FROM `cake`"
516 /// );
517 /// ```
518 fn expr_as<T, A>(mut self, expr: T, alias: A) -> Self
519 where
520 T: Into<SimpleExpr>,
521 A: IntoIdentity,
522 {
523 self.query().expr_as(expr, alias.into_identity());
524 self
525 }
526
527 /// Shorthand of `expr_as(Expr::col((T, C)), A)`.
528 ///
529 /// ```
530 /// use sea_orm::sea_query::{Alias, Expr, Func};
531 /// use sea_orm::{DbBackend, QuerySelect, QueryTrait, entity::*, tests_cfg::cake};
532 ///
533 /// assert_eq!(
534 /// cake::Entity::find()
535 /// .select_only()
536 /// .tbl_col_as((cake::Entity, cake::Column::Name), "cake_name")
537 /// .build(DbBackend::MySql)
538 /// .to_string(),
539 /// "SELECT `cake`.`name` AS `cake_name` FROM `cake`"
540 /// );
541 /// ```
542 fn tbl_col_as<T, C, A>(mut self, (tbl, col): (T, C), alias: A) -> Self
543 where
544 T: IntoIden + 'static,
545 C: IntoIden + 'static,
546 A: IntoIdentity,
547 {
548 self.query()
549 .expr_as(Expr::col((tbl, col)), alias.into_identity());
550 self
551 }
552}
553
554// LINT: when the column does not appear in tables selected from
555/// Performs ORDER BY operations
556pub trait QueryOrder: Sized {
557 #[allow(missing_docs)]
558 type QueryStatement: OrderedStatement;
559
560 /// Add the query to perform an ORDER BY operation
561 fn query(&mut self) -> &mut SelectStatement;
562
563 /// Add an order_by expression
564 /// ```
565 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
566 ///
567 /// assert_eq!(
568 /// cake::Entity::find()
569 /// .order_by(cake::Column::Id, Order::Asc)
570 /// .order_by(cake::Column::Name, Order::Desc)
571 /// .build(DbBackend::MySql)
572 /// .to_string(),
573 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC, `cake`.`name` DESC"
574 /// );
575 /// ```
576 fn order_by<C>(mut self, col: C, ord: Order) -> Self
577 where
578 C: IntoSimpleExpr,
579 {
580 self.query().order_by_expr(col.into_simple_expr(), ord);
581 self
582 }
583
584 /// Add an order_by expression (ascending)
585 /// ```
586 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
587 ///
588 /// assert_eq!(
589 /// cake::Entity::find()
590 /// .order_by_asc(cake::Column::Id)
591 /// .build(DbBackend::MySql)
592 /// .to_string(),
593 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC"
594 /// );
595 /// ```
596 fn order_by_asc<C>(mut self, col: C) -> Self
597 where
598 C: IntoSimpleExpr,
599 {
600 self.query()
601 .order_by_expr(col.into_simple_expr(), Order::Asc);
602 self
603 }
604
605 /// Add an order_by expression (descending)
606 /// ```
607 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
608 ///
609 /// assert_eq!(
610 /// cake::Entity::find()
611 /// .order_by_desc(cake::Column::Id)
612 /// .build(DbBackend::MySql)
613 /// .to_string(),
614 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` DESC"
615 /// );
616 /// ```
617 fn order_by_desc<C>(mut self, col: C) -> Self
618 where
619 C: IntoSimpleExpr,
620 {
621 self.query()
622 .order_by_expr(col.into_simple_expr(), Order::Desc);
623 self
624 }
625
626 /// Add an order_by expression with nulls ordering option
627 /// ```
628 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
629 /// use sea_query::NullOrdering;
630 ///
631 /// assert_eq!(
632 /// cake::Entity::find()
633 /// .order_by_with_nulls(cake::Column::Id, Order::Asc, NullOrdering::First)
634 /// .build(DbBackend::Postgres)
635 /// .to_string(),
636 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" ORDER BY "cake"."id" ASC NULLS FIRST"#
637 /// );
638 /// ```
639 fn order_by_with_nulls<C>(mut self, col: C, ord: Order, nulls: NullOrdering) -> Self
640 where
641 C: IntoSimpleExpr,
642 {
643 self.query()
644 .order_by_expr_with_nulls(col.into_simple_expr(), ord, nulls);
645 self
646 }
647}
648
649// LINT: when the column does not appear in tables selected from
650/// Perform a FILTER opertation on a statement
651pub trait QueryFilter: Sized {
652 #[allow(missing_docs)]
653 type QueryStatement: ConditionalStatement;
654
655 /// Add the query to perform a FILTER on
656 fn query(&mut self) -> &mut Self::QueryStatement;
657
658 /// Add an AND WHERE expression
659 /// ```
660 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
661 ///
662 /// assert_eq!(
663 /// cake::Entity::find()
664 /// .filter(cake::Column::Id.eq(4))
665 /// .filter(cake::Column::Id.eq(5))
666 /// .build(DbBackend::MySql)
667 /// .to_string(),
668 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 AND `cake`.`id` = 5"
669 /// );
670 /// ```
671 ///
672 /// Add a condition tree.
673 /// ```
674 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
675 ///
676 /// assert_eq!(
677 /// cake::Entity::find()
678 /// .filter(
679 /// Condition::any()
680 /// .add(cake::Column::Id.eq(4))
681 /// .add(cake::Column::Id.eq(5))
682 /// )
683 /// .build(DbBackend::MySql)
684 /// .to_string(),
685 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 OR `cake`.`id` = 5"
686 /// );
687 /// ```
688 ///
689 /// Like above, but using the `IN` operator.
690 ///
691 /// ```
692 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
693 ///
694 /// assert_eq!(
695 /// cake::Entity::find()
696 /// .filter(cake::Column::Id.is_in([4, 5]))
697 /// .build(DbBackend::MySql)
698 /// .to_string(),
699 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` IN (4, 5)"
700 /// );
701 /// ```
702 ///
703 /// Like above, but using the `ANY` operator. Postgres only.
704 ///
705 /// ```
706 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
707 ///
708 /// assert_eq!(
709 /// cake::Entity::find()
710 /// .filter(cake::Column::Id.eq_any([4, 5]))
711 /// .build(DbBackend::Postgres),
712 /// Statement::from_sql_and_values(
713 /// DbBackend::Postgres,
714 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."id" = ANY($1)"#,
715 /// [vec![4, 5].into()]
716 /// )
717 /// );
718 /// ```
719 ///
720 /// Add a runtime-built condition tree.
721 /// ```
722 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
723 /// struct Input {
724 /// name: Option<String>,
725 /// }
726 /// let input = Input {
727 /// name: Some("cheese".to_owned()),
728 /// };
729 ///
730 /// let mut conditions = Condition::all();
731 /// if let Some(name) = input.name {
732 /// conditions = conditions.add(cake::Column::Name.contains(&name));
733 /// }
734 ///
735 /// assert_eq!(
736 /// cake::Entity::find()
737 /// .filter(conditions)
738 /// .build(DbBackend::MySql)
739 /// .to_string(),
740 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
741 /// );
742 /// ```
743 ///
744 /// Add a runtime-built condition tree, functional-way.
745 /// ```
746 /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
747 /// struct Input {
748 /// name: Option<String>,
749 /// }
750 /// let input = Input {
751 /// name: Some("cheese".to_owned()),
752 /// };
753 ///
754 /// assert_eq!(
755 /// cake::Entity::find()
756 /// .filter(
757 /// Condition::all().add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
758 /// )
759 /// .build(DbBackend::MySql)
760 /// .to_string(),
761 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
762 /// );
763 /// ```
764 ///
765 /// A slightly more complex example.
766 /// ```
767 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, sea_query::{Expr, ExprTrait}, DbBackend};
768 ///
769 /// assert_eq!(
770 /// cake::Entity::find()
771 /// .filter(
772 /// Condition::all()
773 /// .add(
774 /// Condition::all()
775 /// .not()
776 /// .add(Expr::val(1).eq(1))
777 /// .add(Expr::val(2).eq(2))
778 /// )
779 /// .add(
780 /// Condition::any()
781 /// .add(Expr::val(3).eq(3))
782 /// .add(Expr::val(4).eq(4))
783 /// )
784 /// )
785 /// .build(DbBackend::Postgres)
786 /// .to_string(),
787 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE (NOT (1 = 1 AND 2 = 2)) AND (3 = 3 OR 4 = 4)"#
788 /// );
789 /// ```
790 /// Use a sea_query expression
791 /// ```
792 /// use sea_orm::{entity::*, query::*, sea_query::{Expr, ExprTrait}, tests_cfg::fruit, DbBackend};
793 ///
794 /// assert_eq!(
795 /// fruit::Entity::find()
796 /// .filter(Expr::col(fruit::Column::CakeId).is_null())
797 /// .build(DbBackend::MySql)
798 /// .to_string(),
799 /// "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `cake_id` IS NULL"
800 /// );
801 /// ```
802 fn filter<F>(mut self, filter: F) -> Self
803 where
804 F: IntoCondition,
805 {
806 self.query().cond_where(filter.into_condition());
807 self
808 }
809
810 /// Like [`Self::filter`], but without consuming self
811 fn filter_mut<F>(&mut self, filter: F)
812 where
813 F: IntoCondition,
814 {
815 self.query().cond_where(filter.into_condition());
816 }
817
818 /// Apply a where condition using the model's primary key
819 /// ```
820 /// # use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::{cake, fruit}};
821 /// assert_eq!(
822 /// fruit::Entity::find()
823 /// .left_join(cake::Entity)
824 /// .belongs_to(&cake::Model {
825 /// id: 12,
826 /// name: "".into(),
827 /// })
828 /// .build(DbBackend::MySql)
829 /// .to_string(),
830 /// [
831 /// "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
832 /// "LEFT JOIN `cake` ON `fruit`.`cake_id` = `cake`.`id`",
833 /// "WHERE `cake`.`id` = 12",
834 /// ]
835 /// .join(" ")
836 /// );
837 /// ```
838 fn belongs_to<M>(mut self, model: &M) -> Self
839 where
840 M: ModelTrait,
841 {
842 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
843 let col = key.into_column();
844 self = self.filter(col.eq(model.get(col)));
845 }
846 self
847 }
848
849 /// Like `belongs_to`, but for an ActiveModel. Panic if primary key is not set.
850 #[doc(hidden)]
851 fn belongs_to_active_model<AM>(mut self, model: &AM) -> Self
852 where
853 AM: ActiveModelTrait,
854 {
855 for key in <AM::Entity as EntityTrait>::PrimaryKey::iter() {
856 let col = key.into_column();
857 self = self.filter(col.eq(model.get(col).unwrap()));
858 }
859 self
860 }
861
862 /// Like `belongs_to`, but via a table alias
863 /// ```
864 /// # use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::{cake, fruit}};
865 /// assert_eq!(
866 /// fruit::Entity::find()
867 /// .join_as(JoinType::LeftJoin, fruit::Relation::Cake.def(), "puff")
868 /// .belongs_to_tbl_alias(
869 /// &cake::Model {
870 /// id: 12,
871 /// name: "".into(),
872 /// },
873 /// "puff"
874 /// )
875 /// .build(DbBackend::MySql)
876 /// .to_string(),
877 /// [
878 /// "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
879 /// "LEFT JOIN `cake` AS `puff` ON `fruit`.`cake_id` = `puff`.`id`",
880 /// "WHERE `puff`.`id` = 12",
881 /// ]
882 /// .join(" ")
883 /// );
884 /// ```
885 fn belongs_to_tbl_alias<M>(mut self, model: &M, tbl_alias: &str) -> Self
886 where
887 M: ModelTrait,
888 {
889 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
890 let col = key.into_column();
891 let expr = Expr::col((Alias::new(tbl_alias), col)).eq(model.get(col));
892 self = self.filter(expr);
893 }
894 self
895 }
896}
897
898pub(crate) fn join_tbl_on_condition(
899 from_tbl: DynIden,
900 to_tbl: DynIden,
901 owner_keys: Identity,
902 foreign_keys: Identity,
903) -> Condition {
904 let mut cond = Condition::all();
905 for (owner_key, foreign_key) in owner_keys.into_iter().zip(foreign_keys.into_iter()) {
906 cond = cond
907 .add(Expr::col((from_tbl.clone(), owner_key)).equals((to_tbl.clone(), foreign_key)));
908 }
909 cond
910}