Struct sea_query::query::SelectStatement

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(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<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(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(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(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 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(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(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<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_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self
where T: Into<SimpleExpr>,

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(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<SimpleExpr>, 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),
        Alias::new("C"),
    )
    .to_owned();

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<SimpleExpr>, 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), Alias::new("w"))
    .window(
        Alias::new("w"),
        WindowStatement::partition_by(Char::FontSize),
    )
    .to_owned();

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<SimpleExpr>, 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), Alias::new("w"), Alias::new("C"))
    .window(Alias::new("w"), WindowStatement::partition_by(Char::FontSize))
    .to_owned();

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 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(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""#
);

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(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 on an empty values list.

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

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

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 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_subquery<T>( &mut self, query: SelectStatement, alias: T ) -> &mut Self
where T: IntoIden,

From sub-query.

Examples

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

let query = Query::select()
    .columns([Glyph::Image])
    .from_subquery(
        Query::select()
            .columns([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""#
);

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(ColumnRef::Asterisk)
    .from_function(Func::random(), Alias::new("func"))
    .to_owned();

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(ColumnRef::Asterisk)
    .from(Char::Table)
    .from_clear()
    .from(Font::Table)
    .to_owned();

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, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
where R: IntoTableRef, C: IntoCondition,

Cross join.

Examples

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

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .cross_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` CROSS JOIN `font` ON `character`.`font_id` = `font`.`id`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font" ON "character"."font_id" = "font"."id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" CROSS 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)
        .cross_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` CROSS 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" CROSS 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" CROSS JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
);

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::{*, 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(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
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(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(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(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(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(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 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.

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)))
    .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
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(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 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.

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::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` 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::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
                .add(Expr::col((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_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::*};

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

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

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(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(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(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(&mut self, expr: I) -> &mut Self
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([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""#
);

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(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: SimpleExpr) -> &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(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(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(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(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(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(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(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(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 "#
);

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(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::{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"#
);

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::{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(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"#
);

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(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::col((Alias::new("cte_traversal"), Alias::new("next"))).equals((Alias::new("table"), Alias::new("id")))
                            )
                            .to_owned();

let common_table_expression = CommonTableExpression::new()
            .query(
                base_query.clone().union(UnionType::All, cte_referencing).to_owned()
            )
            .columns([Alias::new("id"), Alias::new("depth"), Alias::new("next"), 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""#
);

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), Alias::new("w"), Alias::new("C"))
    .window(Alias::new("w"), WindowStatement::partition_by(Char::FontSize))
    .to_owned();

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

See QueryStatementBuilder::build_collect_any_into

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

See QueryStatementBuilder::into_sub_query_statement

pub fn into_sub_query_statement(self) -> SubQueryStatement

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

See QueryStatementBuilder::build_any

See QueryStatementBuilder::build_collect_any

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

impl SelectStatement

See QueryStatementWriter::build_collect_into

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

See QueryStatementWriter::build_collect

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

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: SimpleExpr, order: Order) -> &mut Self

See OrderedStatement::order_by_expr

See OrderedStatement::order_by_customs

pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
where T: ToString, I: IntoIterator<Item = (T, Order)>,

See OrderedStatement::order_by_columns

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_with_nulls

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

See OrderedStatement::order_by_expr_with_nulls

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

See OrderedStatement::order_by_customs_with_nulls

pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
where T: ToString, I: IntoIterator<Item = (T, Order, NullOrdering)>,

See OrderedStatement::order_by_columns_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)>,

impl SelectStatement

See ConditionalStatement::and_or_where

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

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

See ConditionalStatement::cond_where

See ConditionalStatement::and_where_option

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

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

See ConditionalStatement::and_where

Trait Implementations§

impl Clone for SelectStatement

fn clone(&self) -> SelectStatement

Returns a copy of the value. Read more

1.0.0 · source§

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

Performs copy-assignment from source. Read more

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. Read more

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

And where condition. Calling or_where after and_where will panic. Read more

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

impl Debug for SelectStatement

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

Formats the value using the given formatter. Read more

impl Default for SelectStatement

fn default() -> SelectStatement

Returns the “default value” for a type. Read more

impl MySqlSelectStatementExt for SelectStatement

Available on crate feature backend-mysql only.

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

Use index hint 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 force_index(&mut self, index: I, scope: IndexHintScope) -> &mut Self
where I: IntoIden,

Force index hint 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 ignore_index(&mut self, index: I, scope: IndexHintScope) -> &mut Self
where I: IntoIden,

Ignore index hint 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`)"#
)

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. Read more

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

Order by SimpleExpr.

fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
where T: ToString, 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. Read more

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

Order by SimpleExpr with nulls order option.

fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
where T: ToString, 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

This method tests for self and other values to be equal, and is used by ==.

1.0.0 · source§

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

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

impl QueryStatementBuilder for SelectStatement

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

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

fn into_sub_query_statement(self) -> SubQueryStatement

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

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

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

impl QueryStatementWriter for SelectStatement

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

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

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

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 Read more

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

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

impl StructuralPartialEq for SelectStatement

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§

impl<T> Any for T
where T: 'static + ?Sized,

fn type_id(&self) -> TypeId

Gets the TypeId of self. Read more

impl<T> Borrow<T> for T
where T: ?Sized,

fn borrow(&self) -> &T

Immutably borrows from an owned value. Read more

impl<T> BorrowMut<T> for T
where T: ?Sized,

fn borrow_mut(&mut self) -> &mut T

Mutably borrows from an owned value. Read more

impl<T> From<T> for T

fn from(t: T) -> T

Returns the argument unchanged.

impl<T, U> Into for T
where U: From<T>,

fn into(self) -> U

Calls U::from(self).

That is, this conversion is whatever the implementation of From<T> for U chooses to do.

impl<T> ToOwned for T
where T: Clone,

type Owned = T

The resulting type after obtaining ownership.

fn to_owned(&self) -> T

Creates owned data from borrowed data, usually by cloning. Read more

fn clone_into(&self, target: &mut T)

Uses borrowed data to replace owned data, usually by cloning. Read more

impl<T, U> TryFrom for T
where U: Into<T>,

type Error = Infallible

The type returned in the event of a conversion error.

fn try_from(value: U) -> Result<T, <T as TryFrom>::Error>

Performs the conversion.

impl<T, U> TryInto for T
where U: TryFrom<T>,

type Error = >::Error

The type returned in the event of a conversion error.