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