Struct Select

Source

pub struct Select<'a> { /* private fields */ }

Expand description

A builder for a SELECT statement.

Implementations§

Source §

impl<'a> Select<'a>

Source

pub fn from_table<T>(table: T) -> Self
where T: Into<Table<'a>>,

Creates a new SELECT statement for the given table.

let query = Select::from_table("users");
let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users`", sql);

The table can be in multiple parts, defining the database.

let query = Select::from_table(("crm", "users"));
let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `crm`.`users`.* FROM `crm`.`users`", sql);

Selecting from a nested SELECT.

let select = Table::from(Select::default().value(1)).alias("num");
let query = Select::from_table(select.alias("num"));
let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `num`.* FROM (SELECT ?) AS `num`", sql);
assert_eq!(vec![Value::from(1)], params);

Selecting from a set of values.

let expected_sql = "SELECT `vals`.* FROM (VALUES (?,?),(?,?)) AS `vals`";
let values = Table::from(values!((1, 2), (3, 4))).alias("vals");
let query = Select::from_table(values);
let (sql, params) = Sqlite::build(query)?;

assert_eq!(expected_sql, sql);
assert_eq!(
    vec![
        Value::from(1),
        Value::from(2),
        Value::from(3),
        Value::from(4),
    ],
    params
);

Source

pub fn and_from<T>(self, table: T) -> Self
where T: Into<Table<'a>>,

Adds a table to be selected.

let query = Select::from_table("users")
    .and_from(Table::from(Select::default().value(1)).alias("num"))
    .column(("users", "name"))
    .value(Table::from("num").asterisk());

let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.`name`, `num`.* FROM `users`, (SELECT ?) AS `num`", sql);

Source

pub fn value<T>(self, value: T) -> Self
where T: Into<Expression<'a>>,

Selects a static value as the column.

let query = Select::default().value(1);
let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT ?", sql);
assert_eq!(vec![Value::from(1)], params);

Creating a qualified asterisk to a joined table:

let join = "dogs".on(("dogs", "slave_id").equals(Column::from(("cats", "master_id"))));

let query = Select::from_table("cats")
    .value(Table::from("cats").asterisk())
    .value(col!("dogs", "age") - val!(4))
    .inner_join(join);

let (sql, params) = Sqlite::build(query)?;

assert_eq!(
    "SELECT `cats`.*, (`dogs`.`age` - ?) FROM `cats` INNER JOIN `dogs` ON `dogs`.`slave_id` = `cats`.`master_id`",
    sql
);

assert_eq!(vec![Value::from(4)], params);

Source

pub fn column<T>(self, column: T) -> Self
where T: Into<Column<'a>>,

Adds a column to be selected.

let query = Select::from_table("users")
    .column("name")
    .column(("users", "id"))
    .column((("crm", "users"), "foo"));

let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `name`, `users`.`id`, `crm`.`users`.`foo` FROM `users`", sql);

Source

pub fn columns<T, C>(self, columns: T) -> Self
where T: IntoIterator<Item = C>, C: Into<Column<'a>>,

A bulk method to select multiple values.

let query = Select::from_table("users").columns(vec!["foo", "bar"]);
let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `foo`, `bar` FROM `users`", sql);

Source

pub fn distinct(self) -> Self

Adds DISTINCT to the select query.

let query = Select::from_table("users").column("foo").column("bar").distinct();
let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT DISTINCT `foo`, `bar` FROM `users`", sql);

Source

pub fn so_that<T>(self, conditions: T) -> Self
where T: Into<ConditionTree<'a>>,

Adds WHERE conditions to the query, replacing the previous conditions. See Comparable for more examples.

let query = Select::from_table("users").so_that("foo".equals("bar"));
let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` WHERE `foo` = ?", sql);

assert_eq!(vec![
   Value::from("bar"),
], params);

Source

pub fn and_where<T>(self, conditions: T) -> Self
where T: Into<ConditionTree<'a>>,

Adds an additional WHERE condition to the query combining the possible previous condition with AND. See Comparable for more examples.

let query = Select::from_table("users")
    .so_that("foo".equals("bar"))
    .and_where("lol".equals("wtf"));

let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` WHERE (`foo` = ? AND `lol` = ?)", sql);

assert_eq!(vec![
   Value::from("bar"),
   Value::from("wtf"),
], params);

Source

pub fn or_where<T>(self, conditions: T) -> Self
where T: Into<ConditionTree<'a>>,

Adds an additional WHERE condition to the query combining the possible previous condition with OR. See Comparable for more examples.

let query = Select::from_table("users")
    .so_that("foo".equals("bar"))
    .or_where("lol".equals("wtf"));

let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` WHERE (`foo` = ? OR `lol` = ?)", sql);

assert_eq!(vec![
   Value::from("bar"),
   Value::from("wtf"),
], params);

Source

pub fn inner_join<J>(self, join: J) -> Self
where J: Into<JoinData<'a>>,

Adds INNER JOIN clause to the query.

let join = "posts".alias("p").on(("p", "user_id").equals(Column::from(("users", "id"))));
let query = Select::from_table("users").inner_join(join);
let (sql, _) = Sqlite::build(query)?;

assert_eq!(
    "SELECT `users`.* FROM `users` INNER JOIN `posts` AS `p` ON `p`.`user_id` = `users`.`id`",
    sql
);

Source

pub fn left_join<J>(self, join: J) -> Self
where J: Into<JoinData<'a>>,

Adds LEFT JOIN clause to the query.

let join = "posts".alias("p").on(("p", "visible").equals(true));
let query = Select::from_table("users").left_join(join);
let (sql, params) = Sqlite::build(query)?;

assert_eq!(
    "SELECT `users`.* FROM `users` LEFT JOIN `posts` AS `p` ON `p`.`visible` = ?",
    sql
);

assert_eq!(
    vec![
        Value::from(true),
    ],
    params
);

Source

pub fn right_join<J>(self, join: J) -> Self
where J: Into<JoinData<'a>>,

Adds RIGHT JOIN clause to the query.

let join = "posts".alias("p").on(("p", "visible").equals(true));
let query = Select::from_table("users").right_join(join);
let (sql, params) = Sqlite::build(query)?;

assert_eq!(
    "SELECT `users`.* FROM `users` RIGHT JOIN `posts` AS `p` ON `p`.`visible` = ?",
    sql
);

assert_eq!(
    vec![
        Value::from(true),
    ],
    params
);

Source

pub fn full_join<J>(self, join: J) -> Self
where J: Into<JoinData<'a>>,

Adds FULL JOIN clause to the query.

let join = "posts".alias("p").on(("p", "visible").equals(true));
let query = Select::from_table("users").full_join(join);
let (sql, params) = Sqlite::build(query)?;

assert_eq!(
    "SELECT `users`.* FROM `users` FULL JOIN `posts` AS `p` ON `p`.`visible` = ?",
    sql
);

assert_eq!(
    vec![
        Value::from(true),
    ],
    params
);

Source

pub fn order_by<T>(self, value: T) -> Self
where T: IntoOrderDefinition<'a>,

Adds an ordering to the ORDER BY section.

let query = Select::from_table("users")
    .order_by("foo")
    .order_by("baz".ascend())
    .order_by("bar".descend());

let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` ORDER BY `foo`, `baz` ASC, `bar` DESC", sql);

Source

pub fn group_by<T>(self, value: T) -> Self
where T: IntoGroupByDefinition<'a>,

Adds a grouping to the GROUP BY section.

This does not check if the grouping is actually valid in respect to aggregated columns.

let query = Select::from_table("users").column("foo").column("bar")
    .group_by("foo")
    .group_by("bar");

let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `foo`, `bar` FROM `users` GROUP BY `foo`, `bar`", sql);

Source

pub fn having<T>(self, conditions: T) -> Self
where T: Into<ConditionTree<'a>>,

Adds group conditions to a query. Should be combined together with a group_by statement.

let query = Select::from_table("users").column("foo").column("bar")
    .group_by("foo")
    .having("foo".greater_than(100));

let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `foo`, `bar` FROM `users` GROUP BY `foo` HAVING `foo` > ?", sql);
assert_eq!(vec![Value::from(100)], params);

Source

pub fn limit(self, limit: usize) -> Self

Sets the LIMIT value.

let query = Select::from_table("users").limit(10);
let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` LIMIT ?", sql);
assert_eq!(vec![Value::from(10_i64)], params);

Source

pub fn offset(self, offset: usize) -> Self

Sets the OFFSET value.

let query = Select::from_table("users").offset(10);
let (sql, params) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` LIMIT ? OFFSET ?", sql);
assert_eq!(vec![Value::from(-1), Value::from(10_i64)], params);

Source

pub fn comment<C: Into<Cow<'a, str>>>(self, comment: C) -> Self

Adds a comment to the select.

let query = Select::from_table("users").comment("trace_id='5bd66ef5095369c7b0d1f8f4bd33716a', parent_id='c532cb4098ac3dd2'");
let (sql, _) = Sqlite::build(query)?;

assert_eq!("SELECT `users`.* FROM `users` /* trace_id='5bd66ef5095369c7b0d1f8f4bd33716a', parent_id='c532cb4098ac3dd2' */", sql);

Source

pub fn with(self, cte: CommonTableExpression<'a>) -> Self

Adds a common table expression to the select.

let cte = Select::default()
    .value(val!(1).alias("val"))
    .into_cte("one")
    .column("val");

let query = Select::from_table("one")
    .column("val")
    .with(cte);

let (sql, params) = Sqlite::build(query)?;

assert_eq!("WITH `one` (`val`) AS (SELECT ? AS `val`) SELECT `val` FROM `one`", sql);
assert_eq!(vec![Value::from(1)], params);

§Panics

Please note that for some databases, a tuple conversion can inject expressions. These will be named in the form of cte_n, where n represents a number from 0 to up. Using these names might cause a panic.

Trait Implementations§

Source §

impl<'a> Clone for Select<'a>

Source §

fn clone(&self) -> Select<'a>

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

Source §

impl<'a> Debug for Select<'a>

Source §

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

Formats the value using the given formatter. Read more

Source §

impl<'a> Default for Select<'a>

Source §

fn default() -> Select<'a>

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

Source §