Struct starfish_core::migrator::prelude::SelectStatement
source · [−]pub struct SelectStatement { /* private fields */ }
Expand description
Select rows from an existing table
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.left_join(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.and_where(Expr::col(Char::SizeW).is_in(vec![3, 4]))
.and_where(Expr::col(Char::Character).like("A%"))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
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%'"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
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%'"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
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%'"#
);
Implementations
sourceimpl SelectStatement
impl SelectStatement
sourcepub fn new() -> SelectStatement
pub fn new() -> SelectStatement
Construct a new SelectStatement
sourcepub fn take(&mut self) -> SelectStatement
pub fn take(&mut self) -> SelectStatement
Take the ownership of data in the current SelectStatement
sourcepub fn conditions<T, F>(
&mut self,
b: bool,
if_true: T,
if_false: F
) -> &mut SelectStatement where
T: FnOnce(&mut SelectStatement),
F: FnOnce(&mut SelectStatement),
pub fn conditions<T, F>(
&mut self,
b: bool,
if_true: T,
if_false: F
) -> &mut SelectStatement where
T: FnOnce(&mut SelectStatement),
F: FnOnce(&mut SelectStatement),
A shorthand to express if … else … when constructing the select statement.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.conditions(
true,
|x| {
x.and_where(Expr::col(Char::FontId).eq(5));
},
|x| {
x.and_where(Expr::col(Char::FontId).eq(10));
},
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
);
sourcepub fn clear_selects(&mut self) -> &mut SelectStatement
pub fn clear_selects(&mut self) -> &mut SelectStatement
Clear the select list
sourcepub fn expr<T>(&mut self, expr: T) -> &mut SelectStatement where
T: Into<SelectExpr>,
pub fn expr<T>(&mut self, expr: T) -> &mut SelectStatement where
T: Into<SelectExpr>,
Add an expression to the select expression list.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.expr(Expr::val(42))
.expr(Expr::col(Char::Id).max())
.expr((1..10_i32).fold(Expr::value(0), |expr, i| expr.add(Expr::value(i))))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT 42, MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
);
sourcepub fn exprs<T, I>(&mut self, exprs: I) -> &mut SelectStatement where
T: Into<SelectExpr>,
I: IntoIterator<Item = T>,
pub fn exprs<T, I>(&mut self, exprs: I) -> &mut SelectStatement where
T: Into<SelectExpr>,
I: IntoIterator<Item = T>,
Add select expressions from vector of SelectExpr
.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.exprs(vec![
Expr::col(Char::Id).max(),
(1..10_i32).fold(Expr::value(0), |expr, i| expr.add(Expr::value(i))),
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
);
pub fn exprs_mut_for_each<F>(&mut self, func: F) where
F: FnMut(&mut SelectExpr),
sourcepub fn distinct(&mut self) -> &mut SelectStatement
pub fn distinct(&mut self) -> &mut SelectStatement
Select distinct
sourcepub fn column<C>(&mut self, col: C) -> &mut SelectStatement where
C: IntoColumnRef,
pub fn column<C>(&mut self, col: C) -> &mut SelectStatement where
C: IntoColumnRef,
Add a column to the select expression list.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.column(Char::Character)
.column(Char::SizeW)
.column(Char::SizeH)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "size_w", "size_h" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "size_w", "size_h" FROM "character""#
);
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.column((Char::Table, Char::Character))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`.`character` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character"."character" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character"."character" FROM "character""#
);
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.column((Alias::new("schema"), Char::Table, Char::Character))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `schema`.`character`.`character` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "schema"."character"."character" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "schema"."character"."character" FROM "character""#
);
pub fn table_column<T, C>(&mut self, t: T, c: C) -> &mut SelectStatement where
T: IntoIden,
C: IntoIden,
Please use the [SelectStatement::column
] with a tuple as [ColumnRef
]
sourcepub fn columns<T, I>(&mut self, cols: I) -> &mut SelectStatement where
T: IntoColumnRef,
I: IntoIterator<Item = T>,
pub fn columns<T, I>(&mut self, cols: I) -> &mut SelectStatement where
T: IntoColumnRef,
I: IntoIterator<Item = T>,
Select columns.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.columns(vec![Char::Character, Char::SizeW, Char::SizeH])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "size_w", "size_h" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "size_w", "size_h" FROM "character""#
);
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.from(Char::Table)
.columns(vec![
(Char::Table, Char::Character),
(Char::Table, Char::SizeW),
(Char::Table, Char::SizeH),
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`.`character`, `character`.`size_w`, `character`.`size_h` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
);
pub fn table_columns<T, C>(
&mut self,
cols: Vec<(T, C), Global>
) -> &mut SelectStatement where
T: IntoIden,
C: IntoIden,
Please use the [SelectStatement::columns
] with a tuple as [ColumnRef
]
sourcepub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut SelectStatement where
T: Into<SimpleExpr>,
A: IntoIden,
pub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut SelectStatement where
T: Into<SimpleExpr>,
A: IntoIden,
Select column.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.expr_as(Expr::col(Char::Character), Alias::new("C"))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` AS `C` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" AS "C" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" AS "C" FROM "character""#
);
pub fn expr_alias<T, A>(&mut self, expr: T, alias: A) -> &mut SelectStatement where
T: Into<SimpleExpr>,
A: IntoIden,
Please use the [SelectStatement::expr_as
] instead
sourcepub fn from<R>(&mut self, tbl_ref: R) -> &mut SelectStatement where
R: IntoTableRef,
pub fn from<R>(&mut self, tbl_ref: R) -> &mut SelectStatement where
R: IntoTableRef,
From table.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::FontSize)
.from(Char::Table)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `font_size` FROM `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "font_size" FROM "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "font_size" FROM "character""#
);
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::FontSize)
.from((Char::Table, Glyph::Table))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `font_size` FROM `character`.`glyph`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "font_size" FROM "character"."glyph""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "font_size" FROM "character"."glyph""#
);
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::FontSize)
.from((Alias::new("database"), Char::Table, Glyph::Table))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `font_size` FROM `database`.`character`.`glyph`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "font_size" FROM "database"."character"."glyph""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "font_size" FROM "database"."character"."glyph""#
);
sourcepub fn from_schema<S, T>(&mut self, schema: S, table: T) -> &mut SelectStatement where
S: 'static + IntoIden,
T: 'static + IntoIden,
👎 Deprecated since 0.9.0: Please use the [SelectStatement::from
] with a tuple as [TableRef
]
pub fn from_schema<S, T>(&mut self, schema: S, table: T) -> &mut SelectStatement where
S: 'static + IntoIden,
T: 'static + IntoIden,
Please use the [SelectStatement::from
] with a tuple as [TableRef
]
From schema.table.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::FontSize)
.from_schema(Char::Table, Glyph::Table)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `font_size` FROM `character`.`glyph`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "font_size" FROM "character"."glyph""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "font_size" FROM "character"."glyph""#
);
sourcepub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
pub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
From table with alias.
Examples
use sea_query::{tests_cfg::*, *};
let table_as: DynIden = SeaRc::new(Alias::new("char"));
let query = Query::select()
.from_as(Char::Table, table_as.clone())
.column((table_as.clone(), Char::Character))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `char`.`character` FROM `character` AS `char`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "char"."character" FROM "character" AS "char""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "char"."character" FROM "character" AS "char""#
);
use sea_query::{tests_cfg::*, *};
let table_as = Alias::new("alias");
let query = Query::select()
.from_as((Font::Table, Char::Table), table_as.clone())
.column((table_as, Char::Character))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `alias`.`character` FROM `font`.`character` AS `alias`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
);
pub fn from_alias<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
Please use the [SelectStatement::from_as
] instead
pub fn from_schema_as<S, T, A>(
&mut self,
schema: S,
table: T,
alias: A
) -> &mut SelectStatement where
S: 'static + IntoIden,
T: 'static + IntoIden,
A: IntoIden,
Please use the [SelectStatement::from_as
] with a tuple as [TableRef
]
sourcepub fn from_subquery<T>(
&mut self,
query: SelectStatement,
alias: T
) -> &mut SelectStatement where
T: IntoIden,
pub fn from_subquery<T>(
&mut self,
query: SelectStatement,
alias: T
) -> &mut SelectStatement where
T: IntoIden,
From sub-query.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.columns(vec![Glyph::Image])
.from_subquery(
Query::select()
.columns(vec![Glyph::Image, Glyph::Aspect])
.from(Glyph::Table)
.take(),
Alias::new("subglyph"),
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `image` FROM (SELECT `image`, `aspect` FROM `glyph`) AS `subglyph`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
);
sourcepub fn left_join<R, C>(
&mut self,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
pub fn left_join<R, C>(
&mut self,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
Left join.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.left_join(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.left_join(
Font::Table,
Condition::all()
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
)
.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
);
sourcepub fn inner_join<R, C>(
&mut self,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
pub fn inner_join<R, C>(
&mut self,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
Inner join.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.inner_join(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.inner_join(
Font::Table,
Condition::all()
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
)
.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
);
sourcepub fn join<R, C>(
&mut self,
join: JoinType,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
pub fn join<R, C>(
&mut self,
join: JoinType,
tbl_ref: R,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
C: IntoCondition,
Join with other table by JoinType
.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join(JoinType::RightJoin, Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join(
JoinType::RightJoin,
Font::Table,
Condition::all()
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
)
.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
);
sourcepub fn join_as<R, A, C>(
&mut self,
join: JoinType,
tbl_ref: R,
alias: A,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
C: IntoCondition,
pub fn join_as<R, A, C>(
&mut self,
join: JoinType,
tbl_ref: R,
alias: A,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
C: IntoCondition,
Join with other table by JoinType
, assigning an alias to the joined table.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join_as(
JoinType::RightJoin,
Font::Table,
Alias::new("f"),
Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id)
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `font`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "font"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join_as(
JoinType::RightJoin,
Font::Table,
Alias::new("f"),
Condition::all()
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.add(Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
)
.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
);
pub fn join_alias<R, A, C>(
&mut self,
join: JoinType,
tbl_ref: R,
alias: A,
condition: C
) -> &mut SelectStatement where
R: IntoTableRef,
A: IntoIden,
C: IntoCondition,
Please use the [SelectStatement::join_as
] instead
sourcepub fn join_subquery<T, C>(
&mut self,
join: JoinType,
query: SelectStatement,
alias: T,
condition: C
) -> &mut SelectStatement where
T: IntoIden,
C: IntoCondition,
pub fn join_subquery<T, C>(
&mut self,
join: JoinType,
query: SelectStatement,
alias: T,
condition: C
) -> &mut SelectStatement where
T: IntoIden,
C: IntoCondition,
Join with sub-query.
Examples
use sea_query::{*, tests_cfg::*};
let sub_glyph: DynIden = SeaRc::new(Alias::new("sub_glyph"));
let query = Query::select()
.column(Font::Name)
.from(Font::Table)
.join_subquery(
JoinType::LeftJoin,
Query::select().column(Glyph::Id).from(Glyph::Table).take(),
sub_glyph.clone(),
Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id)
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Font::Name)
.from(Font::Table)
.join_subquery(
JoinType::LeftJoin,
Query::select().column(Glyph::Id).from(Glyph::Table).take(),
sub_glyph.clone(),
Condition::all()
.add(Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id))
.add(Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id))
)
.to_string(MysqlQueryBuilder),
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`"#
);
sourcepub fn join_lateral<T, C>(
&mut self,
join: JoinType,
query: SelectStatement,
alias: T,
condition: C
) -> &mut SelectStatement where
T: IntoIden,
C: IntoCondition,
pub fn join_lateral<T, C>(
&mut self,
join: JoinType,
query: SelectStatement,
alias: T,
condition: C
) -> &mut SelectStatement where
T: IntoIden,
C: IntoCondition,
Join Lateral with sub-query. Not supported by SQLite.
Examples
use sea_query::{*, tests_cfg::*};
let sub_glyph: DynIden = SeaRc::new(Alias::new("sub_glyph"));
let query = Query::select()
.column(Font::Name)
.from(Font::Table)
.join_lateral(
JoinType::LeftJoin,
Query::select().column(Glyph::Id).from(Glyph::Table).take(),
sub_glyph.clone(),
Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id)
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "name" FROM "font" LEFT JOIN LATERAL (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
);
// Constructing chained join conditions
assert_eq!(
Query::select()
.column(Font::Name)
.from(Font::Table)
.join_lateral(
JoinType::LeftJoin,
Query::select().column(Glyph::Id).from(Glyph::Table).take(),
sub_glyph.clone(),
Condition::all()
.add(Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id))
.add(Expr::tbl(Font::Table, Font::Id).equals(sub_glyph.clone(), Glyph::Id))
)
.to_string(MysqlQueryBuilder),
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`"#
);
sourcepub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut SelectStatement where
T: IntoColumnRef,
I: IntoIterator<Item = T>,
pub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut SelectStatement where
T: IntoColumnRef,
I: IntoIterator<Item = T>,
Group by columns.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join(JoinType::RightJoin, Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.group_by_columns(vec![
Char::Character,
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
);
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join(JoinType::RightJoin, Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.group_by_columns(vec![
(Char::Table, Char::Character),
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
);
sourcepub fn group_by_col<T>(&mut self, col: T) -> &mut SelectStatement where
T: IntoColumnRef,
pub fn group_by_col<T>(&mut self, col: T) -> &mut SelectStatement where
T: IntoColumnRef,
Add a group by column.
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Char::Character)
.column((Font::Table, Font::Name))
.from(Char::Table)
.join(JoinType::RightJoin, Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id))
.group_by_col((Char::Table, Char::Character))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
);
pub fn group_by_table_columns<T, C>(
&mut self,
cols: Vec<(T, C), Global>
) -> &mut SelectStatement where
T: IntoIden,
C: IntoIden,
Please use the [SelectStatement::group_by_columns
] with a tuple as [ColumnRef
]
sourcepub fn add_group_by<I>(&mut self, expr: I) -> &mut SelectStatement where
I: IntoIterator<Item = SimpleExpr>,
pub fn add_group_by<I>(&mut self, expr: I) -> &mut SelectStatement where
I: IntoIterator<Item = SimpleExpr>,
Add group by expressions from vector of SelectExpr
.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.from(Char::Table)
.column(Char::Character)
.add_group_by(vec![
Expr::col(Char::SizeW).into(),
Expr::col(Char::SizeH).into(),
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` GROUP BY `size_w`, `size_h`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
);
sourcepub fn cond_having<C>(&mut self, condition: C) -> &mut SelectStatement where
C: IntoCondition,
pub fn cond_having<C>(&mut self, condition: C) -> &mut SelectStatement where
C: IntoCondition,
Having condition, expressed with [any!
] and [all!
].
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Glyph::Aspect)
.expr(Expr::col(Glyph::Image).max())
.from(Glyph::Table)
.group_by_columns(vec![
Glyph::Aspect,
])
.cond_having(
all![
Expr::tbl(Glyph::Table, Glyph::Aspect).is_in(vec![3, 4]),
any![
Expr::tbl(Glyph::Table, Glyph::Image).like("A%"),
Expr::tbl(Glyph::Table, Glyph::Image).like("B%")
]
]
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
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%')"#
);
sourcepub fn and_having(&mut self, other: SimpleExpr) -> &mut SelectStatement
pub fn and_having(&mut self, other: SimpleExpr) -> &mut SelectStatement
And having condition.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Glyph::Aspect)
.expr(Expr::col(Glyph::Image).max())
.from(Glyph::Table)
.group_by_columns(vec![
Glyph::Aspect,
])
.and_having(Expr::col(Glyph::Aspect).gt(2))
.cond_having(Expr::col(Glyph::Aspect).lt(8))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2 AND `aspect` < 8"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
);
sourcepub fn or_having(&mut self, other: SimpleExpr) -> &mut SelectStatement
👎 Deprecated since 0.12.0: Please use [ConditionalStatement::cond_having
]. Calling or_having
after and_having
will panic.
pub fn or_having(&mut self, other: SimpleExpr) -> &mut SelectStatement
Please use [ConditionalStatement::cond_having
]. Calling or_having
after and_having
will panic.
Or having condition. Please use cond_having
instead.
Calling or_having
after and_having
will panic.
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Glyph::Aspect)
.expr(Expr::col(Glyph::Image).max())
.from(Glyph::Table)
.group_by_columns(vec![
Glyph::Aspect,
])
.or_having(Expr::col(Glyph::Aspect).gt(2))
.or_having(Expr::col(Glyph::Aspect).lt(8))
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2 OR `aspect` < 8"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 OR "aspect" < 8"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 OR "aspect" < 8"#
);
sourcepub fn limit(&mut self, limit: u64) -> &mut SelectStatement
pub fn limit(&mut self, limit: u64) -> &mut SelectStatement
Limit the number of returned rows.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Glyph::Aspect)
.from(Glyph::Table)
.limit(10)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `aspect` FROM `glyph` LIMIT 10"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
);
sourcepub fn reset_limit(&mut self) -> &mut SelectStatement
pub fn reset_limit(&mut self) -> &mut SelectStatement
Reset limit
sourcepub fn offset(&mut self, offset: u64) -> &mut SelectStatement
pub fn offset(&mut self, offset: u64) -> &mut SelectStatement
Offset number of returned rows.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Glyph::Aspect)
.from(Glyph::Table)
.limit(10)
.offset(10)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `aspect` FROM `glyph` LIMIT 10 OFFSET 10"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
);
sourcepub fn reset_offset(&mut self) -> &mut SelectStatement
pub fn reset_offset(&mut self) -> &mut SelectStatement
Reset offset
sourcepub fn lock(&mut self, lock_type: LockType) -> &mut SelectStatement
pub fn lock(&mut self, lock_type: LockType) -> &mut SelectStatement
Row locking (if supported).
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(5))
.lock(LockType::Exclusive)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);
Shared row locking (if supported).
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(5))
.lock_shared()
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR SHARE"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR SHARE"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);
sourcepub fn lock_exclusive(&mut self) -> &mut SelectStatement
pub fn lock_exclusive(&mut self) -> &mut SelectStatement
Exclusive row locking (if supported).
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(5))
.lock_exclusive()
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);
sourcepub fn union(
&mut self,
union_type: UnionType,
query: SelectStatement
) -> &mut SelectStatement
pub fn union(
&mut self,
union_type: UnionType,
query: SelectStatement
) -> &mut SelectStatement
Union with another SelectStatement that must have the same selected fields.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(5))
.union(UnionType::All, Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(4))
.to_owned()
)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL SELECT `character` FROM `character` WHERE `font_id` = 4"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4"#
);
sourcepub fn unions<T>(&mut self, unions: T) -> &mut SelectStatement where
T: IntoIterator<Item = (UnionType, SelectStatement)>,
pub fn unions<T>(&mut self, unions: T) -> &mut SelectStatement where
T: IntoIterator<Item = (UnionType, SelectStatement)>,
Union with multiple SelectStatement that must have the same selected fields.
Examples
use sea_query::{tests_cfg::*, *};
let query = Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(5))
.unions(vec![
(UnionType::All, Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(4))
.to_owned()),
(UnionType::Distinct, Query::select()
.column(Char::Character)
.from(Char::Table)
.and_where(Expr::col(Char::FontId).eq(3))
.to_owned()),
])
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
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"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
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"#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
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"#
);
sourcepub fn with(self, clause: WithClause) -> WithQuery
pub fn with(self, clause: WithClause) -> WithQuery
Create a WithQuery by specifying a WithClause to execute this query with.
Examples
use sea_query::{*, IntoCondition, IntoIden, tests_cfg::*};
let base_query = SelectStatement::new()
.column(Alias::new("id"))
.expr(Expr::val(1i32))
.column(Alias::new("next"))
.column(Alias::new("value"))
.from(Alias::new("table"))
.to_owned();
let cte_referencing = SelectStatement::new()
.column(Alias::new("id"))
.expr(Expr::col(Alias::new("depth")).add(1i32))
.column(Alias::new("next"))
.column(Alias::new("value"))
.from(Alias::new("table"))
.join(
JoinType::InnerJoin,
Alias::new("cte_traversal"),
Expr::tbl(Alias::new("cte_traversal"), Alias::new("next")).equals(Alias::new("table"), Alias::new("id")).into_condition()
)
.to_owned();
let common_table_expression = CommonTableExpression::new()
.query(
base_query.clone().union(UnionType::All, cte_referencing).to_owned()
)
.column(Alias::new("id"))
.column(Alias::new("depth"))
.column(Alias::new("next"))
.column(Alias::new("value"))
.table_name(Alias::new("cte_traversal"))
.to_owned();
let select = SelectStatement::new()
.column(ColumnRef::Asterisk)
.from(Alias::new("cte_traversal"))
.to_owned();
let with_clause = WithClause::new()
.recursive(true)
.cte(common_table_expression)
.cycle(Cycle::new_from_expr_set_using(SimpleExpr::Column(ColumnRef::Column(Alias::new("id").into_iden())), Alias::new("looped"), Alias::new("traversal_path")))
.to_owned();
let query = select.with(with_clause).to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
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`"#
);
assert_eq!(
query.to_string(PostgresQueryBuilder),
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""#
);
assert_eq!(
query.to_string(SqliteQueryBuilder),
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""#
);
sourceimpl SelectStatement
impl SelectStatement
pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut SelectStatement where
T: IntoColumnRef,
pub fn order_by_tbl<T, C>(
&mut self,
table: T,
col: C,
order: Order
) -> &mut SelectStatement where
T: IntoIden,
C: IntoIden,
Please use the [OrderedStatement::order_by
] with a tuple as [ColumnRef
]
pub fn order_by_expr(
&mut self,
expr: SimpleExpr,
order: Order
) -> &mut SelectStatement
pub fn order_by_customs<T>(
&mut self,
cols: Vec<(T, Order), Global>
) -> &mut SelectStatement where
T: ToString,
pub fn order_by_columns<T>(
&mut self,
cols: Vec<(T, Order), Global>
) -> &mut SelectStatement where
T: IntoColumnRef,
pub fn order_by_table_columns<T, C>(
&mut self,
cols: Vec<(T, C, Order), Global>
) -> &mut SelectStatement where
T: IntoIden,
C: IntoIden,
Please use the [OrderedStatement::order_by_columns
] with a tuple as [ColumnRef
]
sourceimpl SelectStatement
impl SelectStatement
pub fn and_where(&mut self, other: SimpleExpr) -> &mut SelectStatement
pub fn and_where_option(
&mut self,
other: Option<SimpleExpr>
) -> &mut SelectStatement
pub fn or_where(&mut self, other: SimpleExpr) -> &mut SelectStatement
Please use [ConditionalStatement::cond_where
]. Calling or_where
after and_where
will panic.
pub fn cond_where<C>(&mut self, condition: C) -> &mut SelectStatement where
C: IntoCondition,
Trait Implementations
sourceimpl Clone for SelectStatement
impl Clone for SelectStatement
sourcefn clone(&self) -> SelectStatement
fn clone(&self) -> SelectStatement
Returns a copy of the value. Read more
1.0.0 · sourcefn clone_from(&mut self, source: &Self)
fn clone_from(&mut self, source: &Self)
Performs copy-assignment from source
. Read more
sourceimpl ConditionalStatement for SelectStatement
impl ConditionalStatement for SelectStatement
fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut SelectStatement
sourcefn cond_where<C>(&mut self, condition: C) -> &mut SelectStatement where
C: IntoCondition,
fn cond_where<C>(&mut self, condition: C) -> &mut SelectStatement where
C: IntoCondition,
Where condition, expressed with any
and all
.
Calling cond_where
multiple times will conjoin them.
Calling or_where
after cond_where
will panic. Read more
sourcefn and_where(&mut self, other: SimpleExpr) -> &mut Self
fn and_where(&mut self, other: SimpleExpr) -> &mut Self
And where condition. This cannot be mixed with ConditionalStatement::or_where
.
Calling or_where
after and_where
will panic. Read more
sourcefn and_where_option(&mut self, other: Option<SimpleExpr>) -> &mut Self
fn and_where_option(&mut self, other: Option<SimpleExpr>) -> &mut Self
Optional and where, short hand for if c.is_some() q.and_where(c)
. Read more
sourcefn or_where(&mut self, other: SimpleExpr) -> &mut Self
fn or_where(&mut self, other: SimpleExpr) -> &mut Self
Please use [ConditionalStatement::cond_where
]. Calling or_where
after and_where
will panic.
Or where condition. This cannot be mixed with ConditionalStatement::and_where
.
Calling or_where
after and_where
will panic. Read more
sourceimpl Debug for SelectStatement
impl Debug for SelectStatement
sourceimpl Default for SelectStatement
impl Default for SelectStatement
sourcefn default() -> SelectStatement
fn default() -> SelectStatement
Returns the “default value” for a type. Read more
sourceimpl OrderedStatement for SelectStatement
impl OrderedStatement for SelectStatement
fn add_order_by(&mut self, order: OrderExpr) -> &mut SelectStatement
sourcefn order_by<T>(&mut self, col: T, order: Order) -> &mut Self where
T: IntoColumnRef,
fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self where
T: IntoColumnRef,
Order by column. Read more
sourcefn order_by_tbl<T, C>(&mut self, table: T, col: C, order: Order) -> &mut Self where
T: IntoIden,
C: IntoIden,
fn order_by_tbl<T, C>(&mut self, table: T, col: C, order: Order) -> &mut Self where
T: IntoIden,
C: IntoIden,
Please use the [OrderedStatement::order_by
] with a tuple as [ColumnRef
]
sourcefn order_by_expr(&mut self, expr: SimpleExpr, order: Order) -> &mut Self
fn order_by_expr(&mut self, expr: SimpleExpr, order: Order) -> &mut Self
Order by SimpleExpr
.
sourcefn order_by_customs<T>(&mut self, cols: Vec<(T, Order), Global>) -> &mut Self where
T: ToString,
fn order_by_customs<T>(&mut self, cols: Vec<(T, Order), Global>) -> &mut Self where
T: ToString,
Order by custom string.
sourcefn order_by_columns<T>(&mut self, cols: Vec<(T, Order), Global>) -> &mut Self where
T: IntoColumnRef,
fn order_by_columns<T>(&mut self, cols: Vec<(T, Order), Global>) -> &mut Self where
T: IntoColumnRef,
Order by vector of columns.
sourcefn order_by_table_columns<T, C>(
&mut self,
cols: Vec<(T, C, Order), Global>
) -> &mut Self where
T: IntoIden,
C: IntoIden,
fn order_by_table_columns<T, C>(
&mut self,
cols: Vec<(T, C, Order), Global>
) -> &mut Self where
T: IntoIden,
C: IntoIden,
Please use the [OrderedStatement::order_by_columns
] with a tuple as [ColumnRef
]
sourcefn order_by_with_nulls<T>(
&mut self,
col: T,
order: Order,
nulls: NullOrdering
) -> &mut Self where
T: IntoColumnRef,
fn order_by_with_nulls<T>(
&mut self,
col: T,
order: Order,
nulls: NullOrdering
) -> &mut Self where
T: IntoColumnRef,
Order by column with nulls order option. Read more
sourcefn order_by_expr_with_nulls(
&mut self,
expr: SimpleExpr,
order: Order,
nulls: NullOrdering
) -> &mut Self
fn order_by_expr_with_nulls(
&mut self,
expr: SimpleExpr,
order: Order,
nulls: NullOrdering
) -> &mut Self
Order by SimpleExpr
with nulls order option.
sourcefn order_by_customs_with_nulls<T>(
&mut self,
cols: Vec<(T, Order, NullOrdering), Global>
) -> &mut Self where
T: ToString,
fn order_by_customs_with_nulls<T>(
&mut self,
cols: Vec<(T, Order, NullOrdering), Global>
) -> &mut Self where
T: ToString,
Order by custom string with nulls order option.
sourcefn order_by_columns_with_nulls<T>(
&mut self,
cols: Vec<(T, Order, NullOrdering), Global>
) -> &mut Self where
T: IntoColumnRef,
fn order_by_columns_with_nulls<T>(
&mut self,
cols: Vec<(T, Order, NullOrdering), Global>
) -> &mut Self where
T: IntoColumnRef,
Order by vector of columns with nulls order option.
sourceimpl QueryStatementBuilder for SelectStatement
impl QueryStatementBuilder for SelectStatement
sourcefn build_collect_any_into(
&self,
query_builder: &dyn QueryBuilder,
sql: &mut SqlWriter,
collector: &mut dyn FnMut(Value)
)
fn build_collect_any_into(
&self,
query_builder: &dyn QueryBuilder,
sql: &mut SqlWriter,
collector: &mut dyn FnMut(Value)
)
Build corresponding SQL statement into the SqlWriter for certain database backend and collect query parameters
fn into_sub_query_statement(self) -> SubQueryStatement
sourcefn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values)
fn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values)
Build corresponding SQL statement for certain database backend and collect query parameters into a vector
sourcefn build_collect_any(
&self,
query_builder: &dyn QueryBuilder,
collector: &mut dyn FnMut(Value)
) -> String
fn build_collect_any(
&self,
query_builder: &dyn QueryBuilder,
collector: &mut dyn FnMut(Value)
) -> String
Build corresponding SQL statement for certain database backend and collect query parameters
sourceimpl QueryStatementWriter for SelectStatement
impl QueryStatementWriter for SelectStatement
sourcefn build_collect<T>(
&self,
query_builder: T,
collector: &mut dyn FnMut(Value)
) -> String where
T: QueryBuilder,
fn build_collect<T>(
&self,
query_builder: T,
collector: &mut dyn FnMut(Value)
) -> String where
T: QueryBuilder,
Build corresponding SQL statement for certain database backend and collect query parameters
Examples
use sea_query::{*, tests_cfg::*};
let query = Query::select()
.column(Glyph::Aspect)
.from(Glyph::Table)
.and_where(Expr::expr(Expr::col(Glyph::Aspect).if_null(0)).gt(2))
.order_by(Glyph::Image, Order::Desc)
.order_by_tbl(Glyph::Table, Glyph::Aspect, Order::Asc)
.to_owned();
assert_eq!(
query.to_string(MysqlQueryBuilder),
r#"SELECT `aspect` FROM `glyph` WHERE IFNULL(`aspect`, 0) > 2 ORDER BY `image` DESC, `glyph`.`aspect` ASC"#
);
let mut params = Vec::new();
let mut collector = |v| params.push(v);
assert_eq!(
query.build_collect(MysqlQueryBuilder, &mut collector),
r#"SELECT `aspect` FROM `glyph` WHERE IFNULL(`aspect`, ?) > ? ORDER BY `image` DESC, `glyph`.`aspect` ASC"#
);
assert_eq!(
params,
vec![Value::Int(Some(0)), Value::Int(Some(2))]
);
sourcefn to_string<T>(&self, query_builder: T) -> String where
T: QueryBuilder,
fn to_string<T>(&self, query_builder: T) -> String where
T: QueryBuilder,
Build corresponding SQL statement for certain database backend and return SQL string Read more
sourceimpl StatementBuilder for SelectStatement
impl StatementBuilder for SelectStatement
sourcefn build(&self, db_backend: &DatabaseBackend) -> Statement
fn build(&self, db_backend: &DatabaseBackend) -> Statement
Method to call in order to build a Statement
Auto Trait Implementations
impl !RefUnwindSafe for SelectStatement
impl Send for SelectStatement
impl Sync for SelectStatement
impl Unpin for SelectStatement
impl !UnwindSafe for SelectStatement
Blanket Implementations
sourceimpl<T> BorrowMut<T> for T where
T: ?Sized,
impl<T> BorrowMut<T> for T where
T: ?Sized,
const: unstable · sourcefn borrow_mut(&mut self) -> &mut T
fn borrow_mut(&mut self) -> &mut T
Mutably borrows from an owned value. Read more
sourceimpl<T> Instrument for T
impl<T> Instrument for T
sourcefn instrument(self, span: Span) -> Instrumented<Self>ⓘNotable traits for Instrumented<T>impl<T> Future for Instrumented<T> where
T: Future, type Output = <T as Future>::Output;
fn instrument(self, span: Span) -> Instrumented<Self>ⓘNotable traits for Instrumented<T>impl<T> Future for Instrumented<T> where
T: Future, type Output = <T as Future>::Output;
T: Future, type Output = <T as Future>::Output;
sourcefn in_current_span(self) -> Instrumented<Self>ⓘNotable traits for Instrumented<T>impl<T> Future for Instrumented<T> where
T: Future, type Output = <T as Future>::Output;
fn in_current_span(self) -> Instrumented<Self>ⓘNotable traits for Instrumented<T>impl<T> Future for Instrumented<T> where
T: Future, type Output = <T as Future>::Output;
T: Future, type Output = <T as Future>::Output;
sourceimpl<T> ToOwned for T where
T: Clone,
impl<T> ToOwned for T where
T: Clone,
type Owned = T
type Owned = T
The resulting type after obtaining ownership.
sourcefn clone_into(&self, target: &mut T)
fn clone_into(&self, target: &mut T)
toowned_clone_into
)Uses borrowed data to replace owned data, usually by cloning. Read more
impl<V, T> VZip<V> for T where
V: MultiLane<T>,
impl<V, T> VZip<V> for T where
V: MultiLane<T>,
fn vzip(self) -> V
sourceimpl<T> WithSubscriber for T
impl<T> WithSubscriber for T
sourcefn with_subscriber<S>(self, subscriber: S) -> WithDispatch<Self>ⓘNotable traits for WithDispatch<T>impl<T> Future for WithDispatch<T> where
T: Future, type Output = <T as Future>::Output;
where
S: Into<Dispatch>,
fn with_subscriber<S>(self, subscriber: S) -> WithDispatch<Self>ⓘNotable traits for WithDispatch<T>impl<T> Future for WithDispatch<T> where
T: Future, type Output = <T as Future>::Output;
where
S: Into<Dispatch>,
T: Future, type Output = <T as Future>::Output;
Attaches the provided Subscriber
to this type, returning a
WithDispatch
wrapper. Read more
sourcefn with_current_subscriber(self) -> WithDispatch<Self>ⓘNotable traits for WithDispatch<T>impl<T> Future for WithDispatch<T> where
T: Future, type Output = <T as Future>::Output;
fn with_current_subscriber(self) -> WithDispatch<Self>ⓘNotable traits for WithDispatch<T>impl<T> Future for WithDispatch<T> where
T: Future, type Output = <T as Future>::Output;
T: Future, type Output = <T as Future>::Output;
Attaches the current default Subscriber
to this type, returning a
WithDispatch
wrapper. Read more