sea_query/query/select.rs
1use std::borrow::Cow;
2#[cfg(feature = "backend-mysql")]
3use std::collections::HashMap;
4
5use crate::{
6 FunctionCall, QueryStatement, QueryStatementBuilder, QueryStatementWriter, SubQueryStatement,
7 WindowStatement, WithClause, WithQuery,
8 backend::QueryBuilder,
9 expr::*,
10 prepare::*,
11 query::{OrderedStatement, condition::*},
12 types::*,
13 value::*,
14};
15use inherent::inherent;
16
17/// Select rows from an existing table
18///
19/// # Examples
20///
21/// ```
22/// use sea_query::{*, tests_cfg::*};
23///
24/// let query = Query::select()
25/// .column(Char::Character)
26/// .column((Font::Table, Font::Name))
27/// .from(Char::Table)
28/// .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
29/// .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
30/// .and_where(Expr::col(Char::Character).like("A%"))
31/// .to_owned();
32///
33/// assert_eq!(
34/// query.to_string(MysqlQueryBuilder),
35/// 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%'"#
36/// );
37/// assert_eq!(
38/// query.to_string(PostgresQueryBuilder),
39/// 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%'"#
40/// );
41/// assert_eq!(
42/// query.to_string(SqliteQueryBuilder),
43/// 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%'"#
44/// );
45/// ```
46#[derive(Default, Debug, Clone, PartialEq)]
47pub struct SelectStatement {
48 pub(crate) distinct: Option<SelectDistinct>,
49 pub(crate) selects: Vec<SelectExpr>,
50 pub(crate) from: Vec<TableRef>,
51 pub(crate) join: Vec<JoinExpr>,
52 pub(crate) r#where: ConditionHolder,
53 pub(crate) groups: Vec<Expr>,
54 pub(crate) having: ConditionHolder,
55 pub(crate) unions: Vec<(UnionType, SelectStatement)>,
56 pub(crate) orders: Vec<OrderExpr>,
57 pub(crate) limit: Option<Value>,
58 pub(crate) offset: Option<Value>,
59 pub(crate) lock: Option<LockClause>,
60 pub(crate) window: Option<(DynIden, WindowStatement)>,
61 pub(crate) with: Option<Box<WithClause>>,
62 #[cfg(feature = "backend-postgres")]
63 pub(crate) table_sample: Option<crate::extension::postgres::TableSample>,
64 #[cfg(feature = "backend-mysql")]
65 pub(crate) index_hints:
66 HashMap<index_hint::IndexHintKey, Vec<crate::extension::mysql::IndexHint>>,
67}
68
69#[cfg(feature = "backend-mysql")]
70mod index_hint {
71 use crate::*;
72 #[derive(Debug, Clone, PartialEq, Eq, Hash)]
73 pub(crate) enum IndexHintKey {
74 Table(TableName, Option<DynIden>),
75 Alias(DynIden),
76 }
77
78 impl From<TableRef> for IndexHintKey {
79 fn from(value: TableRef) -> Self {
80 match value {
81 TableRef::Table(table_name, alias) => Self::Table(table_name, alias),
82 TableRef::SubQuery(_, alias)
83 | TableRef::ValuesList(_, alias)
84 | TableRef::FunctionCall(_, alias) => Self::Alias(alias),
85 }
86 }
87 }
88
89 impl From<&TableRef> for IndexHintKey {
90 fn from(value: &TableRef) -> Self {
91 match value {
92 TableRef::Table(table_name, alias) => {
93 Self::Table(table_name.clone(), alias.clone())
94 }
95 TableRef::SubQuery(_, alias)
96 | TableRef::ValuesList(_, alias)
97 | TableRef::FunctionCall(_, alias) => Self::Alias(alias.clone()),
98 }
99 }
100 }
101
102 impl PartialEq<&TableRef> for IndexHintKey {
103 fn eq(&self, other: &&TableRef) -> bool {
104 match (self, other) {
105 (Self::Table(table_name, alias), TableRef::Table(table_name2, alias2)) => {
106 table_name == table_name2 && alias == alias2
107 }
108 (
109 Self::Alias(alias),
110 TableRef::SubQuery(_, alias2)
111 | TableRef::ValuesList(_, alias2)
112 | TableRef::FunctionCall(_, alias2),
113 ) => alias == alias2,
114 _ => false,
115 }
116 }
117 }
118}
119
120/// List of distinct keywords that can be used in select statement
121#[derive(Debug, Clone, PartialEq)]
122#[non_exhaustive]
123pub enum SelectDistinct {
124 All,
125 Distinct,
126 DistinctRow,
127 DistinctOn(Vec<ColumnRef>),
128}
129
130/// Window type in [`SelectExpr`]
131#[derive(Debug, Clone, PartialEq)]
132#[non_exhaustive]
133pub enum WindowSelectType {
134 /// Name in [`SelectStatement`]
135 Name(DynIden),
136 /// Inline query in [`SelectExpr`]
137 Query(WindowStatement),
138}
139
140/// Select expression used in select statement
141#[derive(Debug, Clone, PartialEq)]
142pub struct SelectExpr {
143 pub expr: Expr,
144 pub alias: Option<DynIden>,
145 pub window: Option<WindowSelectType>,
146}
147
148/// Join expression used in select statement
149#[derive(Debug, Clone, PartialEq)]
150pub struct JoinExpr {
151 pub join: JoinType,
152 pub table: Box<TableRef>,
153 pub on: Option<JoinOn>,
154 pub lateral: bool,
155}
156
157/// List of lock types that can be used in select statement
158#[derive(Debug, Clone, Copy, PartialEq, Eq)]
159#[non_exhaustive]
160pub enum LockType {
161 /// Exclusive lock
162 Update,
163 NoKeyUpdate,
164 /// Shared lock
165 Share,
166 KeyShare,
167}
168
169/// List of lock behavior can be used in select statement
170#[derive(Debug, Clone, Copy, PartialEq, Eq)]
171#[non_exhaustive]
172pub enum LockBehavior {
173 Nowait,
174 SkipLocked,
175}
176
177#[derive(Debug, Clone, PartialEq)]
178pub struct LockClause {
179 pub(crate) r#type: LockType,
180 pub(crate) tables: Vec<TableRef>,
181 pub(crate) behavior: Option<LockBehavior>,
182}
183
184/// List of union types that can be used in union clause
185#[derive(Debug, Clone, Copy, PartialEq, Eq)]
186#[non_exhaustive]
187pub enum UnionType {
188 Intersect,
189 Distinct,
190 Except,
191 All,
192}
193
194impl<T> From<T> for SelectExpr
195where
196 T: Into<Expr>,
197{
198 fn from(expr: T) -> Self {
199 SelectExpr {
200 expr: expr.into(),
201 alias: None,
202 window: None,
203 }
204 }
205}
206
207impl SelectStatement {
208 /// Construct a new [`SelectStatement`]
209 pub fn new() -> Self {
210 Self::default()
211 }
212
213 /// Take the ownership of data in the current [`SelectStatement`]
214 pub fn take(&mut self) -> Self {
215 Self {
216 distinct: self.distinct.take(),
217 selects: std::mem::take(&mut self.selects),
218 from: std::mem::take(&mut self.from),
219 join: std::mem::take(&mut self.join),
220 r#where: std::mem::replace(&mut self.r#where, ConditionHolder::new()),
221 groups: std::mem::take(&mut self.groups),
222 having: std::mem::replace(&mut self.having, ConditionHolder::new()),
223 unions: std::mem::take(&mut self.unions),
224 orders: std::mem::take(&mut self.orders),
225 limit: self.limit.take(),
226 offset: self.offset.take(),
227 lock: self.lock.take(),
228 window: self.window.take(),
229 with: self.with.take(),
230 #[cfg(feature = "backend-postgres")]
231 table_sample: std::mem::take(&mut self.table_sample),
232 #[cfg(feature = "backend-mysql")]
233 index_hints: std::mem::take(&mut self.index_hints),
234 }
235 }
236
237 /// A shorthand to express if ... else ... when constructing the select statement.
238 ///
239 /// # Examples
240 ///
241 /// ```
242 /// use sea_query::{tests_cfg::*, *};
243 ///
244 /// let query = Query::select()
245 /// .column(Char::Character)
246 /// .from(Char::Table)
247 /// .conditions(
248 /// true,
249 /// |x| {
250 /// x.and_where(Expr::col(Char::FontId).eq(5));
251 /// },
252 /// |x| {
253 /// x.and_where(Expr::col(Char::FontId).eq(10));
254 /// },
255 /// )
256 /// .to_owned();
257 ///
258 /// assert_eq!(
259 /// query.to_string(MysqlQueryBuilder),
260 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
261 /// );
262 /// assert_eq!(
263 /// query.to_string(PostgresQueryBuilder),
264 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
265 /// );
266 /// assert_eq!(
267 /// query.to_string(SqliteQueryBuilder),
268 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
269 /// );
270 /// ```
271 pub fn conditions<T, F>(&mut self, b: bool, if_true: T, if_false: F) -> &mut Self
272 where
273 T: FnOnce(&mut Self),
274 F: FnOnce(&mut Self),
275 {
276 if b {
277 if_true(self)
278 } else {
279 if_false(self)
280 }
281 self
282 }
283
284 /// A shorthand to express if ... else ... when constructing the select statement.
285 ///
286 /// # Examples
287 ///
288 /// ```
289 /// use sea_query::{tests_cfg::*, *};
290 ///
291 /// let query = Query::select()
292 /// .column(Char::Character)
293 /// .from(Char::Table)
294 /// .apply_if(Some(5), |q, v| {
295 /// q.and_where(Expr::col(Char::FontId).eq(v));
296 /// })
297 /// .to_owned();
298 ///
299 /// assert_eq!(
300 /// query.to_string(MysqlQueryBuilder),
301 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
302 /// );
303 /// ```
304 pub fn apply_if<T, F>(&mut self, val: Option<T>, if_some: F) -> &mut Self
305 where
306 Self: Sized,
307 F: FnOnce(&mut Self, T),
308 {
309 if let Some(val) = val {
310 if_some(self, val);
311 }
312 self
313 }
314
315 /// Construct part of the select statement in another function.
316 ///
317 /// # Examples
318 ///
319 /// ```
320 /// use sea_query::{tests_cfg::*, *};
321 ///
322 /// let common_expr = |q: &mut SelectStatement| {
323 /// q.and_where(Expr::col(Char::FontId).eq(5));
324 /// };
325 ///
326 /// let query = Query::select()
327 /// .column(Char::Character)
328 /// .from(Char::Table)
329 /// .apply(common_expr)
330 /// .to_owned();
331 ///
332 /// assert_eq!(
333 /// query.to_string(MysqlQueryBuilder),
334 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
335 /// );
336 /// assert_eq!(
337 /// query.to_string(PostgresQueryBuilder),
338 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
339 /// );
340 /// assert_eq!(
341 /// query.to_string(SqliteQueryBuilder),
342 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
343 /// );
344 /// ```
345 pub fn apply<F>(&mut self, func: F) -> &mut Self
346 where
347 F: FnOnce(&mut Self),
348 {
349 func(self);
350 self
351 }
352
353 /// Clear the select list
354 pub fn clear_selects(&mut self) -> &mut Self {
355 self.selects = Vec::new();
356 self
357 }
358
359 /// Add an expression to the select expression list.
360 ///
361 /// # Examples
362 ///
363 /// ```
364 /// use sea_query::{tests_cfg::*, *};
365 ///
366 /// let query = Query::select()
367 /// .from(Char::Table)
368 /// .expr(Expr::val(42))
369 /// .expr(Expr::col(Char::Id).max())
370 /// .expr((1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)))
371 /// .to_owned();
372 ///
373 /// assert_eq!(
374 /// query.to_string(MysqlQueryBuilder),
375 /// r#"SELECT 42, MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
376 /// );
377 /// assert_eq!(
378 /// query.to_string(PostgresQueryBuilder),
379 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
380 /// );
381 /// assert_eq!(
382 /// query.to_string(SqliteQueryBuilder),
383 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
384 /// );
385 /// ```
386 pub fn expr<T>(&mut self, expr: T) -> &mut Self
387 where
388 T: Into<SelectExpr>,
389 {
390 self.selects.push(expr.into());
391 self
392 }
393
394 /// Add select expressions from vector of [`SelectExpr`].
395 ///
396 /// # Examples
397 ///
398 /// ```
399 /// use sea_query::{tests_cfg::*, *};
400 ///
401 /// let query = Query::select()
402 /// .from(Char::Table)
403 /// .exprs([
404 /// Expr::col(Char::Id).max(),
405 /// (1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)),
406 /// ])
407 /// .to_owned();
408 ///
409 /// assert_eq!(
410 /// query.to_string(MysqlQueryBuilder),
411 /// r#"SELECT MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
412 /// );
413 /// assert_eq!(
414 /// query.to_string(PostgresQueryBuilder),
415 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
416 /// );
417 /// assert_eq!(
418 /// query.to_string(SqliteQueryBuilder),
419 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
420 /// );
421 /// ```
422 pub fn exprs<T, I>(&mut self, exprs: I) -> &mut Self
423 where
424 T: Into<SelectExpr>,
425 I: IntoIterator<Item = T>,
426 {
427 self.selects
428 .append(&mut exprs.into_iter().map(|c| c.into()).collect());
429 self
430 }
431
432 pub fn exprs_mut_for_each<F>(&mut self, func: F)
433 where
434 F: FnMut(&mut SelectExpr),
435 {
436 self.selects.iter_mut().for_each(func);
437 }
438
439 /// Select distinct
440 pub fn distinct(&mut self) -> &mut Self {
441 self.distinct = Some(SelectDistinct::Distinct);
442 self
443 }
444
445 /// Select distinct on for *POSTGRES ONLY*
446 ///
447 /// # Examples
448 ///
449 /// ```
450 /// use sea_query::{tests_cfg::*, *};
451 ///
452 /// let query = Query::select()
453 /// .from(Char::Table)
454 /// .distinct_on([Char::Character])
455 /// .column(Char::Character)
456 /// .column(Char::SizeW)
457 /// .column(Char::SizeH)
458 /// .to_owned();
459 ///
460 /// assert_eq!(
461 /// query.to_string(PostgresQueryBuilder),
462 /// r#"SELECT DISTINCT ON ("character") "character", "size_w", "size_h" FROM "character""#
463 /// )
464 /// ```
465 ///
466 /// ```
467 /// use sea_query::{tests_cfg::*, *};
468 ///
469 /// let query = Query::select()
470 /// .from(Char::Table)
471 /// .distinct_on(vec![(Char::Table, Char::Character)])
472 /// .column(Char::Character)
473 /// .column(Char::SizeW)
474 /// .column(Char::SizeH)
475 /// .to_owned();
476 ///
477 /// assert_eq!(
478 /// query.to_string(PostgresQueryBuilder),
479 /// r#"SELECT DISTINCT ON ("character"."character") "character", "size_w", "size_h" FROM "character""#
480 /// )
481 /// ```
482 ///
483 /// ```
484 /// use sea_query::{tests_cfg::*, *};
485 ///
486 /// let distinct_cols: Vec<Character> = vec![];
487 /// let query = Query::select()
488 /// .from(Char::Table)
489 /// .distinct_on(distinct_cols)
490 /// .column(Char::Character)
491 /// .column(Char::SizeW)
492 /// .column(Char::SizeH)
493 /// .to_owned();
494 ///
495 /// assert_eq!(
496 /// query.to_string(PostgresQueryBuilder),
497 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
498 /// )
499 /// ```
500 pub fn distinct_on<T, I>(&mut self, cols: I) -> &mut Self
501 where
502 T: IntoColumnRef,
503 I: IntoIterator<Item = T>,
504 {
505 let cols = cols
506 .into_iter()
507 .map(|col| col.into_column_ref())
508 .collect::<Vec<ColumnRef>>();
509 self.distinct = if !cols.is_empty() {
510 Some(SelectDistinct::DistinctOn(cols))
511 } else {
512 None
513 };
514 self
515 }
516
517 /// Add a column to the select expression list.
518 ///
519 /// # Examples
520 ///
521 /// ```
522 /// use sea_query::{tests_cfg::*, *};
523 ///
524 /// let query = Query::select()
525 /// .from(Char::Table)
526 /// .column(Char::Character)
527 /// .column(Char::SizeW)
528 /// .column(Char::SizeH)
529 /// .to_owned();
530 ///
531 /// assert_eq!(
532 /// query.to_string(MysqlQueryBuilder),
533 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
534 /// );
535 /// assert_eq!(
536 /// query.to_string(PostgresQueryBuilder),
537 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
538 /// );
539 /// assert_eq!(
540 /// query.to_string(SqliteQueryBuilder),
541 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
542 /// );
543 /// ```
544 ///
545 /// ```
546 /// use sea_query::{tests_cfg::*, *};
547 ///
548 /// let query = Query::select()
549 /// .from(Char::Table)
550 /// .column((Char::Table, Char::Character))
551 /// .to_owned();
552 ///
553 /// assert_eq!(
554 /// query.to_string(MysqlQueryBuilder),
555 /// r#"SELECT `character`.`character` FROM `character`"#
556 /// );
557 /// assert_eq!(
558 /// query.to_string(PostgresQueryBuilder),
559 /// r#"SELECT "character"."character" FROM "character""#
560 /// );
561 /// assert_eq!(
562 /// query.to_string(SqliteQueryBuilder),
563 /// r#"SELECT "character"."character" FROM "character""#
564 /// );
565 /// ```
566 ///
567 /// ```
568 /// use sea_query::{tests_cfg::*, *};
569 ///
570 /// let query = Query::select()
571 /// .from(Char::Table)
572 /// .column(("schema", Char::Table, Char::Character))
573 /// .to_owned();
574 ///
575 /// assert_eq!(
576 /// query.to_string(MysqlQueryBuilder),
577 /// r#"SELECT `schema`.`character`.`character` FROM `character`"#
578 /// );
579 /// assert_eq!(
580 /// query.to_string(PostgresQueryBuilder),
581 /// r#"SELECT "schema"."character"."character" FROM "character""#
582 /// );
583 /// assert_eq!(
584 /// query.to_string(SqliteQueryBuilder),
585 /// r#"SELECT "schema"."character"."character" FROM "character""#
586 /// );
587 /// ```
588 pub fn column<C>(&mut self, col: C) -> &mut Self
589 where
590 C: IntoColumnRef,
591 {
592 self.expr(Expr::Column(col.into_column_ref()))
593 }
594
595 /// Select columns.
596 ///
597 /// # Examples
598 ///
599 /// ```
600 /// use sea_query::{tests_cfg::*, *};
601 ///
602 /// let query = Query::select()
603 /// .from(Char::Table)
604 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
605 /// .to_owned();
606 ///
607 /// assert_eq!(
608 /// query.to_string(MysqlQueryBuilder),
609 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
610 /// );
611 /// assert_eq!(
612 /// query.to_string(PostgresQueryBuilder),
613 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
614 /// );
615 /// assert_eq!(
616 /// query.to_string(SqliteQueryBuilder),
617 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
618 /// );
619 /// ```
620 ///
621 /// ```
622 /// use sea_query::{*, tests_cfg::*};
623 ///
624 /// let query = Query::select()
625 /// .from(Char::Table)
626 /// .columns([
627 /// (Char::Table, Char::Character),
628 /// (Char::Table, Char::SizeW),
629 /// (Char::Table, Char::SizeH),
630 /// ])
631 /// .to_owned();
632 ///
633 /// assert_eq!(
634 /// query.to_string(MysqlQueryBuilder),
635 /// r#"SELECT `character`.`character`, `character`.`size_w`, `character`.`size_h` FROM `character`"#
636 /// );
637 /// assert_eq!(
638 /// query.to_string(PostgresQueryBuilder),
639 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
640 /// );
641 /// assert_eq!(
642 /// query.to_string(SqliteQueryBuilder),
643 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
644 /// );
645 /// ```
646 pub fn columns<T, I>(&mut self, cols: I) -> &mut Self
647 where
648 T: IntoColumnRef,
649 I: IntoIterator<Item = T>,
650 {
651 self.exprs(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
652 }
653
654 /// Select column.
655 ///
656 /// # Examples
657 ///
658 /// ```
659 /// use sea_query::{tests_cfg::*, *};
660 ///
661 /// let query = Query::select()
662 /// .from(Char::Table)
663 /// .expr_as(Expr::col(Char::Character), "C")
664 /// .to_owned();
665 ///
666 /// assert_eq!(
667 /// query.to_string(MysqlQueryBuilder),
668 /// r#"SELECT `character` AS `C` FROM `character`"#
669 /// );
670 /// assert_eq!(
671 /// query.to_string(PostgresQueryBuilder),
672 /// r#"SELECT "character" AS "C" FROM "character""#
673 /// );
674 /// assert_eq!(
675 /// query.to_string(SqliteQueryBuilder),
676 /// r#"SELECT "character" AS "C" FROM "character""#
677 /// );
678 /// ```
679 pub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut Self
680 where
681 T: Into<Expr>,
682 A: IntoIden,
683 {
684 self.expr(SelectExpr {
685 expr: expr.into(),
686 alias: Some(alias.into_iden()),
687 window: None,
688 });
689 self
690 }
691
692 /// Select column with window function.
693 ///
694 /// # Examples
695 ///
696 /// ```
697 /// use sea_query::{tests_cfg::*, *};
698 ///
699 /// let query = Query::select()
700 /// .from(Char::Table)
701 /// .expr_window(
702 /// Expr::col(Char::Character),
703 /// WindowStatement::partition_by(Char::FontSize),
704 /// )
705 /// .to_owned();
706 ///
707 /// assert_eq!(
708 /// query.to_string(MysqlQueryBuilder),
709 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) FROM `character`"#
710 /// );
711 /// assert_eq!(
712 /// query.to_string(PostgresQueryBuilder),
713 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
714 /// );
715 /// assert_eq!(
716 /// query.to_string(SqliteQueryBuilder),
717 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
718 /// );
719 /// ```
720 pub fn expr_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self
721 where
722 T: Into<Expr>,
723 {
724 self.expr(SelectExpr {
725 expr: expr.into(),
726 alias: None,
727 window: Some(WindowSelectType::Query(window)),
728 });
729 self
730 }
731
732 /// Select column with window function and label.
733 ///
734 /// # Examples
735 ///
736 /// ```
737 /// use sea_query::{tests_cfg::*, *};
738 ///
739 /// let query = Query::select()
740 /// .from(Char::Table)
741 /// .expr_window_as(
742 /// Expr::col(Char::Character),
743 /// WindowStatement::partition_by(Char::FontSize),
744 /// "C",
745 /// )
746 /// .to_owned();
747 ///
748 /// assert_eq!(
749 /// query.to_string(MysqlQueryBuilder),
750 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
751 /// );
752 /// assert_eq!(
753 /// query.to_string(PostgresQueryBuilder),
754 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
755 /// );
756 /// assert_eq!(
757 /// query.to_string(SqliteQueryBuilder),
758 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
759 /// );
760 /// ```
761 pub fn expr_window_as<T, A>(&mut self, expr: T, window: WindowStatement, alias: A) -> &mut Self
762 where
763 T: Into<Expr>,
764 A: IntoIden,
765 {
766 self.expr(SelectExpr {
767 expr: expr.into(),
768 alias: Some(alias.into_iden()),
769 window: Some(WindowSelectType::Query(window)),
770 });
771 self
772 }
773
774 /// Select column with window name.
775 ///
776 /// # Examples
777 ///
778 /// ```
779 /// use sea_query::{tests_cfg::*, *};
780 ///
781 /// let query = Query::select()
782 /// .from(Char::Table)
783 /// .expr_window_name(Expr::col(Char::Character), "w")
784 /// .window("w", WindowStatement::partition_by(Char::FontSize))
785 /// .to_owned();
786 ///
787 /// assert_eq!(
788 /// query.to_string(MysqlQueryBuilder),
789 /// r#"SELECT `character` OVER `w` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
790 /// );
791 /// assert_eq!(
792 /// query.to_string(PostgresQueryBuilder),
793 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
794 /// );
795 /// assert_eq!(
796 /// query.to_string(SqliteQueryBuilder),
797 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
798 /// );
799 /// ```
800 pub fn expr_window_name<T, W>(&mut self, expr: T, window: W) -> &mut Self
801 where
802 T: Into<Expr>,
803 W: IntoIden,
804 {
805 self.expr(SelectExpr {
806 expr: expr.into(),
807 alias: None,
808 window: Some(WindowSelectType::Name(window.into_iden())),
809 });
810 self
811 }
812
813 /// Select column with window name and label.
814 ///
815 /// # Examples
816 ///
817 /// ```
818 /// use sea_query::{tests_cfg::*, *};
819 ///
820 /// let query = Query::select()
821 /// .from(Char::Table)
822 /// .expr_window_name_as(Expr::col(Char::Character), "w", "C")
823 /// .window("w", WindowStatement::partition_by(Char::FontSize))
824 /// .to_owned();
825 ///
826 /// assert_eq!(
827 /// query.to_string(MysqlQueryBuilder),
828 /// r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
829 /// );
830 /// assert_eq!(
831 /// query.to_string(PostgresQueryBuilder),
832 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
833 /// );
834 /// assert_eq!(
835 /// query.to_string(SqliteQueryBuilder),
836 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
837 /// );
838 /// ```
839 pub fn expr_window_name_as<T, W, A>(&mut self, expr: T, window: W, alias: A) -> &mut Self
840 where
841 T: Into<Expr>,
842 A: IntoIden,
843 W: IntoIden,
844 {
845 self.expr(SelectExpr {
846 expr: expr.into(),
847 alias: Some(alias.into_iden()),
848 window: Some(WindowSelectType::Name(window.into_iden())),
849 });
850 self
851 }
852
853 /// From table.
854 ///
855 /// # Examples
856 ///
857 /// ```
858 /// use sea_query::{tests_cfg::*, *};
859 ///
860 /// let query = Query::select()
861 /// .column(Char::FontSize)
862 /// .from(Char::Table)
863 /// .to_owned();
864 ///
865 /// assert_eq!(
866 /// query.to_string(MysqlQueryBuilder),
867 /// r#"SELECT `font_size` FROM `character`"#
868 /// );
869 /// assert_eq!(
870 /// query.to_string(PostgresQueryBuilder),
871 /// r#"SELECT "font_size" FROM "character""#
872 /// );
873 /// assert_eq!(
874 /// query.to_string(SqliteQueryBuilder),
875 /// r#"SELECT "font_size" FROM "character""#
876 /// );
877 /// ```
878 ///
879 /// ```
880 /// use sea_query::{tests_cfg::*, *};
881 ///
882 /// let query = Query::select()
883 /// .column(Char::FontSize)
884 /// .from((Char::Table, Glyph::Table))
885 /// .to_owned();
886 ///
887 /// assert_eq!(
888 /// query.to_string(MysqlQueryBuilder),
889 /// r#"SELECT `font_size` FROM `character`.`glyph`"#
890 /// );
891 /// assert_eq!(
892 /// query.to_string(PostgresQueryBuilder),
893 /// r#"SELECT "font_size" FROM "character"."glyph""#
894 /// );
895 /// assert_eq!(
896 /// query.to_string(SqliteQueryBuilder),
897 /// r#"SELECT "font_size" FROM "character"."glyph""#
898 /// );
899 /// ```
900 ///
901 /// ```
902 /// use sea_query::{tests_cfg::*, *};
903 ///
904 /// let query = Query::select()
905 /// .column(Char::FontSize)
906 /// .from(("database", Char::Table, Glyph::Table))
907 /// .to_owned();
908 ///
909 /// assert_eq!(
910 /// query.to_string(MysqlQueryBuilder),
911 /// r#"SELECT `font_size` FROM `database`.`character`.`glyph`"#
912 /// );
913 /// assert_eq!(
914 /// query.to_string(PostgresQueryBuilder),
915 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
916 /// );
917 /// assert_eq!(
918 /// query.to_string(SqliteQueryBuilder),
919 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
920 /// );
921 /// ```
922 ///
923 /// If you specify `from` multiple times, the resulting query will have multiple from clauses.
924 /// You can perform an 'old-school' join this way.
925 ///
926 /// ```
927 /// use sea_query::{tests_cfg::*, *};
928 ///
929 /// let query = Query::select()
930 /// .expr(Expr::asterisk())
931 /// .from(Char::Table)
932 /// .from(Font::Table)
933 /// .and_where(Expr::col((Font::Table, Font::Id)).equals((Char::Table, Char::FontId)))
934 /// .to_owned();
935 ///
936 /// assert_eq!(
937 /// query.to_string(MysqlQueryBuilder),
938 /// r#"SELECT * FROM `character`, `font` WHERE `font`.`id` = `character`.`font_id`"#
939 /// );
940 /// assert_eq!(
941 /// query.to_string(PostgresQueryBuilder),
942 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
943 /// );
944 /// assert_eq!(
945 /// query.to_string(SqliteQueryBuilder),
946 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
947 /// );
948 /// ```
949 pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self
950 where
951 R: IntoTableRef,
952 {
953 self.from_from(tbl_ref.into_table_ref())
954 }
955
956 /// Shorthand for selecting from a constant value list.
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 FOR SHARE"#
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}