sea_orm/query/helper.rs
1use crate::{
2 ColumnAsExpr, ColumnTrait, EntityTrait, Identity, IntoIdentity, IntoSimpleExpr, Iterable,
3 ModelTrait, PrimaryKeyToColumn, RelationDef,
4};
5use sea_query::{
6 Alias, Expr, Iden, IntoCondition, IntoIden, LockBehavior, LockType, NullOrdering, SeaRc,
7 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: 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::{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("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().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(SeaRc::clone(&alias));
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(SeaRc::clone(&alias));
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::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
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::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
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::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
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 /// Same as `expr_as`. Here for legacy reasons.
528 ///
529 /// Select column.
530 ///
531 /// ```
532 /// use sea_orm::sea_query::{Alias, Expr, Func};
533 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
534 ///
535 /// assert_eq!(
536 /// cake::Entity::find()
537 /// .expr_as(
538 /// Func::upper(Expr::col((cake::Entity, cake::Column::Name))),
539 /// "name_upper"
540 /// )
541 /// .build(DbBackend::MySql)
542 /// .to_string(),
543 /// "SELECT `cake`.`id`, `cake`.`name`, UPPER(`cake`.`name`) AS `name_upper` FROM `cake`"
544 /// );
545 /// ```
546 fn expr_as_<T, A>(mut self, expr: T, alias: A) -> Self
547 where
548 T: Into<SimpleExpr>,
549 A: IntoIdentity,
550 {
551 self.query().expr_as(expr, alias.into_identity());
552 self
553 }
554
555 /// Shorthand of `expr_as(Expr::col((T, C)), A)`.
556 ///
557 /// ```
558 /// use sea_orm::sea_query::{Alias, Expr, Func};
559 /// use sea_orm::{entity::*, tests_cfg::cake, DbBackend, QuerySelect, QueryTrait};
560 ///
561 /// assert_eq!(
562 /// cake::Entity::find()
563 /// .select_only()
564 /// .tbl_col_as((cake::Entity, cake::Column::Name), "cake_name")
565 /// .build(DbBackend::MySql)
566 /// .to_string(),
567 /// "SELECT `cake`.`name` AS `cake_name` FROM `cake`"
568 /// );
569 /// ```
570 fn tbl_col_as<T, C, A>(mut self, (tbl, col): (T, C), alias: A) -> Self
571 where
572 T: IntoIden + 'static,
573 C: IntoIden + 'static,
574 A: IntoIdentity,
575 {
576 self.query()
577 .expr_as(Expr::col((tbl, col)), alias.into_identity());
578 self
579 }
580}
581
582// LINT: when the column does not appear in tables selected from
583/// Performs ORDER BY operations
584pub trait QueryOrder: Sized {
585 #[allow(missing_docs)]
586 type QueryStatement: OrderedStatement;
587
588 /// Add the query to perform an ORDER BY operation
589 fn query(&mut self) -> &mut SelectStatement;
590
591 /// Add an order_by expression
592 /// ```
593 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
594 ///
595 /// assert_eq!(
596 /// cake::Entity::find()
597 /// .order_by(cake::Column::Id, Order::Asc)
598 /// .order_by(cake::Column::Name, Order::Desc)
599 /// .build(DbBackend::MySql)
600 /// .to_string(),
601 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC, `cake`.`name` DESC"
602 /// );
603 /// ```
604 fn order_by<C>(mut self, col: C, ord: Order) -> Self
605 where
606 C: IntoSimpleExpr,
607 {
608 self.query().order_by_expr(col.into_simple_expr(), ord);
609 self
610 }
611
612 /// Add an order_by expression (ascending)
613 /// ```
614 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
615 ///
616 /// assert_eq!(
617 /// cake::Entity::find()
618 /// .order_by_asc(cake::Column::Id)
619 /// .build(DbBackend::MySql)
620 /// .to_string(),
621 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` ASC"
622 /// );
623 /// ```
624 fn order_by_asc<C>(mut self, col: C) -> Self
625 where
626 C: IntoSimpleExpr,
627 {
628 self.query()
629 .order_by_expr(col.into_simple_expr(), Order::Asc);
630 self
631 }
632
633 /// Add an order_by expression (descending)
634 /// ```
635 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
636 ///
637 /// assert_eq!(
638 /// cake::Entity::find()
639 /// .order_by_desc(cake::Column::Id)
640 /// .build(DbBackend::MySql)
641 /// .to_string(),
642 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` ORDER BY `cake`.`id` DESC"
643 /// );
644 /// ```
645 fn order_by_desc<C>(mut self, col: C) -> Self
646 where
647 C: IntoSimpleExpr,
648 {
649 self.query()
650 .order_by_expr(col.into_simple_expr(), Order::Desc);
651 self
652 }
653
654 /// Add an order_by expression with nulls ordering option
655 /// ```
656 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
657 /// use sea_query::NullOrdering;
658 ///
659 /// assert_eq!(
660 /// cake::Entity::find()
661 /// .order_by_with_nulls(cake::Column::Id, Order::Asc, NullOrdering::First)
662 /// .build(DbBackend::Postgres)
663 /// .to_string(),
664 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" ORDER BY "cake"."id" ASC NULLS FIRST"#
665 /// );
666 /// ```
667 fn order_by_with_nulls<C>(mut self, col: C, ord: Order, nulls: NullOrdering) -> Self
668 where
669 C: IntoSimpleExpr,
670 {
671 self.query()
672 .order_by_expr_with_nulls(col.into_simple_expr(), ord, nulls);
673 self
674 }
675}
676
677// LINT: when the column does not appear in tables selected from
678/// Perform a FILTER opertation on a statement
679pub trait QueryFilter: Sized {
680 #[allow(missing_docs)]
681 type QueryStatement: ConditionalStatement;
682
683 /// Add the query to perform a FILTER on
684 fn query(&mut self) -> &mut Self::QueryStatement;
685
686 /// Add an AND WHERE expression
687 /// ```
688 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
689 ///
690 /// assert_eq!(
691 /// cake::Entity::find()
692 /// .filter(cake::Column::Id.eq(4))
693 /// .filter(cake::Column::Id.eq(5))
694 /// .build(DbBackend::MySql)
695 /// .to_string(),
696 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 AND `cake`.`id` = 5"
697 /// );
698 /// ```
699 ///
700 /// Add a condition tree.
701 /// ```
702 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
703 ///
704 /// assert_eq!(
705 /// cake::Entity::find()
706 /// .filter(
707 /// Condition::any()
708 /// .add(cake::Column::Id.eq(4))
709 /// .add(cake::Column::Id.eq(5))
710 /// )
711 /// .build(DbBackend::MySql)
712 /// .to_string(),
713 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` = 4 OR `cake`.`id` = 5"
714 /// );
715 /// ```
716 ///
717 /// Like above, but using the `IN` operator.
718 ///
719 /// ```
720 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
721 ///
722 /// assert_eq!(
723 /// cake::Entity::find()
724 /// .filter(cake::Column::Id.is_in([4, 5]))
725 /// .build(DbBackend::MySql)
726 /// .to_string(),
727 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` IN (4, 5)"
728 /// );
729 /// ```
730 ///
731 /// Like above, but using the `ANY` operator. Postgres only.
732 ///
733 /// ```
734 /// use sea_orm::sea_query::{extension::postgres::PgFunc, Expr};
735 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
736 ///
737 /// assert_eq!(
738 /// cake::Entity::find()
739 /// .filter(Expr::col((cake::Entity, cake::Column::Id)).eq(PgFunc::any(vec![4, 5])))
740 /// .build(DbBackend::Postgres)
741 /// .to_string(),
742 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."id" = ANY(ARRAY [4,5])"#
743 /// );
744 /// ```
745 ///
746 /// Add a runtime-built condition tree.
747 /// ```
748 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
749 /// struct Input {
750 /// name: Option<String>,
751 /// }
752 /// let input = Input {
753 /// name: Some("cheese".to_owned()),
754 /// };
755 ///
756 /// let mut conditions = Condition::all();
757 /// if let Some(name) = input.name {
758 /// conditions = conditions.add(cake::Column::Name.contains(&name));
759 /// }
760 ///
761 /// assert_eq!(
762 /// cake::Entity::find()
763 /// .filter(conditions)
764 /// .build(DbBackend::MySql)
765 /// .to_string(),
766 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
767 /// );
768 /// ```
769 ///
770 /// Add a runtime-built condition tree, functional-way.
771 /// ```
772 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
773 /// struct Input {
774 /// name: Option<String>,
775 /// }
776 /// let input = Input {
777 /// name: Some("cheese".to_owned()),
778 /// };
779 ///
780 /// assert_eq!(
781 /// cake::Entity::find()
782 /// .filter(
783 /// Condition::all().add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
784 /// )
785 /// .build(DbBackend::MySql)
786 /// .to_string(),
787 /// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
788 /// );
789 /// ```
790 ///
791 /// A slightly more complex example.
792 /// ```
793 /// use sea_orm::{entity::*, query::*, tests_cfg::cake, sea_query::Expr, DbBackend};
794 ///
795 /// assert_eq!(
796 /// cake::Entity::find()
797 /// .filter(
798 /// Condition::all()
799 /// .add(
800 /// Condition::all()
801 /// .not()
802 /// .add(Expr::val(1).eq(1))
803 /// .add(Expr::val(2).eq(2))
804 /// )
805 /// .add(
806 /// Condition::any()
807 /// .add(Expr::val(3).eq(3))
808 /// .add(Expr::val(4).eq(4))
809 /// )
810 /// )
811 /// .build(DbBackend::Postgres)
812 /// .to_string(),
813 /// r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE (NOT (1 = 1 AND 2 = 2)) AND (3 = 3 OR 4 = 4)"#
814 /// );
815 /// ```
816 /// Use a sea_query expression
817 /// ```
818 /// use sea_orm::{entity::*, query::*, sea_query::Expr, tests_cfg::fruit, DbBackend};
819 ///
820 /// assert_eq!(
821 /// fruit::Entity::find()
822 /// .filter(Expr::col(fruit::Column::CakeId).is_null())
823 /// .build(DbBackend::MySql)
824 /// .to_string(),
825 /// "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `cake_id` IS NULL"
826 /// );
827 /// ```
828 fn filter<F>(mut self, filter: F) -> Self
829 where
830 F: IntoCondition,
831 {
832 self.query().cond_where(filter.into_condition());
833 self
834 }
835
836 /// Apply a where condition using the model's primary key
837 fn belongs_to<M>(mut self, model: &M) -> Self
838 where
839 M: ModelTrait,
840 {
841 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
842 let col = key.into_column();
843 self = self.filter(col.eq(model.get(col)));
844 }
845 self
846 }
847
848 /// Perform a check to determine table belongs to a Model through it's name alias
849 fn belongs_to_tbl_alias<M>(mut self, model: &M, tbl_alias: &str) -> Self
850 where
851 M: ModelTrait,
852 {
853 for key in <M::Entity as EntityTrait>::PrimaryKey::iter() {
854 let col = key.into_column();
855 let expr = Expr::col((Alias::new(tbl_alias), col)).eq(model.get(col));
856 self = self.filter(expr);
857 }
858 self
859 }
860}
861
862pub(crate) fn join_tbl_on_condition(
863 from_tbl: SeaRc<dyn Iden>,
864 to_tbl: SeaRc<dyn Iden>,
865 owner_keys: Identity,
866 foreign_keys: Identity,
867) -> Condition {
868 let mut cond = Condition::all();
869 for (owner_key, foreign_key) in owner_keys.into_iter().zip(foreign_keys.into_iter()) {
870 cond = cond.add(
871 Expr::col((SeaRc::clone(&from_tbl), owner_key))
872 .equals((SeaRc::clone(&to_tbl), foreign_key)),
873 );
874 }
875 cond
876}
877
878pub(crate) fn unpack_table_ref(table_ref: &TableRef) -> DynIden {
879 match table_ref {
880 TableRef::Table(tbl)
881 | TableRef::SchemaTable(_, tbl)
882 | TableRef::DatabaseSchemaTable(_, _, tbl)
883 | TableRef::TableAlias(tbl, _)
884 | TableRef::SchemaTableAlias(_, tbl, _)
885 | TableRef::DatabaseSchemaTableAlias(_, _, tbl, _)
886 | TableRef::SubQuery(_, tbl)
887 | TableRef::ValuesList(_, tbl)
888 | TableRef::FunctionCall(_, tbl) => SeaRc::clone(tbl),
889 }
890}
891
892pub(crate) fn unpack_table_alias(table_ref: &TableRef) -> Option<DynIden> {
893 match table_ref {
894 TableRef::Table(_)
895 | TableRef::SchemaTable(_, _)
896 | TableRef::DatabaseSchemaTable(_, _, _)
897 | TableRef::SubQuery(_, _)
898 | TableRef::ValuesList(_, _) => None,
899 TableRef::TableAlias(_, alias)
900 | TableRef::SchemaTableAlias(_, _, alias)
901 | TableRef::DatabaseSchemaTableAlias(_, _, _, alias)
902 | TableRef::FunctionCall(_, alias) => Some(SeaRc::clone(alias)),
903 }
904}