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