Struct QueryBuilder 

pub struct QueryBuilder<'a, Schema = (), State = (), Table = (), Marker = (), Row = (), Grouped = ()> { /* private fields */ }

Type-safe SQL query builder for MySQL.

Start with QueryBuilder::new, then call select, insert, update, delete or with. Each method returns a builder in a new state that only offers the clauses that may come next, so an out-of-order query does not compile. Call ToSQL::to_sql for the SQL text and parameters, or prepare() for a statement with placeholders. It does not run queries; the drizzle crate’s MySQL drivers do.

Type parameters

• Schema: the schema the query runs against.
• State: which clauses have been added so far.
• Table: the table the last FROM or JOIN added, or the target table of an INSERT, UPDATE or DELETE.
• Marker: the selected columns and the tables in scope.
• Row: the Rust type of one result row.
• Grouped: the GROUP BY columns.

Examples

use drizzle::core::expr::eq;
use drizzle::mysql::{builder::QueryBuilder, prelude::*};

#[MySQLTable(NAME = "users")]
struct Users {
    #[column(PRIMARY, AUTO_INCREMENT)]
    id: u64,
    #[column(VARCHAR(255))]
    name: String,
    #[column(DEFAULT = true)]
    active: bool,
}

#[derive(MySQLSchema)]
struct Schema {
    users: Users,
}

let builder = QueryBuilder::new::<Schema>();
let Schema { users } = Schema::new();
let query = builder
    .select(users.name)
    .from(users)
    .r#where(eq(users.active, true))
    .order_by(asc(users.name))
    .limit(10);
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name` FROM `users` WHERE `users`.`active` = ? ORDER BY `users`.`name` ASC LIMIT ?"
);

Implementations

impl<'a, S, T> QueryBuilder<'a, S, DeleteInitial, T>

pub fn where<E, ScopeProof>( self, condition: E, ) -> DeleteBuilder<'a, S, DeleteWhereSet, T>

where E: ExprSources + Expr<'a, MySQLValue<'a>>, E::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, E::SQLType: BooleanLike,

Adds a WHERE clause that picks the rows to delete.

The condition must be a boolean expression over the target table’s columns.

impl<'a, S, T, M, R> QueryBuilder<'a, S, DeleteWhereSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T, M, R> QueryBuilder<'a, S, DeleteOrderSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T, M, R> QueryBuilder<'a, S, DeleteLimitSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T> QueryBuilder<'a, S, DeleteWhereSet, T>

pub fn order_by<O, ScopeProof>( self, order: O, ) -> DeleteBuilder<'a, S, DeleteOrderSet, T>

where O: ToSQL<'a, MySQLValue<'a>> + ExprSources, O::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>,

Adds ORDER BY, which sets the order rows are changed in.

Mostly useful with limit. Only columns of the target table may be used.

impl<'a, S, T> QueryBuilder<'a, S, DeleteWhereSet, T>

pub fn limit<P>(self, limit: P) -> DeleteBuilder<'a, S, DeleteLimitSet, T>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds LIMIT, which caps how many rows are changed.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, T> QueryBuilder<'a, S, DeleteOrderSet, T>

pub fn limit<P>(self, limit: P) -> DeleteBuilder<'a, S, DeleteLimitSet, T>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds LIMIT, which caps how many rows are changed.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, T, M, R> QueryBuilder<'a, S, InsertValuesSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

pub fn on_duplicate_key_update( self, values: T::Update, ) -> InsertBuilder<'a, S, InsertOnDuplicateKeyUpdateSet, T, M, R>

where T: MySQLTable<'a>,

Adds ON DUPLICATE KEY UPDATE, which updates the existing row when the new one would duplicate any primary or unique key.

values is the table’s generated update model. MySQL picks the conflicting key itself, so there is no conflict target.

Panics

Panics when values sets no column.

impl<'a, S, T, M, R> QueryBuilder<'a, S, InsertOnDuplicateKeyUpdateSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, Schema, Table> QueryBuilder<'a, Schema, InsertInitial, Table>

where Table: MySQLTable<'a>,

pub fn ignore(self) -> InsertBuilder<'a, Schema, InsertIgnoreSet, Table>

Turns this statement into INSERT IGNORE.

MySQL then skips rows that duplicate a key, and also turns many other errors (such as out-of-range values) into warnings. Use it only when that is what you want; for duplicates alone, prefer on_duplicate_key_update.

Examples

let query = builder.insert(users).ignore().value(InsertUsers::new("Alice"));
assert_eq!(query.to_sql().sql(), "INSERT IGNORE INTO `users` (`name`) VALUES (?)");

impl<'a, Schema, State, Table> QueryBuilder<'a, Schema, State, Table>

where State: InsertRowSourceState, Table: MySQLTable<'a>,

pub fn columns<Columns>( self, columns: Columns, ) -> InsertBuilder<'a, Schema, InsertColumnsSet<Columns::Columns>, Table>

where Columns: InsertTargetColumns<'a, MySQLValue<'a>, Table>,

Lists the target columns for an INSERT ... SELECT.

Pass a tuple of the table’s columns, then call select. The list must include every required column (one without a default), and the SELECT must produce matching types in the same order; both are checked at compile time.

Examples

let query = builder
    .insert(posts)
    .columns((posts.user_id, posts.title))
    .select(builder.select((users.id, users.name)).from(users));
assert_eq!(
    query.to_sql().sql(),
    "INSERT INTO `posts` (`user_id`, `title`) SELECT `users`.`id`, `users`.`name` FROM `users`"
);

Panics

Panics when the same target column appears more than once.

pub fn value<T>( self, value: Table::Insert<T>, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

Inserts one row. Same as values([value]).

value is the table’s generated insert model (for example InsertUsers).

pub fn values<I, T>( self, values: I, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where I: IntoIterator<Item = Table::Insert<T>>,

Inserts one or more rows.

Each item is the table’s generated insert model, and all rows share one model type. A with_* setter given None leaves its column out of that row, so rows can still set different columns; every row then lists all of them, with DEFAULT for the ones it leaves out. If no row sets a column, this renders () VALUES ().

Examples

let query = builder
    .insert(users)
    .values([InsertUsers::new("Alice"), InsertUsers::new("Bob")]);
assert_eq!(query.to_sql().sql(), "INSERT INTO `users` (`name`) VALUES (?), (?)");

Panics

Panics when values is empty.

pub fn select<Q, R, ScopeProof, AggProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Table: InsertSelectTable, Q: IntoSelectQuery<'a, Schema, R>, Q::Marker: InsertSelectCompatible<'a, MySQLValue<'a>, Table, R> + MarkerScopeValidFor<ScopeProof> + MarkerAggValidFor<Q::Grouped, AggProof>,

Inserts the rows of a SELECT into every insertable column of the table.

The SELECT must produce one value per insertable column, in table order, with compatible types and nullability; its column references and aggregates are checked too. To fill only some columns, call columns first. A WITH clause belongs to the inner SELECT, so it renders after INSERT INTO table (...).

pub fn select_raw<Q>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Q: ToSQL<'a, MySQLValue<'a>>,

Appends any SQL as the row source, without a column list.

Nothing is checked: not the column count, types, nullability, column scope or aggregates. Prefer select.

impl<'a, Schema, Table, Targets> QueryBuilder<'a, Schema, InsertColumnsSet<Targets>, Table>

where Table: MySQLTable<'a>,

pub fn select<Q, R, RequiredProof, ScopeProof, AggProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>, Table: InsertSelectTable, Q: IntoSelectQuery<'a, Schema, R>, Q::Marker: PartialInsertSelectCompatible<'a, MySQLValue<'a>, Targets> + MarkerScopeValidFor<ScopeProof> + MarkerAggValidFor<Q::Grouped, AggProof>,

Inserts the rows of a SELECT into the columns chosen with columns.

The SELECT must produce one value per chosen column, in the same order, with compatible types and nullability.

pub fn select_raw<Q, RequiredProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Table: InsertSelectTable, Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>, Q: ToSQL<'a, MySQLValue<'a>>,

Appends any SQL as the row source for the chosen columns.

Only the column list is checked (it must include every required column). The SQL itself is not checked.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: Prepare,

pub fn prepare<ScopeProof, AggProof>( &self, ) -> PreparedStatement<'a, MySQLValue<'a>>

where M: MarkerScopeValidFor<ScopeProof> + MarkerAggValidFor<G, AggProof>,

Renders this query as a PreparedStatement.

MySQL binds parameters by position, so named placeholders are put in the order they appear in the SQL (a name used twice is bound twice). Bind values by name later with bind.

Compile-time checks

Only compiles when every column reference is in scope (its table is in FROM or a JOIN) and every selected column is grouped or inside an aggregate.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .r#where(eq(users.name, Placeholder::named("name")));
let prepared = query.prepare();
assert_eq!(prepared.sql(), "SELECT `users`.`id` FROM `users` WHERE `users`.`name` = ?");

impl<'a, S, M> QueryBuilder<'a, S, SelectInitial, (), M>

pub fn from<T>( self, table: T, ) -> SelectBuilder<'a, S, SelectFromSet, T, FromMarker<M, T>, <M as ResolveRow<T>>::Row>

where T: ToSQL<'a, MySQLValue<'a>> + ScopeEntry, M: ResolveRow<T>,

Sets the FROM source: a table, a CTE, or a derived table made with alias.

The result row type is inferred from the selected columns and this source. With select(()), the row is the table’s generated select model.

impl<'a, S, T, M, R, G> QueryBuilder<'a, S, SelectFromSet, T, M, R, G>

where T: MySQLTable<'a>,

pub fn use_index<Indexes>( self, indexes: Indexes, ) -> SelectBuilder<'a, S, SelectIndexHintSet<UseIndex>, T, M, R, G>

where Indexes: IndexHintList<'a, T>,

Adds USE INDEX (..) to the FROM table.

Pass one #[MySQLIndex] index of this table, or a tuple of up to eight. An index of another table does not compile. Only one hint (use_index, force_index or ignore_index) can be added, right after from. To hint a joined table, use MySQLIndexHintExt.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .use_index(UsersNameIdx::new());
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`id` FROM `users` USE INDEX (`users_name_idx`)"
);

pub fn force_index<Indexes>( self, indexes: Indexes, ) -> SelectBuilder<'a, S, SelectIndexHintSet<ForceIndex>, T, M, R, G>

where Indexes: IndexHintList<'a, T>,

Adds FORCE INDEX (..) to the FROM table, which tells MySQL to use a table scan only if none of these indexes can be used. See use_index.

pub fn ignore_index<Indexes>( self, indexes: Indexes, ) -> SelectBuilder<'a, S, SelectIndexHintSet<IgnoreIndex>, T, M, R, G>

where Indexes: IndexHintList<'a, T>,

Adds IGNORE INDEX (..) to the FROM table. See use_index.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Join>,

pub fn join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>,

Adds a join with join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn inner_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>,

Adds a join with inner_join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn left_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>,

Adds a join with left_join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn left_outer_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>,

Adds a join with left_outer_join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn right_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>,

Adds a join with right_join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn right_outer_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>,

Adds a join with right_outer_join.

Pass (source, condition) with a boolean condition, or a bare table to join on its foreign key to the previous table. The source can be a table, a derived table (see alias), or a table with an index hint (see MySQLIndexHintExt). After a LEFT or RIGHT join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

Examples

let query = builder
    .select((users.name, posts.title))
    .from(users)
    .left_join((posts, eq(posts.user_id, users.id)));
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`name`, `posts`.`title` FROM `users` LEFT JOIN `posts` ON `posts`.`user_id` = `users`.`id`"
);

pub fn cross_join<Arg: CrossJoinArg<'a, T>>( self, arg: Arg, ) -> SelectBuilder<'a, S, SelectJoinSet, Arg::JoinedTable, <M as JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>>::Marker, <M as JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>>::Row, G>

where M: JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>,

Adds a CROSS JOIN, which pairs every row with every row of arg.

A bare source renders CROSS JOIN. For backwards compatibility, (source, condition) renders the equivalent INNER JOIN ... ON ....

pub fn inner_join_lateral<Arg>( self, arg: Arg, ) -> SelectBuilder<'a, S, SelectJoinSet, Arg::JoinedTable, <M as JoinStep<R, Arg::JoinedTable, Lateral<InnerJoin>, Arg::OnSources>>::Marker, <M as JoinStep<R, Arg::JoinedTable, Lateral<InnerJoin>, Arg::OnSources>>::Row, G>

where Arg: LateralArg<'a, MySQLValue<'a>>, M: JoinStep<R, Arg::JoinedTable, Lateral<InnerJoin>, Arg::OnSources>,

Adds INNER JOIN LATERAL (subquery) AS name ON condition.

A lateral subquery may reference columns of the tables before it. Requires MySQL 8.0.14 or later.

pub fn left_join_lateral<Arg, SelectionProof>( self, arg: Arg, ) -> SelectBuilder<'a, S, SelectJoinSet, Arg::JoinedTable, <M as JoinStep<R, Arg::JoinedTable, Lateral<LeftJoin>, Arg::OnSources>>::Marker, <M as JoinStep<R, Arg::JoinedTable, Lateral<LeftJoin>, Arg::OnSources>>::Row, G>

where Arg: LateralArg<'a, MySQLValue<'a>>, M: JoinStep<R, Arg::JoinedTable, Lateral<LeftJoin>, Arg::OnSources> + LeftLateralSelection<SelectionProof>,

Adds LEFT JOIN LATERAL (subquery) AS name ON condition.

Like inner_join_lateral, but keeps rows with no match. The selection must allow the lateral columns to be missing.

pub fn cross_join_lateral<Source>( self, source: Source, ) -> SelectBuilder<'a, S, SelectJoinSet, Source::JoinedTable, <M as JoinStep<R, Source::JoinedTable, Lateral<InnerJoin>>>::Marker, <M as JoinStep<R, Source::JoinedTable, Lateral<InnerJoin>>>::Row, G>

where Source: LateralSource<'a, MySQLValue<'a>>, M: JoinStep<R, Source::JoinedTable, Lateral<InnerJoin>>,

Adds CROSS JOIN LATERAL (subquery) AS name, with no ON condition.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: SelectClause<Where>,

pub fn where<E>( self, condition: E, ) -> SelectBuilder<'a, S, SelectWhereSet, T, <M as HasScope>::With<E::Sources>, R, G>

where M: HasScope, E: Expr<'a, MySQLValue<'a>>, E::SQLType: BooleanLike,

Adds a WHERE clause. The condition must be a boolean expression.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<GroupBy>,

pub fn group_by<Gr>( self, columns: Gr, ) -> SelectBuilder<'a, S, SelectGroupSet, T, <M as HasScope>::With<Gr::Sources>, R, Gr::Columns>

where M: HasScope, Gr: IntoGroupBy<'a, MySQLValue<'a>>,

Adds a GROUP BY clause. Pass one expression or a tuple.

Every selected column that is not inside an aggregate must appear in the GROUP BY list, or belong to a table grouped by its single-column primary key. This is checked by prepare and when the query runs.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Having>,

pub fn having<E>( self, condition: E, ) -> SelectBuilder<'a, S, SelectHavingSet, T, <M as HasScope>::With<E::Sources>, R, G>

where M: HasScope, E: Expr<'a, MySQLValue<'a>>, E::SQLType: BooleanLike,

Adds a HAVING clause, which filters groups.

Only available after group_by, and only once. The condition must be a boolean expression and may use aggregates.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: SelectClause<OrderBy>,

pub fn order_by<O>( self, order: O, ) -> SelectBuilder<'a, S, SelectOrderSet, T, <M as HasScope>::With<O::Sources>, R, G>

where M: HasScope, O: ToSQL<'a, MySQLValue<'a>> + ExprSources,

Adds an ORDER BY clause. Pass one ordering term or a tuple; wrap a column in asc or desc to set the direction.

impl<'a, S, T, M, R, G> QueryBuilder<'a, S, SelectSetOpSet, T, M, R, G>

pub fn order_by<O, Proof>( self, order: O, ) -> SelectBuilder<'a, S, SelectOrderSet, T, M, R, G>

where O: SetOrderBy<'a, M, T, Proof>,

Sorts a compound (set operation) result.

Pass a selected column (rendered without its table name), or output_alias for a named output, optionally wrapped in asc or desc. A column the first query does not select does not compile.

Examples

let query = builder
    .select(alias(users.name, "label"))
    .from(users)
    .union(builder.select(alias(posts.title, "label")).from(posts))
    .order_by(desc(output_alias("label")));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`name` AS `label` FROM `users`) UNION (SELECT `posts`.`title` AS `label` FROM `posts`) ORDER BY `label` DESC"
);

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: SelectClause<Limit>,

pub fn limit<P>( self, limit: P, ) -> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds a LIMIT clause.

Pass a non-negative integer or an integer placeholder. Both are sent as bound parameters.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<MySqlOffset>,

pub fn offset<P>( self, offset: P, ) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>

where P: PaginationArg<'a, MySQLValue<'a>>,

Skips the first offset rows without limiting the row count.

MySQL has no bare OFFSET, so this renders LIMIT 9223372036854775807 OFFSET ? (see [drizzle_core::helpers::MYSQL_UNBOUNDED_LIMIT]).

Examples

let query = builder.select(users.id).from(users).offset(5);
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`id` FROM `users` LIMIT 9223372036854775807 OFFSET ?"
);

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, T, M, R, G> QueryBuilder<'a, S, SelectLimitSet, T, M, R, G>

pub fn offset<P>( self, offset: P, ) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds an OFFSET clause after LIMIT.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Simple>,

pub fn for_update( self, ) -> SelectBuilder<'a, S, SelectForSet<ForUpdate>, T, M, R, G>

Adds FOR UPDATE, which locks the selected rows until the transaction ends.

Must be the last clause; only nowait or skip_locked can follow. Not available on a compound query, and the result cannot be a set operand, a subquery or an INSERT source.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .limit(2)
    .for_update()
    .skip_locked();
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`id` FROM `users` LIMIT ? FOR UPDATE SKIP LOCKED"
);

pub fn for_share( self, ) -> SelectBuilder<'a, S, SelectForSet<ForShare>, T, M, R, G>

Adds FOR SHARE, which takes shared locks on the selected rows: other transactions can read them but not change them. See for_update.

impl<'a, S, Strength, T, M, R, G> QueryBuilder<'a, S, SelectForSet<Strength>, T, M, R, G>

pub fn nowait( self, ) -> SelectBuilder<'a, S, SelectForSet<Strength, NoWait>, T, M, R, G>

Adds NOWAIT: fail at once instead of waiting when a row is locked by another transaction.

pub fn skip_locked( self, ) -> SelectBuilder<'a, S, SelectForSet<Strength, SkipLocked>, T, M, R, G>

Adds SKIP LOCKED: leave out rows locked by another transaction instead of waiting for them.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Simple> + ExecutableState, T: SQLTable<'a, MySQLSchemaType, MySQLValue<'a>>,

pub fn into_cte<Tag: Tag + 'static>( self, ) -> CTEView<'a, <T as SQLTable<'a, MySQLSchemaType, MySQLValue<'a>>>::Aliased<Tag>, Self>

Turns this SELECT into a common table expression named Tag::NAME.

The result derefs to an aliased copy of the FROM table, so you can select its columns with the usual field access. Pass it to QueryBuilder::with, which has an example. Not available on a compound query.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Source>,

pub fn alias<Tag, AggProof>( self, _tag: Tag, ) -> Derived<'a, MySQLValue<'a>, Tag, <M as DerivedSelection<'a, MySQLValue<'a>, MySQLSchemaType, T>>::Projection, Self>

where Tag: Tag, M: DerivedSelection<'a, MySQLValue<'a>, MySQLSchemaType, T> + MarkerAggValidFor<G, AggProof>, <M as DerivedSelection<'a, MySQLValue<'a>, MySQLSchemaType, T>>::Projection: DerivedProjection<Tag>,

Names this query so it can be used as a derived table in from or a join.

tag is a value of a Tag type; its NAME becomes the SQL alias. The result exposes the selected columns, so the outer query can reference them with typed accessors.

Panics

Panics when the projection contains duplicate output names. Name a computed expression with drizzle_core::expr::AliasExt::named to make each output unique.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Compound>,

pub fn union<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using union.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

pub fn union_all<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using union_all.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

pub fn intersect<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using intersect.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

pub fn intersect_all<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using intersect_all.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

pub fn except<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using except.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

pub fn except_all<O>( self, other: O, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<O::Marker>>::Combined, R, G>

where O: IntoSelectQuery<'a, S, R>, M: SetOperand<O::Marker>,

Combines this query with other using except_all.

Both queries must select the same row type. Each operand is wrapped in parentheses, so its own ORDER BY and LIMIT stay with it. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result. INTERSECT and EXCEPT need MySQL 8.0.31 or later.

Examples

let query = builder
    .select(users.id)
    .from(users)
    .union(builder.select(posts.user_id).from(posts));
assert_eq!(
    query.to_sql().sql(),
    "(SELECT `users`.`id` FROM `users`) UNION (SELECT `posts`.`user_id` FROM `posts`)"
);

impl<'a, Schema, Table> QueryBuilder<'a, Schema, UpdateInitial, Table>

where Table: SQLTable<'a, MySQLSchemaType, MySQLValue<'a>>,

pub fn set( self, values: Table::Update, ) -> UpdateBuilder<'a, Schema, UpdateSetClauseSet, Table>

Sets the columns to change, using the table’s generated update model.

Start from UpdateX::default() and call a with_* setter for each column to change. Columns you do not set are left as they are.

impl<'a, S, T> QueryBuilder<'a, S, UpdateSetClauseSet, T>

pub fn where<E, ScopeProof>( self, condition: E, ) -> UpdateBuilder<'a, S, UpdateWhereSet, T>

where E: ExprSources + Expr<'a, MySQLValue<'a>>, E::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, E::SQLType: BooleanLike,

Adds a WHERE clause that picks the rows to update.

The condition must be a boolean expression over the updated table’s columns.

impl<'a, S, T, M, R> QueryBuilder<'a, S, UpdateWhereSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T, M, R> QueryBuilder<'a, S, UpdateOrderSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T, M, R> QueryBuilder<'a, S, UpdateLimitSet, T, M, R>

pub fn prepare(&self) -> PreparedStatement<'a, MySQLValue<'a>>

Renders this statement as a PreparedStatement whose placeholders are bound in MySQL’s positional order.

impl<'a, S, T> QueryBuilder<'a, S, UpdateWhereSet, T>

pub fn order_by<O, ScopeProof>( self, order: O, ) -> UpdateBuilder<'a, S, UpdateOrderSet, T>

where O: ToSQL<'a, MySQLValue<'a>> + ExprSources, O::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>,

Adds ORDER BY, which sets the order rows are changed in.

Mostly useful with limit. Only columns of the target table may be used.

impl<'a, S, T> QueryBuilder<'a, S, UpdateWhereSet, T>

pub fn limit<P>(self, limit: P) -> UpdateBuilder<'a, S, UpdateLimitSet, T>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds LIMIT, which caps how many rows are changed.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, S, T> QueryBuilder<'a, S, UpdateOrderSet, T>

pub fn limit<P>(self, limit: P) -> UpdateBuilder<'a, S, UpdateLimitSet, T>

where P: PaginationArg<'a, MySQLValue<'a>>,

Adds LIMIT, which caps how many rows are changed.

Panics

Panics when a numeric argument is negative or does not fit in usize.

impl<'a, Schema, State, Table, Marker, Row, Grouped> QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

where State: ExecutableState,

pub fn comment(self, text: impl AsRef<str>) -> Self

Prepends a sqlcommenter comment (/*...*/) to the query.

/* and */ inside text are broken up (/ *, * /) so the text cannot end the comment early. An empty text leaves the query unchanged.

pub fn comment_tags<I, K, V>(self, pairs: I) -> Self

where I: IntoIterator<Item = (K, V)>, K: AsRef<str>, V: AsRef<str>,

Prepends a tag-style sqlcommenter comment to the query.

Each (key, value) pair is URL-encoded and written as key='value'. Pairs are sorted and joined with ,. Pairs with an empty value are skipped; if none remain, the query is unchanged.

impl<'a> QueryBuilder<'a>

pub const fn new<S>() -> QueryBuilder<'a, S, BuilderInit>

Creates a query builder for the schema S.

impl<'a, Schema> QueryBuilder<'a, Schema, BuilderInit>

pub fn select<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, MySQLValue<'a>> + IntoSelectTarget,

Starts a SELECT with the given columns.

Pass one column or expression, a tuple of them, or () to select every column of the FROM table (and of joined tables). Call from next.

Examples

let query = builder.select(()).from(users);
assert_eq!(
    query.to_sql().sql(),
    "SELECT `users`.`id`, `users`.`name`, `users`.`active` FROM `users`"
);

pub fn select_distinct<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, MySQLValue<'a>> + IntoSelectTarget,

Starts a SELECT DISTINCT, which drops duplicate rows.

pub fn insert<Table>( &self, table: Table, ) -> InsertBuilder<'a, Schema, InsertInitial, Table>

where Table: MySQLTable<'a>,

Starts an INSERT into table.

Call values, select, columns or ignore next.

pub fn update<Table>( &self, table: Table, ) -> UpdateBuilder<'a, Schema, UpdateInitial, Table>

where Table: MySQLTable<'a>,

Starts an UPDATE of table. Call set next.

pub fn delete<Table>( &self, table: Table, ) -> DeleteBuilder<'a, Schema, DeleteInitial, Table>

where Table: MySQLTable<'a>,

Starts a DELETE from table.

Without where, the statement deletes every row.

pub fn with<C>(&self, cte: &C) -> QueryBuilder<'a, Schema, CTEInit>

where C: CTEDefinition<'a>,

Starts a WITH clause with one common table expression.

Build the CTE with into_cte. Add more with another .with(..), then start a SELECT, UPDATE or DELETE. For INSERT ... SELECT, put the WITH on the inner SELECT instead.

Examples

struct Active;
impl drizzle::core::Tag for Active {
    const NAME: &'static str = "active";
}

let active = builder
    .select((users.id, users.name))
    .from(users)
    .r#where(eq(users.active, true))
    .into_cte::<Active>();
let query = builder.with(&active).select(active.name).from(&active);
// WITH `active` AS (SELECT ...) SELECT `active`.`name` FROM `active`

impl<'a, Schema> QueryBuilder<'a, Schema, CTEInit>

pub fn select<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, MySQLValue<'a>> + IntoSelectTarget,

Starts a SELECT after the WITH clause.

pub fn select_distinct<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, MySQLValue<'a>> + IntoSelectTarget,

Starts a SELECT DISTINCT after the WITH clause.

pub fn update<Table>( &self, table: Table, ) -> UpdateBuilder<'a, Schema, UpdateInitial, Table>

where Table: MySQLTable<'a>,

Starts an UPDATE after the WITH clause.

pub fn delete<Table>( &self, table: Table, ) -> DeleteBuilder<'a, Schema, DeleteInitial, Table>

where Table: MySQLTable<'a>,

Starts a DELETE after the WITH clause.

pub fn with<C>(&self, cte: &C) -> Self

where C: CTEDefinition<'a>,

Adds another common table expression to the WITH clause.

Trait Implementations

impl<'a, Schema: Clone, State: Clone, Table: Clone, Marker: Clone, Row: Clone, Grouped: Clone> Clone for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

fn clone(&self) -> Self

Returns a duplicate of the value.

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

Performs copy-assignment from source.

impl<'a, Schema: Debug, State: Debug, Table: Debug, Marker: Debug, Row: Debug, Grouped: Debug> Debug for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

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

Formats the value using the given formatter.

impl<'a, Schema, State, Table, Marker, Row, Grouped> ToSQL<'a, MySQLValue<'a>> for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

where State: ExecutableState,

fn to_sql(&self) -> SQL<'a, MySQLValue<'a>>

Renders self as a SQL fragment.

fn into_sql(self) -> SQL<'a, MySQLValue<'a>>

Renders self as a SQL fragment, consuming it.