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