sea_query/query/select.rs
1use inherent::inherent;
2use std::borrow::Cow;
3
4use crate::{
5 ColumnRef, ConditionHolder, ConditionalStatement, DynIden, Expr, FunctionCall, IntoColumnRef,
6 IntoCondition, IntoIden, IntoTableRef, IntoValueTuple, JoinOn, JoinType, LogicalChainOper,
7 NullOrdering, Order, OrderExpr, OrderedStatement, QueryStatement, QueryStatementBuilder,
8 QueryStatementWriter, SqlWriter, SubQueryStatement, TableRef, Value, ValueTuple, Values,
9 WindowStatement, WithClause, WithQuery, backend::QueryBuilder,
10};
11
12/// Select rows from an existing table
13///
14/// # Examples
15///
16/// ```
17/// use sea_query::{*, tests_cfg::*};
18///
19/// let query = Query::select()
20/// .column(Char::Character)
21/// .column((Font::Table, Font::Name))
22/// .from(Char::Table)
23/// .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
24/// .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
25/// .and_where(Expr::col(Char::Character).like("A%"))
26/// .to_owned();
27///
28/// assert_eq!(
29/// query.to_string(MysqlQueryBuilder),
30/// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` WHERE `size_w` IN (3, 4) AND `character` LIKE 'A%'"#
31/// );
32/// assert_eq!(
33/// query.to_string(PostgresQueryBuilder),
34/// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
35/// );
36/// assert_eq!(
37/// query.to_string(SqliteQueryBuilder),
38/// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
39/// );
40/// ```
41#[derive(Default, Debug, Clone, PartialEq)]
42pub struct SelectStatement {
43 pub(crate) distinct: Option<SelectDistinct>,
44 pub(crate) selects: Vec<SelectExpr>,
45 pub(crate) from: Vec<TableRef>,
46 pub(crate) join: Vec<JoinExpr>,
47 pub(crate) r#where: ConditionHolder,
48 pub(crate) groups: Vec<Expr>,
49 pub(crate) having: ConditionHolder,
50 pub(crate) unions: Vec<(UnionType, SelectStatement)>,
51 pub(crate) orders: Vec<OrderExpr>,
52 pub(crate) limit: Option<Value>,
53 pub(crate) offset: Option<Value>,
54 pub(crate) lock: Option<LockClause>,
55 pub(crate) window: Option<(DynIden, WindowStatement)>,
56 pub(crate) with: Option<Box<WithClause>>,
57 #[cfg(feature = "backend-postgres")]
58 pub(crate) table_sample: Option<crate::extension::postgres::TableSample>,
59 #[cfg(feature = "backend-mysql")]
60 pub(crate) index_hints: std::collections::HashMap<
61 index_hint::IndexHintKey,
62 Vec<crate::extension::mysql::IndexHint>,
63 >,
64}
65
66#[cfg(feature = "backend-mysql")]
67mod index_hint {
68 use crate::*;
69 #[derive(Debug, Clone, PartialEq, Eq, Hash)]
70 pub(crate) enum IndexHintKey {
71 Table(TableName, Option<DynIden>),
72 Alias(DynIden),
73 }
74
75 impl From<TableRef> for IndexHintKey {
76 fn from(value: TableRef) -> Self {
77 match value {
78 TableRef::Table(table_name, alias) => Self::Table(table_name, alias),
79 TableRef::SubQuery(_, alias)
80 | TableRef::ValuesList(_, alias)
81 | TableRef::FunctionCall(_, alias) => Self::Alias(alias),
82 }
83 }
84 }
85
86 impl From<&TableRef> for IndexHintKey {
87 fn from(value: &TableRef) -> Self {
88 match value {
89 TableRef::Table(table_name, alias) => {
90 Self::Table(table_name.clone(), alias.clone())
91 }
92 TableRef::SubQuery(_, alias)
93 | TableRef::ValuesList(_, alias)
94 | TableRef::FunctionCall(_, alias) => Self::Alias(alias.clone()),
95 }
96 }
97 }
98
99 impl PartialEq<&TableRef> for IndexHintKey {
100 fn eq(&self, other: &&TableRef) -> bool {
101 match (self, other) {
102 (Self::Table(table_name, alias), TableRef::Table(table_name2, alias2)) => {
103 table_name == table_name2 && alias == alias2
104 }
105 (
106 Self::Alias(alias),
107 TableRef::SubQuery(_, alias2)
108 | TableRef::ValuesList(_, alias2)
109 | TableRef::FunctionCall(_, alias2),
110 ) => alias == alias2,
111 _ => false,
112 }
113 }
114 }
115}
116
117/// List of distinct keywords that can be used in select statement
118#[derive(Debug, Clone, PartialEq)]
119#[non_exhaustive]
120pub enum SelectDistinct {
121 All,
122 Distinct,
123 DistinctRow,
124 DistinctOn(Vec<ColumnRef>),
125}
126
127/// Window type in [`SelectExpr`]
128#[derive(Debug, Clone, PartialEq)]
129#[non_exhaustive]
130pub enum WindowSelectType {
131 /// Name in [`SelectStatement`]
132 Name(DynIden),
133 /// Inline query in [`SelectExpr`]
134 Query(WindowStatement),
135}
136
137/// Select expression used in select statement
138#[derive(Debug, Clone, PartialEq)]
139pub struct SelectExpr {
140 pub expr: Expr,
141 pub alias: Option<DynIden>,
142 pub window: Option<WindowSelectType>,
143}
144
145/// Join expression used in select statement
146#[derive(Debug, Clone, PartialEq)]
147pub struct JoinExpr {
148 pub join: JoinType,
149 pub table: Box<TableRef>,
150 pub on: Option<JoinOn>,
151 pub lateral: bool,
152}
153
154/// List of lock types that can be used in select statement
155#[derive(Debug, Clone, Copy, PartialEq, Eq)]
156#[non_exhaustive]
157pub enum LockType {
158 /// Exclusive lock
159 Update,
160 NoKeyUpdate,
161 /// Shared lock
162 Share,
163 KeyShare,
164}
165
166/// List of lock behavior can be used in select statement
167#[derive(Debug, Clone, Copy, PartialEq, Eq)]
168#[non_exhaustive]
169pub enum LockBehavior {
170 Nowait,
171 SkipLocked,
172}
173
174#[derive(Debug, Clone, PartialEq)]
175pub struct LockClause {
176 pub(crate) r#type: LockType,
177 pub(crate) tables: Vec<TableRef>,
178 pub(crate) behavior: Option<LockBehavior>,
179}
180
181/// List of union types that can be used in union clause
182#[derive(Debug, Clone, Copy, PartialEq, Eq)]
183#[non_exhaustive]
184pub enum UnionType {
185 Intersect,
186 Distinct,
187 Except,
188 All,
189}
190
191impl<T> From<T> for SelectExpr
192where
193 T: Into<Expr>,
194{
195 fn from(expr: T) -> Self {
196 SelectExpr {
197 expr: expr.into(),
198 alias: None,
199 window: None,
200 }
201 }
202}
203
204impl SelectStatement {
205 /// Construct a new [`SelectStatement`]
206 pub fn new() -> Self {
207 Self::default()
208 }
209
210 /// Take the ownership of data in the current [`SelectStatement`]
211 pub fn take(&mut self) -> Self {
212 Self {
213 distinct: self.distinct.take(),
214 selects: std::mem::take(&mut self.selects),
215 from: std::mem::take(&mut self.from),
216 join: std::mem::take(&mut self.join),
217 r#where: std::mem::replace(&mut self.r#where, ConditionHolder::new()),
218 groups: std::mem::take(&mut self.groups),
219 having: std::mem::replace(&mut self.having, ConditionHolder::new()),
220 unions: std::mem::take(&mut self.unions),
221 orders: std::mem::take(&mut self.orders),
222 limit: self.limit.take(),
223 offset: self.offset.take(),
224 lock: self.lock.take(),
225 window: self.window.take(),
226 with: self.with.take(),
227 #[cfg(feature = "backend-postgres")]
228 table_sample: std::mem::take(&mut self.table_sample),
229 #[cfg(feature = "backend-mysql")]
230 index_hints: std::mem::take(&mut self.index_hints),
231 }
232 }
233
234 /// A shorthand to express if ... else ... when constructing the select statement.
235 ///
236 /// # Examples
237 ///
238 /// ```
239 /// use sea_query::{tests_cfg::*, *};
240 ///
241 /// let query = Query::select()
242 /// .column(Char::Character)
243 /// .from(Char::Table)
244 /// .conditions(
245 /// true,
246 /// |x| {
247 /// x.and_where(Expr::col(Char::FontId).eq(5));
248 /// },
249 /// |x| {
250 /// x.and_where(Expr::col(Char::FontId).eq(10));
251 /// },
252 /// )
253 /// .to_owned();
254 ///
255 /// assert_eq!(
256 /// query.to_string(MysqlQueryBuilder),
257 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
258 /// );
259 /// assert_eq!(
260 /// query.to_string(PostgresQueryBuilder),
261 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
262 /// );
263 /// assert_eq!(
264 /// query.to_string(SqliteQueryBuilder),
265 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
266 /// );
267 /// ```
268 pub fn conditions<T, F>(&mut self, b: bool, if_true: T, if_false: F) -> &mut Self
269 where
270 T: FnOnce(&mut Self),
271 F: FnOnce(&mut Self),
272 {
273 if b {
274 if_true(self)
275 } else {
276 if_false(self)
277 }
278 self
279 }
280
281 /// A shorthand to express if ... else ... when constructing the select statement.
282 ///
283 /// # Examples
284 ///
285 /// ```
286 /// use sea_query::{tests_cfg::*, *};
287 ///
288 /// let query = Query::select()
289 /// .column(Char::Character)
290 /// .from(Char::Table)
291 /// .apply_if(Some(5), |q, v| {
292 /// q.and_where(Expr::col(Char::FontId).eq(v));
293 /// })
294 /// .to_owned();
295 ///
296 /// assert_eq!(
297 /// query.to_string(MysqlQueryBuilder),
298 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
299 /// );
300 /// ```
301 pub fn apply_if<T, F>(&mut self, val: Option<T>, if_some: F) -> &mut Self
302 where
303 Self: Sized,
304 F: FnOnce(&mut Self, T),
305 {
306 if let Some(val) = val {
307 if_some(self, val);
308 }
309 self
310 }
311
312 /// Construct part of the select statement in another function.
313 ///
314 /// # Examples
315 ///
316 /// ```
317 /// use sea_query::{tests_cfg::*, *};
318 ///
319 /// let common_expr = |q: &mut SelectStatement| {
320 /// q.and_where(Expr::col(Char::FontId).eq(5));
321 /// };
322 ///
323 /// let query = Query::select()
324 /// .column(Char::Character)
325 /// .from(Char::Table)
326 /// .apply(common_expr)
327 /// .to_owned();
328 ///
329 /// assert_eq!(
330 /// query.to_string(MysqlQueryBuilder),
331 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
332 /// );
333 /// assert_eq!(
334 /// query.to_string(PostgresQueryBuilder),
335 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
336 /// );
337 /// assert_eq!(
338 /// query.to_string(SqliteQueryBuilder),
339 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
340 /// );
341 /// ```
342 pub fn apply<F>(&mut self, func: F) -> &mut Self
343 where
344 F: FnOnce(&mut Self),
345 {
346 func(self);
347 self
348 }
349
350 /// Clear the select list
351 pub fn clear_selects(&mut self) -> &mut Self {
352 self.selects = Vec::new();
353 self
354 }
355
356 /// Add an expression to the select expression list.
357 ///
358 /// # Examples
359 ///
360 /// ```
361 /// use sea_query::{tests_cfg::*, *};
362 ///
363 /// let query = Query::select()
364 /// .from(Char::Table)
365 /// .expr(Expr::val(42))
366 /// .expr(Expr::col(Char::Id).max())
367 /// .expr((1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)))
368 /// .to_owned();
369 ///
370 /// assert_eq!(
371 /// query.to_string(MysqlQueryBuilder),
372 /// r#"SELECT 42, MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
373 /// );
374 /// assert_eq!(
375 /// query.to_string(PostgresQueryBuilder),
376 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
377 /// );
378 /// assert_eq!(
379 /// query.to_string(SqliteQueryBuilder),
380 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
381 /// );
382 /// ```
383 pub fn expr<T>(&mut self, expr: T) -> &mut Self
384 where
385 T: Into<SelectExpr>,
386 {
387 self.selects.push(expr.into());
388 self
389 }
390
391 /// Add select expressions from vector of [`SelectExpr`].
392 ///
393 /// # Examples
394 ///
395 /// ```
396 /// use sea_query::{tests_cfg::*, *};
397 ///
398 /// let query = Query::select()
399 /// .from(Char::Table)
400 /// .exprs([
401 /// Expr::col(Char::Id).max(),
402 /// (1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)),
403 /// ])
404 /// .to_owned();
405 ///
406 /// assert_eq!(
407 /// query.to_string(MysqlQueryBuilder),
408 /// r#"SELECT MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
409 /// );
410 /// assert_eq!(
411 /// query.to_string(PostgresQueryBuilder),
412 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
413 /// );
414 /// assert_eq!(
415 /// query.to_string(SqliteQueryBuilder),
416 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
417 /// );
418 /// ```
419 pub fn exprs<T, I>(&mut self, exprs: I) -> &mut Self
420 where
421 T: Into<SelectExpr>,
422 I: IntoIterator<Item = T>,
423 {
424 self.selects
425 .append(&mut exprs.into_iter().map(|c| c.into()).collect());
426 self
427 }
428
429 pub fn exprs_mut_for_each<F>(&mut self, func: F)
430 where
431 F: FnMut(&mut SelectExpr),
432 {
433 self.selects.iter_mut().for_each(func);
434 }
435
436 /// Select distinct
437 pub fn distinct(&mut self) -> &mut Self {
438 self.distinct = Some(SelectDistinct::Distinct);
439 self
440 }
441
442 /// Select distinct on for *POSTGRES ONLY*
443 ///
444 /// # Examples
445 ///
446 /// ```
447 /// use sea_query::{tests_cfg::*, *};
448 ///
449 /// let query = Query::select()
450 /// .from(Char::Table)
451 /// .distinct_on([Char::Character])
452 /// .column(Char::Character)
453 /// .column(Char::SizeW)
454 /// .column(Char::SizeH)
455 /// .to_owned();
456 ///
457 /// assert_eq!(
458 /// query.to_string(PostgresQueryBuilder),
459 /// r#"SELECT DISTINCT ON ("character") "character", "size_w", "size_h" FROM "character""#
460 /// )
461 /// ```
462 ///
463 /// ```
464 /// use sea_query::{tests_cfg::*, *};
465 ///
466 /// let query = Query::select()
467 /// .from(Char::Table)
468 /// .distinct_on(vec![(Char::Table, Char::Character)])
469 /// .column(Char::Character)
470 /// .column(Char::SizeW)
471 /// .column(Char::SizeH)
472 /// .to_owned();
473 ///
474 /// assert_eq!(
475 /// query.to_string(PostgresQueryBuilder),
476 /// r#"SELECT DISTINCT ON ("character"."character") "character", "size_w", "size_h" FROM "character""#
477 /// )
478 /// ```
479 ///
480 /// ```
481 /// use sea_query::{tests_cfg::*, *};
482 ///
483 /// let distinct_cols: Vec<Character> = vec![];
484 /// let query = Query::select()
485 /// .from(Char::Table)
486 /// .distinct_on(distinct_cols)
487 /// .column(Char::Character)
488 /// .column(Char::SizeW)
489 /// .column(Char::SizeH)
490 /// .to_owned();
491 ///
492 /// assert_eq!(
493 /// query.to_string(PostgresQueryBuilder),
494 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
495 /// )
496 /// ```
497 pub fn distinct_on<T, I>(&mut self, cols: I) -> &mut Self
498 where
499 T: IntoColumnRef,
500 I: IntoIterator<Item = T>,
501 {
502 let cols = cols
503 .into_iter()
504 .map(|col| col.into_column_ref())
505 .collect::<Vec<ColumnRef>>();
506 self.distinct = if !cols.is_empty() {
507 Some(SelectDistinct::DistinctOn(cols))
508 } else {
509 None
510 };
511 self
512 }
513
514 /// Add a column to the select expression list.
515 ///
516 /// # Examples
517 ///
518 /// ```
519 /// use sea_query::{tests_cfg::*, *};
520 ///
521 /// let query = Query::select()
522 /// .from(Char::Table)
523 /// .column(Char::Character)
524 /// .column(Char::SizeW)
525 /// .column(Char::SizeH)
526 /// .to_owned();
527 ///
528 /// assert_eq!(
529 /// query.to_string(MysqlQueryBuilder),
530 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
531 /// );
532 /// assert_eq!(
533 /// query.to_string(PostgresQueryBuilder),
534 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
535 /// );
536 /// assert_eq!(
537 /// query.to_string(SqliteQueryBuilder),
538 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
539 /// );
540 /// ```
541 ///
542 /// ```
543 /// use sea_query::{tests_cfg::*, *};
544 ///
545 /// let query = Query::select()
546 /// .from(Char::Table)
547 /// .column((Char::Table, Char::Character))
548 /// .to_owned();
549 ///
550 /// assert_eq!(
551 /// query.to_string(MysqlQueryBuilder),
552 /// r#"SELECT `character`.`character` FROM `character`"#
553 /// );
554 /// assert_eq!(
555 /// query.to_string(PostgresQueryBuilder),
556 /// r#"SELECT "character"."character" FROM "character""#
557 /// );
558 /// assert_eq!(
559 /// query.to_string(SqliteQueryBuilder),
560 /// r#"SELECT "character"."character" FROM "character""#
561 /// );
562 /// ```
563 ///
564 /// ```
565 /// use sea_query::{tests_cfg::*, *};
566 ///
567 /// let query = Query::select()
568 /// .from(Char::Table)
569 /// .column(("schema", Char::Table, Char::Character))
570 /// .to_owned();
571 ///
572 /// assert_eq!(
573 /// query.to_string(MysqlQueryBuilder),
574 /// r#"SELECT `schema`.`character`.`character` FROM `character`"#
575 /// );
576 /// assert_eq!(
577 /// query.to_string(PostgresQueryBuilder),
578 /// r#"SELECT "schema"."character"."character" FROM "character""#
579 /// );
580 /// assert_eq!(
581 /// query.to_string(SqliteQueryBuilder),
582 /// r#"SELECT "schema"."character"."character" FROM "character""#
583 /// );
584 /// ```
585 pub fn column<C>(&mut self, col: C) -> &mut Self
586 where
587 C: IntoColumnRef,
588 {
589 self.expr(Expr::Column(col.into_column_ref()))
590 }
591
592 /// Select columns.
593 ///
594 /// # Examples
595 ///
596 /// ```
597 /// use sea_query::{tests_cfg::*, *};
598 ///
599 /// let query = Query::select()
600 /// .from(Char::Table)
601 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
602 /// .to_owned();
603 ///
604 /// assert_eq!(
605 /// query.to_string(MysqlQueryBuilder),
606 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
607 /// );
608 /// assert_eq!(
609 /// query.to_string(PostgresQueryBuilder),
610 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
611 /// );
612 /// assert_eq!(
613 /// query.to_string(SqliteQueryBuilder),
614 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
615 /// );
616 /// ```
617 ///
618 /// ```
619 /// use sea_query::{*, tests_cfg::*};
620 ///
621 /// let query = Query::select()
622 /// .from(Char::Table)
623 /// .columns([
624 /// (Char::Table, Char::Character),
625 /// (Char::Table, Char::SizeW),
626 /// (Char::Table, Char::SizeH),
627 /// ])
628 /// .to_owned();
629 ///
630 /// assert_eq!(
631 /// query.to_string(MysqlQueryBuilder),
632 /// r#"SELECT `character`.`character`, `character`.`size_w`, `character`.`size_h` FROM `character`"#
633 /// );
634 /// assert_eq!(
635 /// query.to_string(PostgresQueryBuilder),
636 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
637 /// );
638 /// assert_eq!(
639 /// query.to_string(SqliteQueryBuilder),
640 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
641 /// );
642 /// ```
643 pub fn columns<T, I>(&mut self, cols: I) -> &mut Self
644 where
645 T: IntoColumnRef,
646 I: IntoIterator<Item = T>,
647 {
648 self.exprs(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
649 }
650
651 /// Select column.
652 ///
653 /// # Examples
654 ///
655 /// ```
656 /// use sea_query::{tests_cfg::*, *};
657 ///
658 /// let query = Query::select()
659 /// .from(Char::Table)
660 /// .expr_as(Expr::col(Char::Character), "C")
661 /// .to_owned();
662 ///
663 /// assert_eq!(
664 /// query.to_string(MysqlQueryBuilder),
665 /// r#"SELECT `character` AS `C` FROM `character`"#
666 /// );
667 /// assert_eq!(
668 /// query.to_string(PostgresQueryBuilder),
669 /// r#"SELECT "character" AS "C" FROM "character""#
670 /// );
671 /// assert_eq!(
672 /// query.to_string(SqliteQueryBuilder),
673 /// r#"SELECT "character" AS "C" FROM "character""#
674 /// );
675 /// ```
676 pub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut Self
677 where
678 T: Into<Expr>,
679 A: IntoIden,
680 {
681 self.expr(SelectExpr {
682 expr: expr.into(),
683 alias: Some(alias.into_iden()),
684 window: None,
685 });
686 self
687 }
688
689 /// Select column with window function.
690 ///
691 /// # Examples
692 ///
693 /// ```
694 /// use sea_query::{tests_cfg::*, *};
695 ///
696 /// let query = Query::select()
697 /// .from(Char::Table)
698 /// .expr_window(
699 /// Expr::col(Char::Character),
700 /// WindowStatement::partition_by(Char::FontSize),
701 /// )
702 /// .to_owned();
703 ///
704 /// assert_eq!(
705 /// query.to_string(MysqlQueryBuilder),
706 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) FROM `character`"#
707 /// );
708 /// assert_eq!(
709 /// query.to_string(PostgresQueryBuilder),
710 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
711 /// );
712 /// assert_eq!(
713 /// query.to_string(SqliteQueryBuilder),
714 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
715 /// );
716 /// ```
717 pub fn expr_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self
718 where
719 T: Into<Expr>,
720 {
721 self.expr(SelectExpr {
722 expr: expr.into(),
723 alias: None,
724 window: Some(WindowSelectType::Query(window)),
725 });
726 self
727 }
728
729 /// Select column with window function and label.
730 ///
731 /// # Examples
732 ///
733 /// ```
734 /// use sea_query::{tests_cfg::*, *};
735 ///
736 /// let query = Query::select()
737 /// .from(Char::Table)
738 /// .expr_window_as(
739 /// Expr::col(Char::Character),
740 /// WindowStatement::partition_by(Char::FontSize),
741 /// "C",
742 /// )
743 /// .to_owned();
744 ///
745 /// assert_eq!(
746 /// query.to_string(MysqlQueryBuilder),
747 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
748 /// );
749 /// assert_eq!(
750 /// query.to_string(PostgresQueryBuilder),
751 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
752 /// );
753 /// assert_eq!(
754 /// query.to_string(SqliteQueryBuilder),
755 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
756 /// );
757 /// ```
758 pub fn expr_window_as<T, A>(&mut self, expr: T, window: WindowStatement, alias: A) -> &mut Self
759 where
760 T: Into<Expr>,
761 A: IntoIden,
762 {
763 self.expr(SelectExpr {
764 expr: expr.into(),
765 alias: Some(alias.into_iden()),
766 window: Some(WindowSelectType::Query(window)),
767 });
768 self
769 }
770
771 /// Select column with window name.
772 ///
773 /// # Examples
774 ///
775 /// ```
776 /// use sea_query::{tests_cfg::*, *};
777 ///
778 /// let query = Query::select()
779 /// .from(Char::Table)
780 /// .expr_window_name(Expr::col(Char::Character), "w")
781 /// .window("w", WindowStatement::partition_by(Char::FontSize))
782 /// .to_owned();
783 ///
784 /// assert_eq!(
785 /// query.to_string(MysqlQueryBuilder),
786 /// r#"SELECT `character` OVER `w` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
787 /// );
788 /// assert_eq!(
789 /// query.to_string(PostgresQueryBuilder),
790 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
791 /// );
792 /// assert_eq!(
793 /// query.to_string(SqliteQueryBuilder),
794 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
795 /// );
796 /// ```
797 pub fn expr_window_name<T, W>(&mut self, expr: T, window: W) -> &mut Self
798 where
799 T: Into<Expr>,
800 W: IntoIden,
801 {
802 self.expr(SelectExpr {
803 expr: expr.into(),
804 alias: None,
805 window: Some(WindowSelectType::Name(window.into_iden())),
806 });
807 self
808 }
809
810 /// Select column with window name and label.
811 ///
812 /// # Examples
813 ///
814 /// ```
815 /// use sea_query::{tests_cfg::*, *};
816 ///
817 /// let query = Query::select()
818 /// .from(Char::Table)
819 /// .expr_window_name_as(Expr::col(Char::Character), "w", "C")
820 /// .window("w", WindowStatement::partition_by(Char::FontSize))
821 /// .to_owned();
822 ///
823 /// assert_eq!(
824 /// query.to_string(MysqlQueryBuilder),
825 /// r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
826 /// );
827 /// assert_eq!(
828 /// query.to_string(PostgresQueryBuilder),
829 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
830 /// );
831 /// assert_eq!(
832 /// query.to_string(SqliteQueryBuilder),
833 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
834 /// );
835 /// ```
836 pub fn expr_window_name_as<T, W, A>(&mut self, expr: T, window: W, alias: A) -> &mut Self
837 where
838 T: Into<Expr>,
839 A: IntoIden,
840 W: IntoIden,
841 {
842 self.expr(SelectExpr {
843 expr: expr.into(),
844 alias: Some(alias.into_iden()),
845 window: Some(WindowSelectType::Name(window.into_iden())),
846 });
847 self
848 }
849
850 /// From table.
851 ///
852 /// # Examples
853 ///
854 /// ```
855 /// use sea_query::{tests_cfg::*, *};
856 ///
857 /// let query = Query::select()
858 /// .column(Char::FontSize)
859 /// .from(Char::Table)
860 /// .to_owned();
861 ///
862 /// assert_eq!(
863 /// query.to_string(MysqlQueryBuilder),
864 /// r#"SELECT `font_size` FROM `character`"#
865 /// );
866 /// assert_eq!(
867 /// query.to_string(PostgresQueryBuilder),
868 /// r#"SELECT "font_size" FROM "character""#
869 /// );
870 /// assert_eq!(
871 /// query.to_string(SqliteQueryBuilder),
872 /// r#"SELECT "font_size" FROM "character""#
873 /// );
874 /// ```
875 ///
876 /// ```
877 /// use sea_query::{tests_cfg::*, *};
878 ///
879 /// let query = Query::select()
880 /// .column(Char::FontSize)
881 /// .from((Char::Table, Glyph::Table))
882 /// .to_owned();
883 ///
884 /// assert_eq!(
885 /// query.to_string(MysqlQueryBuilder),
886 /// r#"SELECT `font_size` FROM `character`.`glyph`"#
887 /// );
888 /// assert_eq!(
889 /// query.to_string(PostgresQueryBuilder),
890 /// r#"SELECT "font_size" FROM "character"."glyph""#
891 /// );
892 /// assert_eq!(
893 /// query.to_string(SqliteQueryBuilder),
894 /// r#"SELECT "font_size" FROM "character"."glyph""#
895 /// );
896 /// ```
897 ///
898 /// ```
899 /// use sea_query::{tests_cfg::*, *};
900 ///
901 /// let query = Query::select()
902 /// .column(Char::FontSize)
903 /// .from(("database", Char::Table, Glyph::Table))
904 /// .to_owned();
905 ///
906 /// assert_eq!(
907 /// query.to_string(MysqlQueryBuilder),
908 /// r#"SELECT `font_size` FROM `database`.`character`.`glyph`"#
909 /// );
910 /// assert_eq!(
911 /// query.to_string(PostgresQueryBuilder),
912 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
913 /// );
914 /// assert_eq!(
915 /// query.to_string(SqliteQueryBuilder),
916 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
917 /// );
918 /// ```
919 ///
920 /// If you specify `from` multiple times, the resulting query will have multiple from clauses.
921 /// You can perform an 'old-school' join this way.
922 ///
923 /// ```
924 /// use sea_query::{tests_cfg::*, *};
925 ///
926 /// let query = Query::select()
927 /// .expr(Expr::asterisk())
928 /// .from(Char::Table)
929 /// .from(Font::Table)
930 /// .and_where(Expr::col((Font::Table, Font::Id)).equals((Char::Table, Char::FontId)))
931 /// .to_owned();
932 ///
933 /// assert_eq!(
934 /// query.to_string(MysqlQueryBuilder),
935 /// r#"SELECT * FROM `character`, `font` WHERE `font`.`id` = `character`.`font_id`"#
936 /// );
937 /// assert_eq!(
938 /// query.to_string(PostgresQueryBuilder),
939 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
940 /// );
941 /// assert_eq!(
942 /// query.to_string(SqliteQueryBuilder),
943 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
944 /// );
945 /// ```
946 pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self
947 where
948 R: IntoTableRef,
949 {
950 self.from_from(tbl_ref.into_table_ref())
951 }
952
953 /// Shorthand for selecting from a constant value list.
954 ///
955 /// # Panics
956 ///
957 /// Panics on an empty values list.
958 ///
959 /// ```
960 /// use sea_query::{tests_cfg::*, *};
961 ///
962 /// let query = Query::select()
963 /// .expr(Expr::asterisk())
964 /// .from_values([(1, "hello"), (2, "world")], "x")
965 /// .to_owned();
966 ///
967 /// assert_eq!(
968 /// query.to_string(MysqlQueryBuilder),
969 /// r#"SELECT * FROM (VALUES ROW(1, 'hello'), ROW(2, 'world')) AS `x`"#
970 /// );
971 /// assert_eq!(
972 /// query.to_string(PostgresQueryBuilder),
973 /// r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
974 /// );
975 /// assert_eq!(
976 /// query.to_string(SqliteQueryBuilder),
977 /// r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
978 /// );
979 /// ```
980 pub fn from_values<I, V, A>(&mut self, value_tuples: I, alias: A) -> &mut Self
981 where
982 I: IntoIterator<Item = V>,
983 V: IntoValueTuple,
984 A: IntoIden,
985 {
986 let value_tuples: Vec<ValueTuple> = value_tuples
987 .into_iter()
988 .map(|vt| vt.into_value_tuple())
989 .collect();
990 assert!(!value_tuples.is_empty());
991 self.from_from(TableRef::ValuesList(value_tuples, alias.into_iden()))
992 }
993
994 /// From table with alias.
995 ///
996 /// # Examples
997 ///
998 /// ```
999 /// use sea_query::{tests_cfg::*, *};
1000 ///
1001 /// let query = Query::select()
1002 /// .from_as(Char::Table, "char")
1003 /// .column(("char", Char::Character))
1004 /// .to_owned();
1005 ///
1006 /// assert_eq!(
1007 /// query.to_string(MysqlQueryBuilder),
1008 /// r#"SELECT `char`.`character` FROM `character` AS `char`"#
1009 /// );
1010 /// assert_eq!(
1011 /// query.to_string(PostgresQueryBuilder),
1012 /// r#"SELECT "char"."character" FROM "character" AS "char""#
1013 /// );
1014 /// assert_eq!(
1015 /// query.to_string(SqliteQueryBuilder),
1016 /// r#"SELECT "char"."character" FROM "character" AS "char""#
1017 /// );
1018 /// ```
1019 ///
1020 /// ```
1021 /// use sea_query::{audit::*, tests_cfg::*, *};
1022 ///
1023 /// let query = Query::select()
1024 /// .from_as((Font::Table, Char::Table), "alias")
1025 /// .column(("alias", Char::Character))
1026 /// .to_owned();
1027 ///
1028 /// assert_eq!(
1029 /// query.to_string(MysqlQueryBuilder),
1030 /// r#"SELECT `alias`.`character` FROM `font`.`character` AS `alias`"#
1031 /// );
1032 /// assert_eq!(
1033 /// query.to_string(PostgresQueryBuilder),
1034 /// r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
1035 /// );
1036 /// assert_eq!(
1037 /// query.to_string(SqliteQueryBuilder),
1038 /// r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
1039 /// );
1040 /// assert_eq!(
1041 /// query.audit().unwrap().selects(),
1042 /// [TableName(Some(Font::Table.into()), Char::Table.into_iden())]
1043 /// );
1044 /// ```
1045 pub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut Self
1046 where
1047 R: IntoTableRef,
1048 A: IntoIden,
1049 {
1050 self.from_from(tbl_ref.into_table_ref().alias(alias.into_iden()))
1051 }
1052
1053 /// From sub-query.
1054 ///
1055 /// # Examples
1056 ///
1057 /// ```
1058 /// use sea_query::{audit::*, tests_cfg::*, *};
1059 ///
1060 /// let query = Query::select()
1061 /// .columns([Glyph::Image])
1062 /// .from_subquery(
1063 /// Query::select()
1064 /// .columns([Glyph::Image, Glyph::Aspect])
1065 /// .from(Glyph::Table)
1066 /// .take(),
1067 /// "subglyph",
1068 /// )
1069 /// .to_owned();
1070 ///
1071 /// assert_eq!(
1072 /// query.to_string(MysqlQueryBuilder),
1073 /// r#"SELECT `image` FROM (SELECT `image`, `aspect` FROM `glyph`) AS `subglyph`"#
1074 /// );
1075 /// assert_eq!(
1076 /// query.to_string(PostgresQueryBuilder),
1077 /// r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
1078 /// );
1079 /// assert_eq!(
1080 /// query.to_string(SqliteQueryBuilder),
1081 /// r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
1082 /// );
1083 /// assert_eq!(
1084 /// query.audit().unwrap().selected_tables(),
1085 /// [Glyph::Table.into_iden()]
1086 /// );
1087 /// ```
1088 pub fn from_subquery<T>(&mut self, query: SelectStatement, alias: T) -> &mut Self
1089 where
1090 T: IntoIden,
1091 {
1092 self.from_from(TableRef::SubQuery(query.into(), alias.into_iden()))
1093 }
1094
1095 /// From function call.
1096 ///
1097 /// # Examples
1098 ///
1099 /// ```
1100 /// use sea_query::{tests_cfg::*, *};
1101 ///
1102 /// let query = Query::select()
1103 /// .column(Asterisk)
1104 /// .from_function(Func::random(), "func")
1105 /// .to_owned();
1106 ///
1107 /// assert_eq!(
1108 /// query.to_string(MysqlQueryBuilder),
1109 /// r#"SELECT * FROM RAND() AS `func`"#
1110 /// );
1111 /// assert_eq!(
1112 /// query.to_string(PostgresQueryBuilder),
1113 /// r#"SELECT * FROM RANDOM() AS "func""#
1114 /// );
1115 /// assert_eq!(
1116 /// query.to_string(SqliteQueryBuilder),
1117 /// r#"SELECT * FROM RANDOM() AS "func""#
1118 /// );
1119 /// ```
1120 pub fn from_function<T>(&mut self, func: FunctionCall, alias: T) -> &mut Self
1121 where
1122 T: IntoIden,
1123 {
1124 self.from_from(TableRef::FunctionCall(func, alias.into_iden()))
1125 }
1126
1127 /// Clears all current from clauses.
1128 ///
1129 /// # Examples
1130 ///
1131 /// ```
1132 /// use sea_query::{tests_cfg::*, *};
1133 ///
1134 /// let query = Query::select()
1135 /// .column(Asterisk)
1136 /// .from(Char::Table)
1137 /// .from_clear()
1138 /// .from(Font::Table)
1139 /// .to_owned();
1140 ///
1141 /// assert_eq!(
1142 /// query.to_string(MysqlQueryBuilder),
1143 /// r#"SELECT * FROM `font`"#
1144 /// );
1145 /// assert_eq!(
1146 /// query.to_string(PostgresQueryBuilder),
1147 /// r#"SELECT * FROM "font""#
1148 /// );
1149 /// assert_eq!(
1150 /// query.to_string(SqliteQueryBuilder),
1151 /// r#"SELECT * FROM "font""#
1152 /// );
1153 /// ```
1154 pub fn from_clear(&mut self) -> &mut Self {
1155 self.from.clear();
1156 self
1157 }
1158
1159 #[allow(clippy::wrong_self_convention)]
1160 fn from_from(&mut self, select: TableRef) -> &mut Self {
1161 self.from.push(select);
1162 self
1163 }
1164
1165 /// Cross join.
1166 ///
1167 /// # Examples
1168 ///
1169 /// ```
1170 /// use sea_query::{audit::*, tests_cfg::*, *};
1171 ///
1172 /// let query = Query::select()
1173 /// .column(Char::Character)
1174 /// .column((Font::Table, Font::Name))
1175 /// .from(Char::Table)
1176 /// .cross_join(Font::Table)
1177 /// .to_owned();
1178 ///
1179 /// assert_eq!(
1180 /// query.to_string(MysqlQueryBuilder),
1181 /// r#"SELECT `character`, `font`.`name` FROM `character` CROSS JOIN `font`"#
1182 /// );
1183 /// assert_eq!(
1184 /// query.to_string(PostgresQueryBuilder),
1185 /// r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
1186 /// );
1187 /// assert_eq!(
1188 /// query.to_string(SqliteQueryBuilder),
1189 /// r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
1190 /// );
1191 /// assert_eq!(
1192 /// query.audit().unwrap().selected_tables(),
1193 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1194 /// );
1195 /// ```
1196 pub fn cross_join<R>(&mut self, tbl_ref: R) -> &mut Self
1197 where
1198 R: IntoTableRef,
1199 {
1200 self.push_join(JoinType::CrossJoin, tbl_ref.into_table_ref(), None, false);
1201 self
1202 }
1203
1204 /// Left join.
1205 ///
1206 /// # Examples
1207 ///
1208 /// ```
1209 /// use sea_query::{audit::*, tests_cfg::*, *};
1210 ///
1211 /// let query = Query::select()
1212 /// .column(Char::Character)
1213 /// .column((Font::Table, Font::Name))
1214 /// .from(Char::Table)
1215 /// .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1216 /// .to_owned();
1217 ///
1218 /// assert_eq!(
1219 /// query.to_string(MysqlQueryBuilder),
1220 /// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1221 /// );
1222 /// assert_eq!(
1223 /// query.to_string(PostgresQueryBuilder),
1224 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
1225 /// );
1226 /// assert_eq!(
1227 /// query.to_string(SqliteQueryBuilder),
1228 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
1229 /// );
1230 /// assert_eq!(
1231 /// query.audit().unwrap().selected_tables(),
1232 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1233 /// );
1234 ///
1235 /// // Constructing chained join conditions
1236 /// let query = Query::select()
1237 /// .column(Char::Character)
1238 /// .column((Font::Table, Font::Name))
1239 /// .from(Char::Table)
1240 /// .left_join(
1241 /// Font::Table,
1242 /// all![
1243 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1244 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1245 /// ]
1246 /// )
1247 /// .to_owned();
1248 ///
1249 /// assert_eq!(
1250 /// query.to_string(MysqlQueryBuilder),
1251 /// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1252 /// );
1253 /// assert_eq!(
1254 /// query.to_string(PostgresQueryBuilder),
1255 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1256 /// );
1257 /// assert_eq!(
1258 /// query.to_string(SqliteQueryBuilder),
1259 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1260 /// );
1261 /// ```
1262 pub fn left_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1263 where
1264 R: IntoTableRef,
1265 C: IntoCondition,
1266 {
1267 self.join(JoinType::LeftJoin, tbl_ref, condition)
1268 }
1269
1270 /// Right join.
1271 ///
1272 /// # Examples
1273 ///
1274 /// ```
1275 /// use sea_query::{*, tests_cfg::*};
1276 ///
1277 /// let query = Query::select()
1278 /// .column(Char::Character)
1279 /// .column((Font::Table, Font::Name))
1280 /// .from(Char::Table)
1281 /// .right_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1282 /// .to_owned();
1283 ///
1284 /// assert_eq!(
1285 /// query.to_string(MysqlQueryBuilder),
1286 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1287 /// );
1288 /// assert_eq!(
1289 /// query.to_string(PostgresQueryBuilder),
1290 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1291 /// );
1292 /// assert_eq!(
1293 /// query.to_string(SqliteQueryBuilder),
1294 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1295 /// );
1296 ///
1297 /// // Constructing chained join conditions
1298 /// let query = Query::select()
1299 /// .column(Char::Character)
1300 /// .column((Font::Table, Font::Name))
1301 /// .from(Char::Table)
1302 /// .right_join(
1303 /// Font::Table,
1304 /// all![
1305 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1306 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1307 /// ]
1308 /// )
1309 /// .to_owned();
1310 ///
1311 /// assert_eq!(
1312 /// query.to_string(MysqlQueryBuilder),
1313 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1314 /// );
1315 /// assert_eq!(
1316 /// query.to_string(PostgresQueryBuilder),
1317 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1318 /// );
1319 /// assert_eq!(
1320 /// query.to_string(SqliteQueryBuilder),
1321 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1322 /// );
1323 /// ```
1324 pub fn right_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1325 where
1326 R: IntoTableRef,
1327 C: IntoCondition,
1328 {
1329 self.join(JoinType::RightJoin, tbl_ref, condition)
1330 }
1331
1332 /// Inner join.
1333 ///
1334 /// # Examples
1335 ///
1336 /// ```
1337 /// use sea_query::{*, tests_cfg::*};
1338 ///
1339 /// let query = Query::select()
1340 /// .column(Char::Character)
1341 /// .column((Font::Table, Font::Name))
1342 /// .from(Char::Table)
1343 /// .inner_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1344 /// .to_owned();
1345 ///
1346 /// assert_eq!(
1347 /// query.to_string(MysqlQueryBuilder),
1348 /// r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1349 /// );
1350 /// assert_eq!(
1351 /// query.to_string(PostgresQueryBuilder),
1352 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
1353 /// );
1354 /// assert_eq!(
1355 /// query.to_string(SqliteQueryBuilder),
1356 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
1357 /// );
1358 ///
1359 /// // Constructing chained join conditions
1360 /// let query = Query::select()
1361 /// .column(Char::Character)
1362 /// .column((Font::Table, Font::Name))
1363 /// .from(Char::Table)
1364 /// .inner_join(
1365 /// Font::Table,
1366 /// all![
1367 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1368 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1369 /// ]
1370 /// )
1371 /// .to_owned();
1372 ///
1373 /// assert_eq!(
1374 /// query.to_string(MysqlQueryBuilder),
1375 /// r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1376 /// );
1377 /// assert_eq!(
1378 /// query.to_string(PostgresQueryBuilder),
1379 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1380 /// );
1381 /// assert_eq!(
1382 /// query.to_string(SqliteQueryBuilder),
1383 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1384 /// );
1385 /// ```
1386 pub fn inner_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1387 where
1388 R: IntoTableRef,
1389 C: IntoCondition,
1390 {
1391 self.join(JoinType::InnerJoin, tbl_ref, condition)
1392 }
1393
1394 /// Full outer join.
1395 ///
1396 /// # Examples
1397 ///
1398 /// ```
1399 /// use sea_query::{*, tests_cfg::*};
1400 ///
1401 /// let query = Query::select()
1402 /// .column(Char::Character)
1403 /// .column((Font::Table, Font::Name))
1404 /// .from(Char::Table)
1405 /// .full_outer_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1406 /// .to_owned();
1407 ///
1408 /// assert_eq!(
1409 /// query.to_string(PostgresQueryBuilder),
1410 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
1411 /// );
1412 /// assert_eq!(
1413 /// query.to_string(SqliteQueryBuilder),
1414 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
1415 /// );
1416 ///
1417 /// // Constructing chained join conditions
1418 /// let query = Query::select()
1419 /// .column(Char::Character)
1420 /// .column((Font::Table, Font::Name))
1421 /// .from(Char::Table)
1422 /// .full_outer_join(
1423 /// Font::Table,
1424 /// all![
1425 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1426 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1427 /// ]
1428 /// )
1429 /// .to_owned();
1430 ///
1431 /// assert_eq!(
1432 /// query.to_string(PostgresQueryBuilder),
1433 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1434 /// );
1435 /// assert_eq!(
1436 /// query.to_string(SqliteQueryBuilder),
1437 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1438 /// );
1439 /// ```
1440 pub fn full_outer_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1441 where
1442 R: IntoTableRef,
1443 C: IntoCondition,
1444 {
1445 self.join(JoinType::FullOuterJoin, tbl_ref, condition)
1446 }
1447
1448 /// Straight join. MySQL only.
1449 ///
1450 /// # Examples
1451 ///
1452 /// ```
1453 /// use sea_query::{*, tests_cfg::*};
1454 ///
1455 /// let query = Query::select()
1456 /// .column(Char::Character)
1457 /// .column((Font::Table, Font::Name))
1458 /// .from(Char::Table)
1459 /// .straight_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1460 /// .to_owned();
1461 ///
1462 /// assert_eq!(
1463 /// query.to_string(MysqlQueryBuilder),
1464 /// r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1465 /// );
1466 ///
1467 /// // Constructing chained join conditions
1468 /// let query = Query::select()
1469 /// .column(Char::Character)
1470 /// .column((Font::Table, Font::Name))
1471 /// .from(Char::Table)
1472 /// .straight_join(
1473 /// Font::Table,
1474 /// all![
1475 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1476 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1477 /// ]
1478 /// )
1479 /// .to_owned();
1480 ///
1481 /// assert_eq!(
1482 /// query.to_string(MysqlQueryBuilder),
1483 /// r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1484 /// );
1485 /// ```
1486 pub fn straight_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1487 where
1488 R: IntoTableRef,
1489 C: IntoCondition,
1490 {
1491 self.join(JoinType::StraightJoin, tbl_ref, condition)
1492 }
1493
1494 /// Join with other table by [`JoinType`].
1495 ///
1496 /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
1497 ///
1498 /// # Examples
1499 ///
1500 /// ```
1501 /// use sea_query::{audit::*, tests_cfg::*, *};
1502 ///
1503 /// let query = Query::select()
1504 /// .column(Char::Character)
1505 /// .column((Font::Table, Font::Name))
1506 /// .from(Char::Table)
1507 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1508 /// .to_owned();
1509 ///
1510 /// assert_eq!(
1511 /// query.to_string(MysqlQueryBuilder),
1512 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1513 /// );
1514 /// assert_eq!(
1515 /// query.to_string(PostgresQueryBuilder),
1516 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1517 /// );
1518 /// assert_eq!(
1519 /// query.to_string(SqliteQueryBuilder),
1520 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1521 /// );
1522 /// assert_eq!(
1523 /// query.audit().unwrap().selected_tables(),
1524 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1525 /// );
1526 ///
1527 /// // Constructing chained join conditions
1528 /// let query = Query::select()
1529 /// .column(Char::Character)
1530 /// .column((Font::Table, Font::Name))
1531 /// .from(Char::Table)
1532 /// .join(
1533 /// JoinType::RightJoin,
1534 /// Font::Table,
1535 /// all![
1536 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1537 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1538 /// ]
1539 /// )
1540 /// .to_owned();
1541 ///
1542 /// assert_eq!(
1543 /// query.to_string(MysqlQueryBuilder),
1544 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1545 /// );
1546 /// assert_eq!(
1547 /// query.to_string(PostgresQueryBuilder),
1548 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1549 /// );
1550 /// assert_eq!(
1551 /// query.to_string(SqliteQueryBuilder),
1552 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1553 /// );
1554 /// assert_eq!(
1555 /// query.audit().unwrap().selected_tables(),
1556 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1557 /// );
1558 /// ```
1559 pub fn join<R, C>(&mut self, join: JoinType, tbl_ref: R, condition: C) -> &mut Self
1560 where
1561 R: IntoTableRef,
1562 C: IntoCondition,
1563 {
1564 let on = match join {
1565 JoinType::CrossJoin => None,
1566 _ => Some(JoinOn::Condition(Box::new(
1567 ConditionHolder::new_with_condition(condition.into_condition()),
1568 ))),
1569 };
1570 self.push_join(join, tbl_ref.into_table_ref(), on, false)
1571 }
1572
1573 /// Join with other table by [`JoinType`], assigning an alias to the joined table.
1574 ///
1575 /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
1576 ///
1577 /// # Examples
1578 ///
1579 /// ```
1580 /// use sea_query::{*, tests_cfg::*};
1581 ///
1582 /// let query = Query::select()
1583 /// .column(Char::Character)
1584 /// .column(("f", Font::Name))
1585 /// .from(Char::Table)
1586 /// .join_as(
1587 /// JoinType::RightJoin,
1588 /// Font::Table,
1589 /// "f",
1590 /// Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id))
1591 /// )
1592 /// .to_owned();
1593 ///
1594 /// assert_eq!(
1595 /// query.to_string(MysqlQueryBuilder),
1596 /// r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id`"#
1597 /// );
1598 /// assert_eq!(
1599 /// query.to_string(PostgresQueryBuilder),
1600 /// r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
1601 /// );
1602 /// assert_eq!(
1603 /// query.to_string(SqliteQueryBuilder),
1604 /// r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
1605 /// );
1606 ///
1607 /// // Constructing chained join conditions
1608 /// assert_eq!(
1609 /// Query::select()
1610 /// .column(Char::Character)
1611 /// .column(("f", Font::Name))
1612 /// .from(Char::Table)
1613 /// .join_as(
1614 /// JoinType::RightJoin,
1615 /// Font::Table,
1616 /// "f",
1617 /// Condition::all()
1618 /// .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
1619 /// .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
1620 /// )
1621 /// .to_string(MysqlQueryBuilder),
1622 /// r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id` AND `character`.`font_id` = `f`.`id`"#
1623 /// );
1624 /// ```
1625 pub fn join_as<R, A, C>(
1626 &mut self,
1627 join: JoinType,
1628 tbl_ref: R,
1629 alias: A,
1630 condition: C,
1631 ) -> &mut Self
1632 where
1633 R: IntoTableRef,
1634 A: IntoIden,
1635 C: IntoCondition,
1636 {
1637 let on = match join {
1638 JoinType::CrossJoin => None,
1639 _ => Some(JoinOn::Condition(Box::new(
1640 ConditionHolder::new_with_condition(condition.into_condition()),
1641 ))),
1642 };
1643 self.push_join(
1644 join,
1645 tbl_ref.into_table_ref().alias(alias.into_iden()),
1646 on,
1647 false,
1648 )
1649 }
1650
1651 /// Join with sub-query.
1652 ///
1653 /// # Examples
1654 ///
1655 /// ```
1656 /// use sea_query::{tests_cfg::*, audit::*, *};
1657 ///
1658 /// let query = Query::select()
1659 /// .column(Font::Name)
1660 /// .from(Font::Table)
1661 /// .join_subquery(
1662 /// JoinType::LeftJoin,
1663 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1664 /// "sub_glyph",
1665 /// Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
1666 /// )
1667 /// .to_owned();
1668 ///
1669 /// assert_eq!(
1670 /// query.to_string(MysqlQueryBuilder),
1671 /// r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
1672 /// );
1673 /// assert_eq!(
1674 /// query.to_string(PostgresQueryBuilder),
1675 /// r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1676 /// );
1677 /// assert_eq!(
1678 /// query.to_string(SqliteQueryBuilder),
1679 /// r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1680 /// );
1681 /// assert_eq!(
1682 /// query.audit().unwrap().selected_tables(),
1683 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1684 /// );
1685 ///
1686 /// // Constructing chained join conditions
1687 /// assert_eq!(
1688 /// Query::select()
1689 /// .column(Font::Name)
1690 /// .from(Font::Table)
1691 /// .join_subquery(
1692 /// JoinType::LeftJoin,
1693 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1694 /// "sub_glyph",
1695 /// Condition::all()
1696 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1697 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1698 /// )
1699 /// .to_string(MysqlQueryBuilder),
1700 /// r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
1701 /// );
1702 /// assert_eq!(
1703 /// query.audit().unwrap().selected_tables(),
1704 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1705 /// );
1706 /// ```
1707 pub fn join_subquery<T, C>(
1708 &mut self,
1709 join: JoinType,
1710 query: SelectStatement,
1711 alias: T,
1712 condition: C,
1713 ) -> &mut Self
1714 where
1715 T: IntoIden,
1716 C: IntoCondition,
1717 {
1718 self.push_join(
1719 join,
1720 TableRef::SubQuery(query.into(), alias.into_iden()),
1721 JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
1722 condition.into_condition(),
1723 ))),
1724 false,
1725 )
1726 }
1727
1728 /// Join Lateral with sub-query. Not supported by SQLite.
1729 ///
1730 /// # Examples
1731 ///
1732 /// ```
1733 /// use sea_query::{audit::*, tests_cfg::*, *};
1734 ///
1735 /// let query = Query::select()
1736 /// .column(Font::Name)
1737 /// .from(Font::Table)
1738 /// .join_lateral(
1739 /// JoinType::LeftJoin,
1740 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1741 /// "sub_glyph",
1742 /// Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
1743 /// )
1744 /// .to_owned();
1745 ///
1746 /// assert_eq!(
1747 /// query.to_string(MysqlQueryBuilder),
1748 /// r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
1749 /// );
1750 /// assert_eq!(
1751 /// query.to_string(PostgresQueryBuilder),
1752 /// r#"SELECT "name" FROM "font" LEFT JOIN LATERAL (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1753 /// );
1754 /// assert_eq!(
1755 /// query.audit().unwrap().selected_tables(),
1756 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1757 /// );
1758 ///
1759 /// // Constructing chained join conditions
1760 /// assert_eq!(
1761 /// Query::select()
1762 /// .column(Font::Name)
1763 /// .from(Font::Table)
1764 /// .join_lateral(
1765 /// JoinType::LeftJoin,
1766 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1767 /// "sub_glyph",
1768 /// Condition::all()
1769 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1770 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1771 /// )
1772 /// .to_string(MysqlQueryBuilder),
1773 /// r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
1774 /// );
1775 /// ```
1776 pub fn join_lateral<T, C>(
1777 &mut self,
1778 join: JoinType,
1779 query: SelectStatement,
1780 alias: T,
1781 condition: C,
1782 ) -> &mut Self
1783 where
1784 T: IntoIden,
1785 C: IntoCondition,
1786 {
1787 self.push_join(
1788 join,
1789 TableRef::SubQuery(query.into(), alias.into_iden()),
1790 JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
1791 condition.into_condition(),
1792 ))),
1793 true,
1794 )
1795 }
1796
1797 fn push_join(
1798 &mut self,
1799 join: JoinType,
1800 table: TableRef,
1801 on: impl Into<Option<JoinOn>>,
1802 lateral: bool,
1803 ) -> &mut Self {
1804 self.join.push(JoinExpr {
1805 join,
1806 table: Box::new(table),
1807 on: on.into(),
1808 lateral,
1809 });
1810 self
1811 }
1812
1813 /// Group by columns.
1814 ///
1815 /// # Examples
1816 ///
1817 /// ```
1818 /// use sea_query::{*, tests_cfg::*};
1819 ///
1820 /// let query = Query::select()
1821 /// .column(Char::Character)
1822 /// .column((Font::Table, Font::Name))
1823 /// .from(Char::Table)
1824 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1825 /// .group_by_columns([
1826 /// Char::Character,
1827 /// ])
1828 /// .to_owned();
1829 ///
1830 /// assert_eq!(
1831 /// query.to_string(MysqlQueryBuilder),
1832 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`"#
1833 /// );
1834 /// assert_eq!(
1835 /// query.to_string(PostgresQueryBuilder),
1836 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
1837 /// );
1838 /// assert_eq!(
1839 /// query.to_string(SqliteQueryBuilder),
1840 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
1841 /// );
1842 /// ```
1843 ///
1844 /// ```
1845 /// use sea_query::{*, tests_cfg::*};
1846 ///
1847 /// let query = Query::select()
1848 /// .column(Char::Character)
1849 /// .column((Font::Table, Font::Name))
1850 /// .from(Char::Table)
1851 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1852 /// .group_by_columns([
1853 /// (Char::Table, Char::Character),
1854 /// ])
1855 /// .to_owned();
1856 ///
1857 /// assert_eq!(
1858 /// query.to_string(MysqlQueryBuilder),
1859 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
1860 /// );
1861 /// assert_eq!(
1862 /// query.to_string(PostgresQueryBuilder),
1863 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1864 /// );
1865 /// assert_eq!(
1866 /// query.to_string(SqliteQueryBuilder),
1867 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1868 /// );
1869 /// ```
1870 pub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut Self
1871 where
1872 T: IntoColumnRef,
1873 I: IntoIterator<Item = T>,
1874 {
1875 self.add_group_by(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
1876 }
1877
1878 /// Add a group by column.
1879 ///
1880 /// ```
1881 /// use sea_query::{*, tests_cfg::*};
1882 ///
1883 /// let query = Query::select()
1884 /// .column(Char::Character)
1885 /// .column((Font::Table, Font::Name))
1886 /// .from(Char::Table)
1887 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1888 /// .group_by_col((Char::Table, Char::Character))
1889 /// .to_owned();
1890 ///
1891 /// assert_eq!(
1892 /// query.to_string(MysqlQueryBuilder),
1893 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
1894 /// );
1895 /// assert_eq!(
1896 /// query.to_string(PostgresQueryBuilder),
1897 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1898 /// );
1899 /// assert_eq!(
1900 /// query.to_string(SqliteQueryBuilder),
1901 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1902 /// );
1903 /// ```
1904 pub fn group_by_col<T>(&mut self, col: T) -> &mut Self
1905 where
1906 T: IntoColumnRef,
1907 {
1908 self.group_by_columns([col])
1909 }
1910
1911 /// Add group by expressions from vector of [`SelectExpr`].
1912 ///
1913 /// # Examples
1914 ///
1915 /// ```
1916 /// use sea_query::{tests_cfg::*, *};
1917 ///
1918 /// let query = Query::select()
1919 /// .from(Char::Table)
1920 /// .column(Char::Character)
1921 /// .add_group_by([Expr::col(Char::SizeW).into(), Expr::col(Char::SizeH).into()])
1922 /// .to_owned();
1923 ///
1924 /// assert_eq!(
1925 /// query.to_string(MysqlQueryBuilder),
1926 /// r#"SELECT `character` FROM `character` GROUP BY `size_w`, `size_h`"#
1927 /// );
1928 /// assert_eq!(
1929 /// query.to_string(PostgresQueryBuilder),
1930 /// r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
1931 /// );
1932 /// assert_eq!(
1933 /// query.to_string(SqliteQueryBuilder),
1934 /// r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
1935 /// );
1936 /// ```
1937 pub fn add_group_by<I>(&mut self, expr: I) -> &mut Self
1938 where
1939 I: IntoIterator<Item = Expr>,
1940 {
1941 self.groups.append(&mut expr.into_iter().collect());
1942 self
1943 }
1944
1945 /// Having condition, expressed with [`any!`](crate::any) and [`all!`](crate::all).
1946 ///
1947 /// # Examples
1948 ///
1949 /// ```
1950 /// use sea_query::{*, tests_cfg::*};
1951 ///
1952 /// let query = Query::select()
1953 /// .column(Glyph::Aspect)
1954 /// .expr(Expr::col(Glyph::Image).max())
1955 /// .from(Glyph::Table)
1956 /// .group_by_columns([
1957 /// Glyph::Aspect,
1958 /// ])
1959 /// .cond_having(
1960 /// all![
1961 /// Expr::col((Glyph::Table, Glyph::Aspect)).is_in([3, 4]),
1962 /// any![
1963 /// Expr::col((Glyph::Table, Glyph::Image)).like("A%"),
1964 /// Expr::col((Glyph::Table, Glyph::Image)).like("B%")
1965 /// ]
1966 /// ]
1967 /// )
1968 /// .to_owned();
1969 ///
1970 /// assert_eq!(
1971 /// query.to_string(MysqlQueryBuilder),
1972 /// r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `glyph`.`aspect` IN (3, 4) AND (`glyph`.`image` LIKE 'A%' OR `glyph`.`image` LIKE 'B%')"#
1973 /// );
1974 /// ```
1975 pub fn cond_having<C>(&mut self, condition: C) -> &mut Self
1976 where
1977 C: IntoCondition,
1978 {
1979 self.having.add_condition(condition.into_condition());
1980 self
1981 }
1982
1983 /// And having condition.
1984 ///
1985 /// # Examples
1986 ///
1987 /// ```
1988 /// use sea_query::{*, tests_cfg::*};
1989 ///
1990 /// let query = Query::select()
1991 /// .column(Glyph::Aspect)
1992 /// .expr(Expr::col(Glyph::Image).max())
1993 /// .from(Glyph::Table)
1994 /// .group_by_columns([
1995 /// Glyph::Aspect,
1996 /// ])
1997 /// .and_having(Expr::col(Glyph::Aspect).gt(2))
1998 /// .cond_having(Expr::col(Glyph::Aspect).lt(8))
1999 /// .to_owned();
2000 ///
2001 /// assert_eq!(
2002 /// query.to_string(MysqlQueryBuilder),
2003 /// r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2 AND `aspect` < 8"#
2004 /// );
2005 /// assert_eq!(
2006 /// query.to_string(PostgresQueryBuilder),
2007 /// r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
2008 /// );
2009 /// assert_eq!(
2010 /// query.to_string(SqliteQueryBuilder),
2011 /// r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
2012 /// );
2013 /// ```
2014 pub fn and_having(&mut self, other: Expr) -> &mut Self {
2015 self.cond_having(other)
2016 }
2017
2018 /// Limit the number of returned rows.
2019 ///
2020 /// # Examples
2021 ///
2022 /// ```
2023 /// use sea_query::{tests_cfg::*, *};
2024 ///
2025 /// let query = Query::select()
2026 /// .column(Glyph::Aspect)
2027 /// .from(Glyph::Table)
2028 /// .limit(10)
2029 /// .to_owned();
2030 ///
2031 /// assert_eq!(
2032 /// query.to_string(MysqlQueryBuilder),
2033 /// r#"SELECT `aspect` FROM `glyph` LIMIT 10"#
2034 /// );
2035 /// assert_eq!(
2036 /// query.to_string(PostgresQueryBuilder),
2037 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
2038 /// );
2039 /// assert_eq!(
2040 /// query.to_string(SqliteQueryBuilder),
2041 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
2042 /// );
2043 /// ```
2044 pub fn limit(&mut self, limit: u64) -> &mut Self {
2045 self.limit = Some(limit.into());
2046 self
2047 }
2048
2049 /// Reset limit
2050 pub fn reset_limit(&mut self) -> &mut Self {
2051 self.limit = None;
2052 self
2053 }
2054
2055 /// Offset number of returned rows.
2056 ///
2057 /// # Examples
2058 ///
2059 /// ```
2060 /// use sea_query::{tests_cfg::*, *};
2061 ///
2062 /// let query = Query::select()
2063 /// .column(Glyph::Aspect)
2064 /// .from(Glyph::Table)
2065 /// .limit(10)
2066 /// .offset(10)
2067 /// .to_owned();
2068 ///
2069 /// assert_eq!(
2070 /// query.to_string(MysqlQueryBuilder),
2071 /// r#"SELECT `aspect` FROM `glyph` LIMIT 10 OFFSET 10"#
2072 /// );
2073 /// assert_eq!(
2074 /// query.to_string(PostgresQueryBuilder),
2075 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
2076 /// );
2077 /// assert_eq!(
2078 /// query.to_string(SqliteQueryBuilder),
2079 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
2080 /// );
2081 /// ```
2082 pub fn offset(&mut self, offset: u64) -> &mut Self {
2083 self.offset = Some(offset.into());
2084 self
2085 }
2086
2087 /// Reset offset
2088 pub fn reset_offset(&mut self) -> &mut Self {
2089 self.offset = None;
2090 self
2091 }
2092
2093 /// Row locking (if supported).
2094 ///
2095 /// # Examples
2096 ///
2097 /// ```
2098 /// use sea_query::{tests_cfg::*, *};
2099 ///
2100 /// let query = Query::select()
2101 /// .column(Char::Character)
2102 /// .from(Char::Table)
2103 /// .and_where(Expr::col(Char::FontId).eq(5))
2104 /// .lock(LockType::Update)
2105 /// .to_owned();
2106 ///
2107 /// assert_eq!(
2108 /// query.to_string(MysqlQueryBuilder),
2109 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
2110 /// );
2111 /// assert_eq!(
2112 /// query.to_string(PostgresQueryBuilder),
2113 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
2114 /// );
2115 /// assert_eq!(
2116 /// query.to_string(SqliteQueryBuilder),
2117 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2118 /// );
2119 /// ```
2120 pub fn lock(&mut self, r#type: LockType) -> &mut Self {
2121 self.lock = Some(LockClause {
2122 r#type,
2123 tables: Vec::new(),
2124 behavior: None,
2125 });
2126 self
2127 }
2128
2129 /// Row locking with tables (if supported).
2130 ///
2131 /// # Examples
2132 ///
2133 /// ```
2134 /// use sea_query::{tests_cfg::*, *};
2135 ///
2136 /// let query = Query::select()
2137 /// .column(Char::Character)
2138 /// .from(Char::Table)
2139 /// .and_where(Expr::col(Char::FontId).eq(5))
2140 /// .lock_with_tables(LockType::Update, [Glyph::Table])
2141 /// .to_owned();
2142 ///
2143 /// assert_eq!(
2144 /// query.to_string(MysqlQueryBuilder),
2145 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph`"#
2146 /// );
2147 /// assert_eq!(
2148 /// query.to_string(PostgresQueryBuilder),
2149 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph""#
2150 /// );
2151 /// assert_eq!(
2152 /// query.to_string(SqliteQueryBuilder),
2153 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2154 /// );
2155 /// ```
2156 pub fn lock_with_tables<T, I>(&mut self, r#type: LockType, tables: I) -> &mut Self
2157 where
2158 T: IntoTableRef,
2159 I: IntoIterator<Item = T>,
2160 {
2161 self.lock = Some(LockClause {
2162 r#type,
2163 tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
2164 behavior: None,
2165 });
2166 self
2167 }
2168
2169 /// Row locking with behavior (if supported).
2170 ///
2171 /// # Examples
2172 ///
2173 /// ```
2174 /// use sea_query::{tests_cfg::*, *};
2175 ///
2176 /// let query = Query::select()
2177 /// .column(Char::Character)
2178 /// .from(Char::Table)
2179 /// .and_where(Expr::col(Char::FontId).eq(5))
2180 /// .lock_with_behavior(LockType::Update, LockBehavior::Nowait)
2181 /// .to_owned();
2182 ///
2183 /// assert_eq!(
2184 /// query.to_string(MysqlQueryBuilder),
2185 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE NOWAIT"#
2186 /// );
2187 /// assert_eq!(
2188 /// query.to_string(PostgresQueryBuilder),
2189 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE NOWAIT"#
2190 /// );
2191 /// assert_eq!(
2192 /// query.to_string(SqliteQueryBuilder),
2193 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2194 /// );
2195 /// ```
2196 pub fn lock_with_behavior(&mut self, r#type: LockType, behavior: LockBehavior) -> &mut Self {
2197 self.lock = Some(LockClause {
2198 r#type,
2199 tables: Vec::new(),
2200 behavior: Some(behavior),
2201 });
2202 self
2203 }
2204
2205 /// Row locking with tables and behavior (if supported).
2206 ///
2207 /// # Examples
2208 ///
2209 /// ```
2210 /// use sea_query::{tests_cfg::*, *};
2211 ///
2212 /// let query = Query::select()
2213 /// .column(Char::Character)
2214 /// .from(Char::Table)
2215 /// .and_where(Expr::col(Char::FontId).eq(5))
2216 /// .lock_with_tables_behavior(LockType::Update, [Glyph::Table], LockBehavior::Nowait)
2217 /// .to_owned();
2218 ///
2219 /// assert_eq!(
2220 /// query.to_string(MysqlQueryBuilder),
2221 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph` NOWAIT"#
2222 /// );
2223 /// assert_eq!(
2224 /// query.to_string(PostgresQueryBuilder),
2225 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph" NOWAIT"#
2226 /// );
2227 /// assert_eq!(
2228 /// query.to_string(SqliteQueryBuilder),
2229 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2230 /// );
2231 /// ```
2232 pub fn lock_with_tables_behavior<T, I>(
2233 &mut self,
2234 r#type: LockType,
2235 tables: I,
2236 behavior: LockBehavior,
2237 ) -> &mut Self
2238 where
2239 T: IntoTableRef,
2240 I: IntoIterator<Item = T>,
2241 {
2242 self.lock = Some(LockClause {
2243 r#type,
2244 tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
2245 behavior: Some(behavior),
2246 });
2247 self
2248 }
2249
2250 /// Shared row locking (if supported).
2251 ///
2252 /// # Examples
2253 ///
2254 /// ```
2255 /// use sea_query::{tests_cfg::*, *};
2256 ///
2257 /// let query = Query::select()
2258 /// .column(Char::Character)
2259 /// .from(Char::Table)
2260 /// .and_where(Expr::col(Char::FontId).eq(5))
2261 /// .lock_shared()
2262 /// .to_owned();
2263 ///
2264 /// assert_eq!(
2265 /// query.to_string(MysqlQueryBuilder),
2266 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 LOCK IN SHARE MODE"#
2267 /// );
2268 /// assert_eq!(
2269 /// query.to_string(PostgresQueryBuilder),
2270 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR SHARE"#
2271 /// );
2272 /// assert_eq!(
2273 /// query.to_string(SqliteQueryBuilder),
2274 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2275 /// );
2276 /// ```
2277 pub fn lock_shared(&mut self) -> &mut Self {
2278 self.lock(LockType::Share)
2279 }
2280
2281 /// Exclusive row locking (if supported).
2282 ///
2283 /// # Examples
2284 ///
2285 /// ```
2286 /// use sea_query::{tests_cfg::*, *};
2287 ///
2288 /// let query = Query::select()
2289 /// .column(Char::Character)
2290 /// .from(Char::Table)
2291 /// .and_where(Expr::col(Char::FontId).eq(5))
2292 /// .lock_exclusive()
2293 /// .to_owned();
2294 ///
2295 /// assert_eq!(
2296 /// query.to_string(MysqlQueryBuilder),
2297 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
2298 /// );
2299 /// assert_eq!(
2300 /// query.to_string(PostgresQueryBuilder),
2301 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
2302 /// );
2303 /// assert_eq!(
2304 /// query.to_string(SqliteQueryBuilder),
2305 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2306 /// );
2307 /// ```
2308 pub fn lock_exclusive(&mut self) -> &mut Self {
2309 self.lock(LockType::Update)
2310 }
2311
2312 /// Union with another SelectStatement that must have the same selected fields.
2313 ///
2314 /// # Examples
2315 ///
2316 /// ```
2317 /// use sea_query::{audit::*, tests_cfg::*, *};
2318 ///
2319 /// let query = Query::select()
2320 /// .column(Char::Character)
2321 /// .from(Char::Table)
2322 /// .and_where(Expr::col(Char::FontId).eq(5))
2323 /// .union(UnionType::All, Query::select()
2324 /// .column(Char::Character)
2325 /// .from(Char::Table)
2326 /// .and_where(Expr::col(Char::FontId).eq(4))
2327 /// .to_owned()
2328 /// )
2329 /// .to_owned();
2330 ///
2331 /// assert_eq!(
2332 /// query.to_string(MysqlQueryBuilder),
2333 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4)"#
2334 /// );
2335 /// assert_eq!(
2336 /// query.to_string(PostgresQueryBuilder),
2337 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4)"#
2338 /// );
2339 /// assert_eq!(
2340 /// query.to_string(SqliteQueryBuilder),
2341 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4"#
2342 /// );
2343 /// assert_eq!(
2344 /// query.audit().unwrap().selected_tables(),
2345 /// [Char::Table.into_iden()]
2346 /// );
2347 /// ```
2348 pub fn union(&mut self, union_type: UnionType, query: SelectStatement) -> &mut Self {
2349 self.unions.push((union_type, query));
2350 self
2351 }
2352
2353 /// Union with multiple SelectStatement that must have the same selected fields.
2354 ///
2355 /// # Examples
2356 ///
2357 /// ```
2358 /// use sea_query::{audit::*, tests_cfg::*, *};
2359 ///
2360 /// let query = Query::select()
2361 /// .column(Char::Character)
2362 /// .from(Char::Table)
2363 /// .and_where(Expr::col(Char::FontId).eq(5))
2364 /// .unions([
2365 /// (UnionType::All, Query::select()
2366 /// .column(Char::Character)
2367 /// .from(Char::Table)
2368 /// .and_where(Expr::col(Char::FontId).eq(4))
2369 /// .to_owned()),
2370 /// (UnionType::Distinct, Query::select()
2371 /// .column(Glyph::Image)
2372 /// .from(Glyph::Table)
2373 /// .to_owned()),
2374 /// ])
2375 /// .to_owned();
2376 ///
2377 /// assert_eq!(
2378 /// query.to_string(MysqlQueryBuilder),
2379 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4) UNION (SELECT `image` FROM `glyph`)"#
2380 /// );
2381 /// assert_eq!(
2382 /// query.to_string(PostgresQueryBuilder),
2383 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4) UNION (SELECT "image" FROM "glyph")"#
2384 /// );
2385 /// assert_eq!(
2386 /// query.to_string(SqliteQueryBuilder),
2387 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4 UNION SELECT "image" FROM "glyph""#
2388 /// );
2389 /// assert_eq!(
2390 /// query.audit().unwrap().selected_tables(),
2391 /// [Char::Table.into_iden(), Glyph::Table.into_iden()]
2392 /// );
2393 /// ```
2394 pub fn unions<T: IntoIterator<Item = (UnionType, SelectStatement)>>(
2395 &mut self,
2396 unions: T,
2397 ) -> &mut Self {
2398 self.unions.extend(unions);
2399 self
2400 }
2401
2402 /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
2403 ///
2404 /// # Examples
2405 ///
2406 /// ```
2407 /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
2408 ///
2409 /// let base_query = SelectStatement::new()
2410 /// .column("id")
2411 /// .expr(1i32)
2412 /// .column("next")
2413 /// .column("value")
2414 /// .from(Task::Table)
2415 /// .to_owned();
2416 ///
2417 /// let cte_referencing = SelectStatement::new()
2418 /// .column("id")
2419 /// .expr(Expr::col("depth").add(1i32))
2420 /// .column("next")
2421 /// .column("value")
2422 /// .from(Task::Table)
2423 /// .join(
2424 /// JoinType::InnerJoin,
2425 /// "cte_traversal",
2426 /// Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
2427 /// )
2428 /// .to_owned();
2429 ///
2430 /// let common_table_expression = CommonTableExpression::new()
2431 /// .query(
2432 /// base_query.clone().union(UnionType::All, cte_referencing).to_owned()
2433 /// )
2434 /// .columns(["id", "depth", "next", "value"])
2435 /// .table_name("cte_traversal")
2436 /// .to_owned();
2437 ///
2438 /// let select = SelectStatement::new()
2439 /// .column(Asterisk)
2440 /// .from("cte_traversal")
2441 /// .to_owned();
2442 ///
2443 /// let with_clause = WithClause::new()
2444 /// .recursive(true)
2445 /// .cte(common_table_expression)
2446 /// .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
2447 /// .to_owned();
2448 ///
2449 /// let query = select.with(with_clause).to_owned();
2450 ///
2451 /// assert_eq!(
2452 /// query.to_string(MysqlQueryBuilder),
2453 /// r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
2454 /// );
2455 /// assert_eq!(
2456 /// query.to_string(PostgresQueryBuilder),
2457 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
2458 /// );
2459 /// assert_eq!(
2460 /// query.to_string(SqliteQueryBuilder),
2461 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
2462 /// );
2463 /// assert_eq!(
2464 /// query.audit().unwrap().selected_tables(),
2465 /// [Task::Table.into_iden()]
2466 /// );
2467 /// ```
2468 pub fn with(self, clause: WithClause) -> WithQuery {
2469 clause.query(self)
2470 }
2471
2472 /// Create a Common Table Expression by specifying a [CommonTableExpression] or [WithClause] to execute this query with.
2473 ///
2474 /// # Examples
2475 ///
2476 /// ```
2477 /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
2478 ///
2479 /// let base_query = SelectStatement::new()
2480 /// .column("id")
2481 /// .expr(1i32)
2482 /// .column("next")
2483 /// .column("value")
2484 /// .from(Task::Table)
2485 /// .to_owned();
2486 ///
2487 /// let cte_referencing = SelectStatement::new()
2488 /// .column("id")
2489 /// .expr(Expr::col("depth").add(1i32))
2490 /// .column("next")
2491 /// .column("value")
2492 /// .from(Task::Table)
2493 /// .join(
2494 /// JoinType::InnerJoin,
2495 /// "cte_traversal",
2496 /// Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
2497 /// )
2498 /// .to_owned();
2499 ///
2500 /// let common_table_expression = CommonTableExpression::new()
2501 /// .query(
2502 /// base_query.clone().union(UnionType::All, cte_referencing).to_owned()
2503 /// )
2504 /// .columns(["id", "depth", "next", "value"])
2505 /// .table_name("cte_traversal")
2506 /// .to_owned();
2507 ///
2508 /// let with_clause = WithClause::new()
2509 /// .recursive(true)
2510 /// .cte(common_table_expression)
2511 /// .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
2512 /// .to_owned();
2513 ///
2514 /// let query = SelectStatement::new()
2515 /// .column(Asterisk)
2516 /// .from("cte_traversal")
2517 /// .with_cte(with_clause)
2518 /// .to_owned();
2519 ///
2520 /// assert_eq!(
2521 /// query.to_string(MysqlQueryBuilder),
2522 /// r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
2523 /// );
2524 /// assert_eq!(
2525 /// query.to_string(PostgresQueryBuilder),
2526 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
2527 /// );
2528 /// assert_eq!(
2529 /// query.to_string(SqliteQueryBuilder),
2530 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
2531 /// );
2532 /// assert_eq!(
2533 /// query.audit().unwrap().selected_tables(),
2534 /// [Task::Table.into_iden()]
2535 /// );
2536 /// ```
2537 pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
2538 self.with = Some(Box::new(clause.into()));
2539 self
2540 }
2541
2542 /// WINDOW
2543 ///
2544 /// # Examples:
2545 ///
2546 /// ```
2547 /// use sea_query::{tests_cfg::*, *};
2548 ///
2549 /// let query = Query::select()
2550 /// .from(Char::Table)
2551 /// .expr_window_name_as(Expr::col(Char::Character), "w", "C")
2552 /// .window("w", WindowStatement::partition_by(Char::FontSize))
2553 /// .to_owned();
2554 ///
2555 /// assert_eq!(
2556 /// query.to_string(MysqlQueryBuilder),
2557 /// r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
2558 /// );
2559 /// assert_eq!(
2560 /// query.to_string(PostgresQueryBuilder),
2561 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
2562 /// );
2563 /// assert_eq!(
2564 /// query.to_string(SqliteQueryBuilder),
2565 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
2566 /// );
2567 /// ```
2568 pub fn window<A>(&mut self, name: A, window: WindowStatement) -> &mut Self
2569 where
2570 A: IntoIden,
2571 {
2572 self.window = Some((name.into_iden(), window));
2573 self
2574 }
2575}
2576
2577#[inherent]
2578impl QueryStatementBuilder for SelectStatement {
2579 pub fn build_collect_any_into(
2580 &self,
2581 query_builder: &impl QueryBuilder,
2582 sql: &mut impl SqlWriter,
2583 ) {
2584 query_builder.prepare_select_statement(self, sql);
2585 }
2586
2587 pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values);
2588 pub fn build_collect_any(
2589 &self,
2590 query_builder: &impl QueryBuilder,
2591 sql: &mut impl SqlWriter,
2592 ) -> String;
2593}
2594
2595impl From<SelectStatement> for QueryStatement {
2596 fn from(s: SelectStatement) -> Self {
2597 Self::Select(s)
2598 }
2599}
2600
2601impl From<SelectStatement> for SubQueryStatement {
2602 fn from(s: SelectStatement) -> Self {
2603 Self::SelectStatement(s)
2604 }
2605}
2606
2607#[inherent]
2608impl QueryStatementWriter for SelectStatement {
2609 pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
2610 query_builder.prepare_select_statement(self, sql);
2611 }
2612
2613 pub fn build_collect<T: QueryBuilder>(
2614 &self,
2615 query_builder: T,
2616 sql: &mut impl SqlWriter,
2617 ) -> String;
2618 pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);
2619 pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;
2620}
2621
2622#[inherent]
2623impl OrderedStatement for SelectStatement {
2624 pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
2625 self.orders.push(order);
2626 self
2627 }
2628
2629 pub fn clear_order_by(&mut self) -> &mut Self {
2630 self.orders = Vec::new();
2631 self
2632 }
2633
2634 pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self
2635 where
2636 T: IntoColumnRef;
2637
2638 pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self;
2639 pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
2640 where
2641 T: Into<Cow<'static, str>>,
2642 I: IntoIterator<Item = (T, Order)>;
2643 pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
2644 where
2645 T: IntoColumnRef,
2646 I: IntoIterator<Item = (T, Order)>;
2647 pub fn order_by_with_nulls<T>(
2648 &mut self,
2649 col: T,
2650 order: Order,
2651 nulls: NullOrdering,
2652 ) -> &mut Self
2653 where
2654 T: IntoColumnRef;
2655 pub fn order_by_expr_with_nulls(
2656 &mut self,
2657 expr: Expr,
2658 order: Order,
2659 nulls: NullOrdering,
2660 ) -> &mut Self;
2661 pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
2662 where
2663 T: Into<Cow<'static, str>>,
2664 I: IntoIterator<Item = (T, Order, NullOrdering)>;
2665 pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
2666 where
2667 T: IntoColumnRef,
2668 I: IntoIterator<Item = (T, Order, NullOrdering)>;
2669}
2670
2671#[inherent]
2672impl ConditionalStatement for SelectStatement {
2673 pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
2674 self.r#where.add_and_or(condition);
2675 self
2676 }
2677
2678 pub fn cond_where<C>(&mut self, condition: C) -> &mut Self
2679 where
2680 C: IntoCondition,
2681 {
2682 self.r#where.add_condition(condition.into_condition());
2683 self
2684 }
2685
2686 pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self;
2687 pub fn and_where(&mut self, other: Expr) -> &mut Self;
2688}