Struct SelectStatement 

pub struct SelectStatement { /* private fields */ }

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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
    .and_where(Expr::col(Char::Character).like("A%"))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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(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

impl SelectStatement

pub fn new() -> Self

Construct a new SelectStatement

pub fn take(&mut self) -> Self

Take the ownership of data in the current SelectStatement

pub fn conditions<T, F>( &mut self, b: bool, if_true: T, if_false: F, ) -> &mut Self

where T: FnOnce(&mut Self), F: FnOnce(&mut Self),

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(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5"#
);
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"#
);

pub fn apply_if<T, F>(&mut self, val: Option<T>, if_some: F) -> &mut Self

where Self: Sized, F: FnOnce(&mut Self, T),

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)
    .apply_if(Some(5), |q, v| {
        q.and_where(Expr::col(Char::FontId).eq(v));
    })
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
);

pub fn apply<F>(&mut self, func: F) -> &mut Self

where F: FnOnce(&mut Self),

Construct part of the select statement in another function.

Examples

use sea_query::{tests_cfg::*, *};

let common_expr = |q: &mut SelectStatement| {
    q.and_where(Expr::col(Char::FontId).eq(5));
};

let query = Query::select()
    .column(Char::Character)
    .from(Char::Table)
    .apply(common_expr)
    .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"#
);

pub fn clear_selects(&mut self) -> &mut Self

Clear the select list

pub fn expr<T>(&mut self, expr: T) -> &mut Self

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(i)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT 42, MAX([id]), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM [character]"#
);
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""#
);

pub fn exprs<T, I>(&mut self, exprs: I) -> &mut Self

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([
        Expr::col(Char::Id).max(),
        (1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT MAX([id]), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM [character]"#
);
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),

pub fn distinct(&mut self) -> &mut Self

Select distinct

pub fn distinct_on<T, I>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = T>,

Select distinct on for POSTGRES ONLY

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .distinct_on([Char::Character])
    .column(Char::Character)
    .column(Char::SizeW)
    .column(Char::SizeH)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT DISTINCT ON ("character") "character", "size_w", "size_h" FROM "character""#
)

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .distinct_on(vec![(Char::Table, Char::Character)])
    .column(Char::Character)
    .column(Char::SizeW)
    .column(Char::SizeH)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT DISTINCT ON ("character"."character") "character", "size_w", "size_h" FROM "character""#
)

use sea_query::{tests_cfg::*, *};

let distinct_cols: Vec<Character> = vec![];
let query = Query::select()
    .from(Char::Table)
    .distinct_on(distinct_cols)
    .column(Char::Character)
    .column(Char::SizeW)
    .column(Char::SizeH)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "size_w", "size_h" FROM "character""#
)

pub fn column<C>(&mut self, col: C) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [character], [size_w], [size_h] FROM [character]"#
);
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(MsSqlQueryBuilder),
    r#"SELECT [character].[character] FROM [character]"#
);
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(("schema", Char::Table, Char::Character))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [schema].[character].[character] FROM [character]"#
);
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 columns<T, I>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = T>,

Select columns.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .columns([Char::Character, Char::SizeW, Char::SizeH])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [size_w], [size_h] FROM [character]"#
);
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([
        (Char::Table, Char::Character),
        (Char::Table, Char::SizeW),
        (Char::Table, Char::SizeH),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character].[character], [character].[size_w], [character].[size_h] FROM [character]"#
);
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 expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut Self

where T: Into<Expr>, A: IntoIden,

Select column.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_as(Expr::col(Char::Character), "C")
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] AS [C] FROM [character]"#
);
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_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self

where T: Into<Expr>,

Select column with window function.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_window(
        Expr::col(Char::Character),
        WindowStatement::partition_by(Char::FontSize),
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] OVER ( PARTITION BY [font_size] ) FROM [character]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` OVER ( PARTITION BY `font_size` ) FROM `character`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
);

pub fn expr_window_as<T, A>( &mut self, expr: T, window: WindowStatement, alias: A, ) -> &mut Self

where T: Into<Expr>, A: IntoIden,

Select column with window function and label.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_window_as(
        Expr::col(Char::Character),
        WindowStatement::partition_by(Char::FontSize),
        "C",
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] OVER ( PARTITION BY [font_size] ) AS [C] FROM [character]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
);

pub fn expr_window_name<T, W>(&mut self, expr: T, window: W) -> &mut Self

where T: Into<Expr>, W: IntoIden,

Select column with window name.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_window_name(Expr::col(Char::Character), "w")
    .window("w", WindowStatement::partition_by(Char::FontSize))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] OVER [w] FROM [character] WINDOW [w] AS (PARTITION BY [font_size])"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` OVER `w` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);

pub fn expr_window_name_as<T, W, A>( &mut self, expr: T, window: W, alias: A, ) -> &mut Self

where T: Into<Expr>, A: IntoIden, W: IntoIden,

Select column with window name and label.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_window_name_as(Expr::col(Char::Character), "w", "C")
    .window("w", WindowStatement::partition_by(Char::FontSize))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] OVER [w] AS [C] FROM [character] WINDOW [w] AS (PARTITION BY [font_size])"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);

pub fn into_table(&mut self, into_table: SelectInto) -> &mut Self

Target table for SELECT INTO clause

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .column(Char::Character)
    .into_table(SelectInto::table("character_copy").modifier(SelectIntoTableModifier::Unlogged))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" INTO UNLOGGED TABLE "character_copy" FROM "character""#
);

let query = Query::select()
    .from(Char::Table)
    .column(Char::Character)
    .into_table(
        SelectInto::table("character_temp").modifier(SelectIntoTableModifier::Temporary),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" INTO TEMPORARY TABLE "character_temp" FROM "character""#
);

pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [font_size] FROM [character]"#
);
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(MsSqlQueryBuilder),
    r#"SELECT [font_size] FROM [character].[glyph]"#
);
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(("database", Char::Table, Glyph::Table))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [font_size] FROM [database].[character].[glyph]"#
);
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""#
);

If you specify from multiple times, the resulting query will have multiple from clauses. You can perform an ‘old-school’ join this way.

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(Expr::asterisk())
    .from(Char::Table)
    .from(Font::Table)
    .and_where(Expr::col((Font::Table, Font::Id)).equals((Char::Table, Char::FontId)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT * FROM [character], [font] WHERE [font].[id] = [character].[font_id]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT * FROM `character`, `font` WHERE `font`.`id` = `character`.`font_id`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
);

pub fn from_values<I, V, A>(&mut self, value_tuples: I, alias: A) -> &mut Self

where I: IntoIterator<Item = V>, V: IntoValueTuple, A: IntoIden,

Shorthand for selecting from a constant value list.

Panics

Panics on an empty values list.

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .expr(Expr::asterisk())
    .from_values([(1, "hello"), (2, "world")], "x")
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS [x]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT * FROM (VALUES ROW(1, 'hello'), ROW(2, 'world')) AS `x`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
);

pub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut Self

where R: IntoTableRef, A: IntoIden,

From table with alias.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from_as(Char::Table, "char")
    .column(("char", Char::Character))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [char].[character] FROM [character] AS [char]"#
);
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::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .from_as((Font::Table, Char::Table), "alias")
    .column(("alias", Char::Character))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [alias].[character] FROM [font].[character] AS [alias]"#
);
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""#
);
assert_eq!(
    query.audit().unwrap().selects(),
    [TableName(Some(Font::Table.into()), Char::Table.into_iden())]
);

pub fn from_subquery<T>( &mut self, query: SelectStatement, alias: T, ) -> &mut Self

where T: IntoIden,

From sub-query.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .columns([Glyph::Image])
    .from_subquery(
        Query::select()
            .columns([Glyph::Image, Glyph::Aspect])
            .from(Glyph::Table)
            .take(),
        "subglyph",
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [image] FROM (SELECT [image], [aspect] FROM [glyph]) AS [subglyph]"#
);
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""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Glyph::Table.into_iden()]
);

pub fn from_function<T>(&mut self, func: FunctionCall, alias: T) -> &mut Self

where T: IntoIden,

From function call.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .column(Asterisk)
    .from_function(Func::random(), "func")
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT * FROM RANDOM() AS [func]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT * FROM RAND() AS `func`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT * FROM RANDOM() AS "func""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT * FROM RANDOM() AS "func""#
);

pub fn from_clear(&mut self) -> &mut Self

Clears all current from clauses.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .column(Asterisk)
    .from(Char::Table)
    .from_clear()
    .from(Font::Table)
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT * FROM [font]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT * FROM `font`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT * FROM "font""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT * FROM "font""#
);

pub fn cross_join<R>(&mut self, tbl_ref: R) -> &mut Self

where R: IntoTableRef,

Cross join.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .cross_join(Font::Table)
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] CROSS JOIN [font]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `font`.`name` FROM `character` CROSS JOIN `font`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden(), Font::Table.into_iden()]
);

pub fn left_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self

where R: IntoTableRef, C: IntoCondition,

Left join.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] LEFT JOIN [font] ON [character].[font_id] = [font].[id]"#
);
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""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden(), Font::Table.into_iden()]
);

// Constructing chained join conditions
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .left_join(
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] LEFT JOIN [font] ON [character].[font_id] = [font].[id] AND [character].[font_id] = [font].[id]"#
);
assert_eq!(
    query.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`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "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" AND "character"."font_id" = "font"."id""#
);

pub fn right_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self

where R: IntoTableRef, C: IntoCondition,

Right join.

Examples

use sea_query::{*, tests_cfg::*};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .right_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] RIGHT JOIN [font] ON [character].[font_id] = [font].[id]"#
);
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
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .right_join(
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] RIGHT JOIN [font] ON [character].[font_id] = [font].[id] AND [character].[font_id] = [font].[id]"#
);
assert_eq!(
    query.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`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "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" AND "character"."font_id" = "font"."id""#
);

pub fn inner_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self

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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] INNER JOIN [font] ON [character].[font_id] = [font].[id]"#
);
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
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .inner_join(
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] INNER JOIN [font] ON [character].[font_id] = [font].[id] AND [character].[font_id] = [font].[id]"#
);
assert_eq!(
    query.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`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "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" AND "character"."font_id" = "font"."id""#
);

pub fn full_outer_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self

where R: IntoTableRef, C: IntoCondition,

Full outer join.

Examples

use sea_query::{*, tests_cfg::*};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .full_outer_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
);

// Constructing chained join conditions
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .full_outer_join(
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
);

pub fn straight_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self

where R: IntoTableRef, C: IntoCondition,

Straight join. MySQL only.

Examples

use sea_query::{*, tests_cfg::*};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .straight_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id`"#
);

// Constructing chained join conditions
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .straight_join(
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
);

pub fn join<R, C>( &mut self, join: JoinType, tbl_ref: R, condition: C, ) -> &mut Self

where R: IntoTableRef, C: IntoCondition,

Join with other table by JoinType.

If JoinType is CrossJoin, the condition will be ignored.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] RIGHT JOIN [font] ON [character].[font_id] = [font].[id]"#
);
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""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden(), Font::Table.into_iden()]
);

// Constructing chained join conditions
let query = Query::select()
        .column(Char::Character)
        .column((Font::Table, Font::Name))
        .from(Char::Table)
        .join(
            JoinType::RightJoin,
            Font::Table,
            all![
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
            ]
        )
        .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] RIGHT JOIN [font] ON [character].[font_id] = [font].[id] AND [character].[font_id] = [font].[id]"#
);
assert_eq!(
    query.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`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "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" AND "character"."font_id" = "font"."id""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden(), Font::Table.into_iden()]
);

pub fn join_as<R, A, C>( &mut self, join: JoinType, tbl_ref: R, alias: A, condition: C, ) -> &mut Self

where R: IntoTableRef, A: IntoIden, C: IntoCondition,

Join with other table by JoinType, assigning an alias to the joined table.

If JoinType is CrossJoin, the condition will be ignored.

Examples

use sea_query::{*, tests_cfg::*};

let query = Query::select()
    .column(Char::Character)
    .column(("f", Font::Name))
    .from(Char::Table)
    .join_as(
        JoinType::RightJoin,
        Font::Table,
        "f",
        Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id))
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [f].[name] FROM [character] RIGHT JOIN [font] AS [f] ON [character].[font_id] = [f].[id]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
);

// Constructing chained join conditions
assert_eq!(
    Query::select()
        .column(Char::Character)
        .column(("f", Font::Name))
        .from(Char::Table)
        .join_as(
            JoinType::RightJoin,
            Font::Table,
            "f",
            Condition::all()
                .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
                .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
        )
        .to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id` AND `character`.`font_id` = `f`.`id`"#
);

pub fn join_subquery<T, C>( &mut self, join: JoinType, query: SelectStatement, alias: T, condition: C, ) -> &mut Self

where T: IntoIden, C: IntoCondition,

Join with sub-query.

Examples

use sea_query::{tests_cfg::*, audit::*, *};

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",
        Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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(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""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Font::Table.into_iden(), Glyph::Table.into_iden()]
);

// 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",
            Condition::all()
                .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
                .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", 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`"#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Font::Table.into_iden(), Glyph::Table.into_iden()]
);

pub fn join_lateral<T, C>( &mut self, join: JoinType, query: SelectStatement, alias: T, condition: C, ) -> &mut Self

where T: IntoIden, C: IntoCondition,

Join Lateral with sub-query. Not supported by SQLite.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

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",
        Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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(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""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Font::Table.into_iden(), Glyph::Table.into_iden()]
);

// 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",
            Condition::all()
                .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
                .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", 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`"#
);

pub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut Self

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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .group_by_columns([
        Char::Character,
    ])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character], [font].[name] FROM [character] RIGHT JOIN [font] ON [character].[font_id] = [font].[id] GROUP BY [character]"#
);
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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .group_by_columns([
        (Char::Table, Char::Character),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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(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_col<T>(&mut self, col: T) -> &mut Self

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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .group_by_col((Char::Table, Char::Character))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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(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 add_group_by<I>(&mut self, expr: I) -> &mut Self

where I: IntoIterator<Item = Expr>,

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([Expr::col(Char::SizeW).into(), Expr::col(Char::SizeH).into()])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] GROUP BY [size_w], [size_h]"#
);
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""#
);

pub fn cond_having<C>(&mut self, condition: C) -> &mut Self

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([
        Glyph::Aspect,
    ])
    .cond_having(
        all![
            Expr::col((Glyph::Table, Glyph::Aspect)).is_in([3, 4]),
            any![
                Expr::col((Glyph::Table, Glyph::Image)).like("A%"),
                Expr::col((Glyph::Table, Glyph::Image)).like("B%")
            ]
        ]
    )
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    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%')"#
);
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%')"#
);

pub fn and_having(&mut self, other: Expr) -> &mut Self

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([
        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(MsSqlQueryBuilder),
    r#"SELECT [aspect], MAX([image]) FROM [glyph] GROUP BY [aspect] HAVING [aspect] > 2 AND [aspect] < 8"#
);
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"#
);

pub fn limit(&mut self, limit: u64) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [aspect] FROM [glyph] ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"#
);
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"#
);

pub fn reset_limit(&mut self) -> &mut Self

Reset limit

pub fn offset(&mut self, offset: u64) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [aspect] FROM [glyph] ORDER BY (SELECT NULL) OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY"#
);
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"#
);

pub fn reset_offset(&mut self) -> &mut Self

Reset offset

pub fn lock(&mut self, type: LockType) -> &mut Self

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::Update)
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR UPDATE"#
);
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 "#
);

pub fn lock_with_tables<T, I>(&mut self, type: LockType, tables: I) -> &mut Self

where T: IntoTableRef, I: IntoIterator<Item = T>,

Row locking with tables (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_with_tables(LockType::Update, [Glyph::Table])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR UPDATE OF [glyph]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);

pub fn lock_with_behavior( &mut self, type: LockType, behavior: LockBehavior, ) -> &mut Self

Row locking with behavior (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_with_behavior(LockType::Update, LockBehavior::Nowait)
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR UPDATE NOWAIT"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE NOWAIT"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE NOWAIT"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);

pub fn lock_with_tables_behavior<T, I>( &mut self, type: LockType, tables: I, behavior: LockBehavior, ) -> &mut Self

where T: IntoTableRef, I: IntoIterator<Item = T>,

Row locking with tables and behavior (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_with_tables_behavior(LockType::Update, [Glyph::Table], LockBehavior::Nowait)
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR UPDATE OF [glyph] NOWAIT"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph` NOWAIT"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph" NOWAIT"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
);

pub fn lock_shared(&mut self) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR SHARE"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` FROM `character` WHERE `font_id` = 5 LOCK IN SHARE MODE"#
);
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 "#
);

pub fn lock_exclusive(&mut self) -> &mut Self

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(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 FOR UPDATE"#
);
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 "#
);

pub fn union( &mut self, union_type: UnionType, query: SelectStatement, ) -> &mut Self

Union with another SelectStatement that must have the same selected fields.

Examples

use sea_query::{audit::*, 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(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 UNION ALL SELECT [character] FROM [character] WHERE [font_id] = 4"#
);
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"#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden()]
);

pub fn unions<T: IntoIterator<Item = (UnionType, SelectStatement)>>( &mut self, unions: T, ) -> &mut Self

Union with multiple SelectStatement that must have the same selected fields.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::select()
    .column(Char::Character)
    .from(Char::Table)
    .and_where(Expr::col(Char::FontId).eq(5))
    .unions([
        (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(Glyph::Image)
            .from(Glyph::Table)
            .to_owned()),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] FROM [character] WHERE [font_id] = 5 UNION ALL SELECT [character] FROM [character] WHERE [font_id] = 4 UNION SELECT [image] FROM [glyph]"#
);
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 `image` FROM `glyph`)"#
);
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 "image" FROM "glyph")"#
);
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 "image" FROM "glyph""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden(), Glyph::Table.into_iden()]
);

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, audit::AuditTrait, tests_cfg::*};

let base_query = SelectStatement::new()
                    .column("id")
                    .expr(1i32)
                    .column("next")
                    .column("value")
                    .from(Task::Table)
                    .to_owned();

let cte_referencing = SelectStatement::new()
                            .column("id")
                            .expr(Expr::col("depth").add(1i32))
                            .column("next")
                            .column("value")
                            .from(Task::Table)
                            .join(
                                JoinType::InnerJoin,
                                "cte_traversal",
                                Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
                            )
                            .to_owned();

let common_table_expression = CommonTableExpression::new()
            .query(
                base_query.clone().union(UnionType::All, cte_referencing).to_owned()
            )
            .columns(["id", "depth", "next", "value"])
            .table_name("cte_traversal")
            .to_owned();

let select = SelectStatement::new()
        .column(Asterisk)
        .from("cte_traversal")
        .to_owned();

let with_clause = WithClause::new()
        .recursive(true)
        .cte(common_table_expression)
        .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
        .to_owned();

let query = select.with(with_clause).to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"WITH RECURSIVE [cte_traversal] ([id], [depth], [next], [value]) AS (SELECT [id], 1, [next], [value] FROM [task] UNION ALL SELECT [id], [depth] + 1, [next], [value] FROM [task] INNER JOIN [cte_traversal] ON [cte_traversal].[next] = [task].[id]) CYCLE [id] SET [looped] USING [traversal_path] SELECT * FROM [cte_traversal]"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Task::Table.into_iden()]
);

pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self

Create a Common Table Expression by specifying a CommonTableExpression or WithClause to execute this query with.

Examples

use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};

let base_query = SelectStatement::new()
                    .column("id")
                    .expr(1i32)
                    .column("next")
                    .column("value")
                    .from(Task::Table)
                    .to_owned();

let cte_referencing = SelectStatement::new()
                            .column("id")
                            .expr(Expr::col("depth").add(1i32))
                            .column("next")
                            .column("value")
                            .from(Task::Table)
                            .join(
                                JoinType::InnerJoin,
                                "cte_traversal",
                                Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
                            )
                            .to_owned();

let common_table_expression = CommonTableExpression::new()
            .query(
                base_query.clone().union(UnionType::All, cte_referencing).to_owned()
            )
            .columns(["id", "depth", "next", "value"])
            .table_name("cte_traversal")
            .to_owned();

let with_clause = WithClause::new()
        .recursive(true)
        .cte(common_table_expression)
        .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
        .to_owned();

let query = SelectStatement::new()
        .column(Asterisk)
        .from("cte_traversal")
        .with_cte(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 `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Task::Table.into_iden()]
);

pub fn window<A>(&mut self, name: A, window: WindowStatement) -> &mut Self

where A: IntoIden,

WINDOW

Examples:

use sea_query::{tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .expr_window_name_as(Expr::col(Char::Character), "w", "C")
    .window("w", WindowStatement::partition_by(Char::FontSize))
    .to_owned();

assert_eq!(
    query.to_string(MsSqlQueryBuilder),
    r#"SELECT [character] OVER [w] AS [C] FROM [character] WINDOW [w] AS (PARTITION BY [font_size])"#
);
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
);

impl SelectStatement

pub fn build_collect_any_into( &self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter, )

See QueryStatementBuilder::build_collect_any_into

pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values)

See QueryStatementBuilder::build_any

pub fn build_collect_any( &self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter, ) -> String

See QueryStatementBuilder::build_collect_any

impl SelectStatement

pub fn build_collect_into<T: QueryBuilder>( &self, query_builder: T, sql: &mut impl SqlWriter, )

See QueryStatementWriter::build_collect_into

pub fn build_collect<T: QueryBuilder>( &self, query_builder: T, sql: &mut impl SqlWriter, ) -> String

See QueryStatementWriter::build_collect

pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values)

See QueryStatementWriter::build

pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String

See QueryStatementWriter::to_string

impl SelectStatement

pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self

See OrderedStatement::add_order_by

pub fn clear_order_by(&mut self) -> &mut Self

See OrderedStatement::clear_order_by

pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self

where T: IntoColumnRef,

See OrderedStatement::order_by

pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self

See OrderedStatement::order_by_expr

pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self

where T: Into<Cow<'static, str>>, I: IntoIterator<Item = (T, Order)>,

See OrderedStatement::order_by_customs

pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order)>,

See OrderedStatement::order_by_columns

pub fn order_by_with_nulls<T>( &mut self, col: T, order: Order, nulls: NullOrdering, ) -> &mut Self

where T: IntoColumnRef,

See OrderedStatement::order_by_with_nulls

pub fn order_by_expr_with_nulls( &mut self, expr: Expr, order: Order, nulls: NullOrdering, ) -> &mut Self

See OrderedStatement::order_by_expr_with_nulls

pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: Into<Cow<'static, str>>, I: IntoIterator<Item = (T, Order, NullOrdering)>,

See OrderedStatement::order_by_customs_with_nulls

pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order, NullOrdering)>,

See OrderedStatement::order_by_columns_with_nulls

impl SelectStatement

pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self

See ConditionalStatement::and_or_where

pub fn cond_where<C>(&mut self, condition: C) -> &mut Self

where C: IntoCondition,

See ConditionalStatement::cond_where

pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self

See ConditionalStatement::and_where_option

pub fn and_where(&mut self, other: Expr) -> &mut Self

See ConditionalStatement::and_where

impl SelectStatement

pub fn mut_from<F>(&mut self, f: F) -> &mut Self

where F: Fn(&TableRef) -> TableRef,

pub fn mut_join<F>(&mut self, f: F) -> &mut Self

where F: Fn(&TableRef) -> TableRef,

pub fn mut_where_subquery<F>(&mut self, f: &F) -> &mut Self

where F: Fn(&mut SelectStatement),

Mutate all SelectStatement subqueries found in the WHERE and HAVING conditions.

Trait Implementations

impl Clone for SelectStatement

fn clone(&self) -> SelectStatement

Returns a duplicate of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl ConditionalStatement for SelectStatement

fn cond_where<C>(&mut self, condition: C) -> &mut Self

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.

fn and_where(&mut self, other: Expr) -> &mut Self

And where condition. Calling or_where after and_where will panic.

fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self

Optional and where, short hand for if c.is_some() q.and_where(c).

impl Debug for SelectStatement

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl Default for SelectStatement

fn default() -> SelectStatement

Returns the “default value” for a type.

impl From<SelectStatement> for Expr

fn from(v: SelectStatement) -> Self

Converts to this type from the input type.

impl From<SelectStatement> for QueryStatement

fn from(s: SelectStatement) -> Self

Converts to this type from the input type.

impl From<SelectStatement> for SubQueryStatement

fn from(s: SelectStatement) -> Self

Converts to this type from the input type.

impl MySqlSelectStatementExt for SelectStatement

fn use_index<I>(&mut self, index: I, scope: IndexHintScope) -> &mut Self

where I: IntoIden,

USE INDEX hint on the last table in the from clause for MySQL

Give the optimizer information about how to choose indexes during query processing. See MySQL reference manual for Index Hints

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .use_index(IndexName::new("IDX_123456"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `character` USE INDEX (`IDX_123456`)"#
);

fn use_index_on<T, I>( &mut self, table: T, index: I, scope: IndexHintScope, ) -> &mut Self

where T: IntoTableRef, I: IntoIden,

USE INDEX hint on the specified in the from clause for MySQL

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Font::Table)
    .from(Char::Table)
    .use_index_on(Char::Table, IndexName::new("IDX_123456"), IndexHintScope::All)
    .use_index_on(Font::Table, IndexName::new("IDX_654321"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `font` USE INDEX (`IDX_654321`), `character` USE INDEX (`IDX_123456`)"#
);

fn force_index<I>(&mut self, index: I, scope: IndexHintScope) -> &mut Self

where I: IntoIden,

FORCE INDEX hint on the last table in from clause for MySQL

Give the optimizer information about how to choose indexes during query processing. See MySQL reference manual for Index Hints

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .force_index(IndexName::new("IDX_123456"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `character` FORCE INDEX (`IDX_123456`)"#
);

fn force_index_on<T, I>( &mut self, table: T, index: I, scope: IndexHintScope, ) -> &mut Self

where T: IntoTableRef, I: IntoIden,

FORCE INDEX hint on the specified in the from clause for MySQL

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Font::Table)
    .from(Char::Table)
    .force_index_on(Char::Table, IndexName::new("IDX_123456"), IndexHintScope::All)
    .force_index_on(Font::Table, IndexName::new("IDX_654321"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `font` FORCE INDEX (`IDX_654321`), `character` FORCE INDEX (`IDX_123456`)"#
);

fn ignore_index<I>(&mut self, index: I, scope: IndexHintScope) -> &mut Self

where I: IntoIden,

Ignore index hint on the last table in the from clause for MySQL

Give the optimizer information about how to choose indexes during query processing. See MySQL reference manual for Index Hints

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Char::Table)
    .ignore_index(IndexName::new("IDX_123456"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `character` IGNORE INDEX (`IDX_123456`)"#
)

fn ignore_index_on<T, I>( &mut self, table: T, index: I, scope: IndexHintScope, ) -> &mut Self

where T: IntoTableRef, I: IntoIden,

IGNORE INDEX hint on the specified in the from clause for MySQL

Examples

use sea_query::{extension::mysql::*, tests_cfg::*, *};

let query = Query::select()
    .from(Font::Table)
    .from(Char::Table)
    .ignore_index_on(Char::Table, IndexName::new("IDX_123456"), IndexHintScope::All)
    .ignore_index_on(Font::Table, IndexName::new("IDX_654321"), IndexHintScope::All)
    .column(Char::SizeW)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `size_w` FROM `font` IGNORE INDEX (`IDX_654321`), `character` IGNORE INDEX (`IDX_123456`)"#
);

impl OrderedStatement for SelectStatement

fn clear_order_by(&mut self) -> &mut Self

Clear order expressions

fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self

where T: IntoColumnRef,

Order by column.

fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self

Order by Expr.

fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self

where T: Into<Cow<'static, str>>, I: IntoIterator<Item = (T, Order)>,

Order by custom string.

fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order)>,

Order by vector of columns.

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.

fn order_by_expr_with_nulls( &mut self, expr: Expr, order: Order, nulls: NullOrdering, ) -> &mut Self

Order by Expr with nulls order option.

fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: Into<Cow<'static, str>>, I: IntoIterator<Item = (T, Order, NullOrdering)>,

Order by custom string with nulls order option.

fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order, NullOrdering)>,

Order by vector of columns with nulls order option.

impl PartialEq for SelectStatement

fn eq(&self, other: &SelectStatement) -> bool

Tests for self and other values to be equal, and is used by ==.

fn ne(&self, other: &Rhs) -> bool

Tests for !=. The default implementation is almost always sufficient, and should not be overridden without very good reason.

impl PostgresSelectStatementExt for SelectStatement

fn table_sample( &mut self, method: SampleMethod, percentage: f64, repeatable: Option<f64>, ) -> &mut Self

TABLESAMPLE

Examples

use sea_query::{extension::postgres::*, tests_cfg::*, *};

let query = Query::select()
    .columns([Glyph::Image])
    .from(Glyph::Table)
    .table_sample(SampleMethod::SYSTEM, 50.0, None)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "image" FROM "glyph" TABLESAMPLE SYSTEM (50)"#
);

let query = Query::select()
    .columns([Glyph::Image])
    .from(Glyph::Table)
    .table_sample(SampleMethod::SYSTEM, 50.0, Some(3.14))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "image" FROM "glyph" TABLESAMPLE SYSTEM (50) REPEATABLE (3.14)"#
);

impl QueryStatementBuilder for SelectStatement

fn build_collect_any_into( &self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter, )

Build corresponding SQL statement into the SqlWriter for certain database backend and collect query parameters

fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values)

Build corresponding SQL statement for certain database backend and collect query parameters into a vector

fn build_collect_any( &self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter, ) -> String

Build corresponding SQL statement for certain database backend and collect query parameters

fn into_sub_query_statement(self) -> SubQueryStatement

impl QueryStatementWriter for SelectStatement

fn build_collect_into<T: QueryBuilder>( &self, query_builder: T, sql: &mut impl SqlWriter, )

fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String

Build corresponding SQL statement for certain database backend and return SQL string

fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values)

Build corresponding SQL statement for certain database backend and collect query parameters into a vector

fn build_collect<T: QueryBuilder>( &self, query_builder: T, sql: &mut impl SqlWriter, ) -> String

Build corresponding SQL statement for certain database backend and collect query parameters

impl StructuralPartialEq for SelectStatement