Trait diesel::query_dsl::QueryDsl

pub trait QueryDsl: Sized {
    fn distinct(self) -> Distinct<Self>
    where
        Self: DistinctDsl,
    { ... }
    fn distinct_on<Expr>(self, expr: Expr) -> DistinctOn<Self, Expr>
    where
        Self: DistinctOnDsl<Expr>,
    { ... }
    fn select<Selection>(self, selection: Selection) -> Select<Self, Selection>
    where
        Selection: Expression,
        Self: SelectDsl<Selection>,
    { ... }
    fn count(self) -> Select<Self, CountStar>
    where
        Self: SelectDsl<CountStar>,
    { ... }
    fn inner_join<Rhs>(self, rhs: Rhs) -> Self::Output
    where
        Self: JoinWithImplicitOnClause<Rhs, Inner>,
    { ... }
    fn left_outer_join<Rhs>(self, rhs: Rhs) -> Self::Output
    where
        Self: JoinWithImplicitOnClause<Rhs, LeftOuter>,
    { ... }
    fn left_join<Rhs>(self, rhs: Rhs) -> Self::Output
    where
        Self: JoinWithImplicitOnClause<Rhs, LeftOuter>,
    { ... }
    fn filter<Predicate>(self, predicate: Predicate) -> Filter<Self, Predicate>
    where
        Self: FilterDsl<Predicate>,
    { ... }
    fn or_filter<Predicate>(
        self, 
        predicate: Predicate
    ) -> OrFilter<Self, Predicate>
    where
        Self: OrFilterDsl<Predicate>,
    { ... }
    fn find<PK>(self, id: PK) -> Find<Self, PK>
    where
        Self: FindDsl<PK>,
    { ... }
    fn order<Expr>(self, expr: Expr) -> Order<Self, Expr>
    where
        Expr: Expression,
        Self: OrderDsl<Expr>,
    { ... }
    fn order_by<Expr>(self, expr: Expr) -> Order<Self, Expr>
    where
        Expr: Expression,
        Self: OrderDsl<Expr>,
    { ... }
    fn then_order_by<Order>(self, order: Order) -> ThenOrderBy<Self, Order>
    where
        Self: ThenOrderDsl<Order>,
    { ... }
    fn limit(self, limit: i64) -> Limit<Self>
    where
        Self: LimitDsl,
    { ... }
    fn offset(self, offset: i64) -> Offset<Self>
    where
        Self: OffsetDsl,
    { ... }
    fn for_update(self) -> ForUpdate<Self>
    where
        Self: LockingDsl<ForUpdate>,
    { ... }
    fn for_no_key_update(self) -> ForNoKeyUpdate<Self>
    where
        Self: LockingDsl<ForNoKeyUpdate>,
    { ... }
    fn for_share(self) -> ForShare<Self>
    where
        Self: LockingDsl<ForShare>,
    { ... }
    fn for_key_share(self) -> ForKeyShare<Self>
    where
        Self: LockingDsl<ForKeyShare>,
    { ... }
    fn skip_locked(self) -> SkipLocked<Self>
    where
        Self: ModifyLockDsl<SkipLocked>,
    { ... }
    fn no_wait(self) -> NoWait<Self>
    where
        Self: ModifyLockDsl<NoWait>,
    { ... }
    fn into_boxed<'a, DB>(self) -> IntoBoxed<'a, Self, DB>
    where
        DB: Backend,
        Self: BoxedDsl<'a, DB>,
    { ... }
    fn single_value(self) -> SingleValue<Self>
    where
        Self: SingleValueDsl,
    { ... }
    fn nullable(self) -> NullableSelect<Self>
    where
        Self: SelectNullableDsl,
    { ... }
}

Methods used to construct select statements.

Provided methods

fn distinct(self) -> Distinct<Self> where
    Self: DistinctDsl, 

Adds the DISTINCT keyword to a query.

This method will override any previous distinct clause that was present. For example, on PostgreSQL, foo.distinct_on(bar).distinct() will create the same query as foo.distinct().

Example

diesel::insert_into(users)
    .values(&vec![name.eq("Sean"); 3])
    .execute(&connection)?;
let names = users.select(name).load::<String>(&connection)?;
let distinct_names = users.select(name).distinct().load::<String>(&connection)?;

assert_eq!(vec!["Sean"; 3], names);
assert_eq!(vec!["Sean"; 1], distinct_names);

fn distinct_on<Expr>(self, expr: Expr) -> DistinctOn<Self, Expr> where
    Self: DistinctOnDsl<Expr>, 

Adds the DISTINCT ON clause to a query.

Example

diesel::insert_into(animals)
    .values(&vec![
        (species.eq("dog"), name.eq(Some("Jack")), legs.eq(4)),
        (species.eq("dog"), name.eq(None), legs.eq(4)),
        (species.eq("spider"), name.eq(None), legs.eq(8)),
    ])
    .execute(&connection)
    .unwrap();
let all_animals = animals.select((species, name, legs)).load(&connection);
let distinct_animals = animals.select((species, name, legs)).distinct_on(species).load(&connection);

assert_eq!(Ok(vec![Animal::new("dog", Some("Jack"), 4),
                   Animal::new("dog", None, 4),
                   Animal::new("spider", None, 8)]), all_animals);
assert_eq!(Ok(vec![Animal::new("dog", Some("Jack"), 4),
                   Animal::new("spider", None, 8)]), distinct_animals);

fn select<Selection>(self, selection: Selection) -> Select<Self, Selection> where
    Selection: Expression,
    Self: SelectDsl<Selection>, 

Adds a SELECT clause to the query.

If there was already a select clause present, it will be overridden. For example, foo.select(bar).select(baz) will produce the same query as foo.select(baz).

By default, the select clause will be roughly equivalent to SELECT * (however, Diesel will list all columns to ensure that they are in the order we expect).

select has slightly stricter bounds on its arguments than other methods. In particular, when used with a left outer join, .nullable must be called on columns that come from the right side of a join. It can be called on the column itself, or on an expression containing that column. title.nullable(), lower(title).nullable(), and (id, title).nullable() would all be valid.

Examples

// By default, all columns will be selected
let all_users = users.load::<(i32, String)>(&connection)?;
assert_eq!(vec![(1, String::from("Sean")), (2, String::from("Tess"))], all_users);

let all_names = users.select(name).load::<String>(&connection)?;
assert_eq!(vec!["Sean", "Tess"], all_names);

When used with a left join

let join = users::table.left_join(posts::table);

// By default, all columns from both tables are selected
let all_data = join.load::<(User, Option<Post>)>(&connection)?;
let expected_data = vec![
    (User::new(1, "Sean"), Some(Post::new(post_id, 1, "Sean's Post"))),
    (User::new(2, "Tess"), None),
];
assert_eq!(expected_data, all_data);

// Since `posts` is on the right side of a left join, `.nullable` is
// needed.
let names_and_titles = join.select((users::name, posts::title.nullable()))
    .load::<(String, Option<String>)>(&connection)?;
let expected_data = vec![
    (String::from("Sean"), Some(String::from("Sean's Post"))),
    (String::from("Tess"), None),
];
assert_eq!(expected_data, names_and_titles);

fn count(self) -> Select<Self, CountStar> where
    Self: SelectDsl<CountStar>, 

Get the count of a query. This is equivalent to .select(count_star())

Example

let count = users.count().get_result(&connection);
assert_eq!(Ok(2), count);

fn inner_join<Rhs>(self, rhs: Rhs) -> Self::Output where
    Self: JoinWithImplicitOnClause<Rhs, Inner>, 

Join two tables using a SQL INNER JOIN.

If you have invoked joinable! for the two tables, you can pass that table directly. Otherwise you will need to use .on to specify the ON clause.

You can join to as many tables as you'd like in a query, with the restriction that no table can appear in the query more than once. The reason for this restriction is that one of the appearances would require aliasing, and we do not currently have a fleshed out story for dealing with table aliases.

You will also need to call allow_tables_to_appear_in_same_query!. If you are using infer_schema! or diesel print-schema, this will have been generated for you. See the documentation for allow_tables_to_appear_in_same_query! for details.

Diesel expects multi-table joins to be semantically grouped based on the relationships. For example, users.inner_join(posts.inner_join(comments)) is not the same as users.inner_join(posts).inner_join(comments). The first would deserialize into (User, (Post, Comment)) and generate the following SQL:

SELECT * FROM users
    INNER JOIN posts ON posts.user_id = users.id
    INNER JOIN comments ON comments.post_id = posts.id

While the second query would deserialize into (User, Post, Comment) and generate the following SQL:

SELECT * FROM users
    INNER JOIN posts ON posts.user_id = users.id
    INNER JOIN comments ON comments.user_id = users.id

Examples

With implicit ON clause

joinable!(posts -> users (user_id));
allow_tables_to_appear_in_same_query!(users, posts);

let data = users.inner_join(posts)
    .select((name, title))
    .load(&connection);

let expected_data = vec![
    (String::from("Sean"), String::from("My first post")),
    (String::from("Sean"), String::from("About Rust")),
    (String::from("Tess"), String::from("My first post too")),
];
assert_eq!(Ok(expected_data), data);

With explicit ON clause

allow_tables_to_appear_in_same_query!(users, posts);

diesel::insert_into(posts)
    .values(&vec![
        (user_id.eq(1), title.eq("Sean's post")),
        (user_id.eq(2), title.eq("Sean is a jerk")),
    ])
    .execute(&connection)
    .unwrap();

let data = users
    .inner_join(posts.on(title.like(name.concat("%"))))
    .select((name, title))
    .load(&connection);
let expected_data = vec![
    (String::from("Sean"), String::from("Sean's post")),
    (String::from("Sean"), String::from("Sean is a jerk")),
];
assert_eq!(Ok(expected_data), data);

fn left_outer_join<Rhs>(self, rhs: Rhs) -> Self::Output where
    Self: JoinWithImplicitOnClause<Rhs, LeftOuter>, 

Join two tables using a SQL LEFT OUTER JOIN.

Behaves similarly to inner_join, but will produce a left join instead. See inner_join for usage examples.

fn left_join<Rhs>(self, rhs: Rhs) -> Self::Output where
    Self: JoinWithImplicitOnClause<Rhs, LeftOuter>, 

Alias for left_outer_join.

fn filter<Predicate>(self, predicate: Predicate) -> Filter<Self, Predicate> where
    Self: FilterDsl<Predicate>, 

Adds to the WHERE clause of a query.

If there is already a WHERE clause, the result will be old AND new.

Example:

let seans_id = users.filter(name.eq("Sean")).select(id)
    .first(&connection);
assert_eq!(Ok(1), seans_id);
let tess_id = users.filter(name.eq("Tess")).select(id)
    .first(&connection);
assert_eq!(Ok(2), tess_id);

fn or_filter<Predicate>(self, predicate: Predicate) -> OrFilter<Self, Predicate> where
    Self: OrFilterDsl<Predicate>, 

Adds to the WHERE clause of a query using OR

If there is already a WHERE clause, the result will be (old OR new). Calling foo.filter(bar).or_filter(baz) is identical to foo.filter(bar.or(baz)). However, the second form is much harder to do dynamically.

Example:

diesel::insert_into(animals)
    .values(&vec![
        (species.eq("cat"), legs.eq(4), name.eq("Sinatra")),
        (species.eq("dog"), legs.eq(3), name.eq("Fido")),
        (species.eq("spider"), legs.eq(8), name.eq("Charlotte")),
    ])
    .execute(&connection)?;

let good_animals = animals
    .filter(name.eq("Fido"))
    .or_filter(legs.eq(4))
    .select(name)
    .get_results::<Option<String>>(&connection)?;
let expected = vec![
    Some(String::from("Sinatra")),
    Some(String::from("Fido")),
];
assert_eq!(expected, good_animals);

fn find<PK>(self, id: PK) -> Find<Self, PK> where
    Self: FindDsl<PK>, 

Attempts to find a single record from the given table by primary key.

Example

let sean = (1, "Sean".to_string());
let tess = (2, "Tess".to_string());
assert_eq!(Ok(sean), users.find(1).first(&connection));
assert_eq!(Ok(tess), users.find(2).first(&connection));
assert_eq!(Err::<(i32, String), _>(NotFound), users.find(3).first(&connection));

fn order<Expr>(self, expr: Expr) -> Order<Self, Expr> where
    Expr: Expression,
    Self: OrderDsl<Expr>, 

Sets the order clause of a query.

If there was already a order clause, it will be overridden. See also: .desc() and .asc()

Ordering by multiple columns can be achieved by passing a tuple of those columns. To construct an order clause of an unknown number of columns, see QueryDsl::then_order_by

Examples

diesel::insert_into(users)
    .values(&vec![
        name.eq("Saul"),
        name.eq("Steve"),
        name.eq("Stan"),
    ])
    .execute(&connection)?;

let ordered_names = users.select(name)
    .order(name.desc())
    .load::<String>(&connection)?;
assert_eq!(vec!["Steve", "Stan", "Saul"], ordered_names);

diesel::insert_into(users).values(name.eq("Stan")).execute(&connection)?;

let data = users.select((name, id))
    .order((name.asc(), id.desc()))
    .load(&connection)?;
let expected_data = vec![
    (String::from("Saul"), 3),
    (String::from("Stan"), 6),
    (String::from("Stan"), 5),
    (String::from("Steve"), 4),
];
assert_eq!(expected_data, data);

fn order_by<Expr>(self, expr: Expr) -> Order<Self, Expr> where
    Expr: Expression,
    Self: OrderDsl<Expr>, 

Alias for order

fn then_order_by<Order>(self, order: Order) -> ThenOrderBy<Self, Order> where
    Self: ThenOrderDsl<Order>, 

Appends to the ORDER BY clause of this SQL query.

Unlike .order, this method will append rather than replace. In other words, .order_by(foo).order_by(bar) is equivalent to .order_by(bar). In contrast, .order_by(foo).then_order_by(bar) is equivalent to .order((foo, bar)). This method is only present on boxed queries.

Examples

diesel::insert_into(users)
    .values(&vec![
        name.eq("Saul"),
        name.eq("Steve"),
        name.eq("Stan"),
        name.eq("Stan"),
    ])
    .execute(&connection)?;

let data = users.select((name, id))
    .order_by(name.asc())
    .then_order_by(id.desc())
    .load(&connection)?;
let expected_data = vec![
    (String::from("Saul"), 3),
    (String::from("Stan"), 6),
    (String::from("Stan"), 5),
    (String::from("Steve"), 4),
];
assert_eq!(expected_data, data);

fn limit(self, limit: i64) -> Limit<Self> where
    Self: LimitDsl, 

Sets the limit clause of the query.

If there was already a limit clause, it will be overridden.

Example

// Using a limit
let limited = users.select(name)
    .order(id)
    .limit(1)
    .load::<String>(&connection)?;

// Without a limit
let no_limit = users.select(name)
    .order(id)
    .load::<String>(&connection)?;

assert_eq!(vec!["Sean"], limited);
assert_eq!(vec!["Sean", "Bastien", "Pascal"], no_limit);

fn offset(self, offset: i64) -> Offset<Self> where
    Self: OffsetDsl, 

Sets the offset clause of the query.

If there was already a offset clause, it will be overridden.

Example

// Using an offset
let offset = users.select(name)
    .order(id)
    .limit(2)
    .offset(1)
    .load::<String>(&connection)?;

// No Offset
let no_offset = users.select(name)
    .order(id)
    .limit(2)
    .load::<String>(&connection)?;

assert_eq!(vec!["Bastien", "Pascal"], offset);
assert_eq!(vec!["Sean", "Bastien"], no_offset);

fn for_update(self) -> ForUpdate<Self> where
    Self: LockingDsl<ForUpdate>, 

Adds FOR UPDATE to the end of the select statement.

This method is only available for MySQL and PostgreSQL. SQLite does not provide any form of row locking.

Additionally, .for_update cannot be used on queries with a distinct clause, group by clause, having clause, or any unions. Queries with a FOR UPDATE clause cannot be boxed.

Example

// Executes `SELECT * FROM users FOR UPDATE`
users.for_update().load(&connection)

fn for_no_key_update(self) -> ForNoKeyUpdate<Self> where
    Self: LockingDsl<ForNoKeyUpdate>, 

Adds FOR NO KEY UPDATE to the end of the select statement.

This method is only available for PostgreSQL. SQLite does not provide any form of row locking, and MySQL does not support anything finer than row-level locking.

Additionally, .for_no_key_update cannot be used on queries with a distinct clause, group by clause, having clause, or any unions. Queries with a FOR NO KEY UPDATE clause cannot be boxed.

Example

// Executes `SELECT * FROM users FOR NO KEY UPDATE`
users.for_no_key_update().load(&connection)

fn for_share(self) -> ForShare<Self> where
    Self: LockingDsl<ForShare>, 

Adds FOR SHARE to the end of the select statement.

This method is only available for MySQL and PostgreSQL. SQLite does not provide any form of row locking.

Additionally, .for_share cannot be used on queries with a distinct clause, group by clause, having clause, or any unions. Queries with a FOR SHARE clause cannot be boxed.

Example

// Executes `SELECT * FROM users FOR SHARE`
users.for_share().load(&connection)

fn for_key_share(self) -> ForKeyShare<Self> where
    Self: LockingDsl<ForKeyShare>, 

Adds FOR KEY SHARE to the end of the select statement.

This method is only available for PostgreSQL. SQLite does not provide any form of row locking, and MySQL does not support anything finer than row-level locking.

Additionally, .for_key_share cannot be used on queries with a distinct clause, group by clause, having clause, or any unions. Queries with a FOR KEY SHARE clause cannot be boxed.

Example

// Executes `SELECT * FROM users FOR KEY SHARE`
users.for_key_share().load(&connection)

fn skip_locked(self) -> SkipLocked<Self> where
    Self: ModifyLockDsl<SkipLocked>, 

Adds SKIP LOCKED to the end of a FOR UPDATE clause.

This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.

Example

// Executes `SELECT * FROM users FOR UPDATE SKIP LOCKED`
users.for_update().skip_locked().load(&connection)

fn no_wait(self) -> NoWait<Self> where
    Self: ModifyLockDsl<NoWait>, 

Adds NOWAIT to the end of a FOR UPDATE clause.

This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.

Example

// Executes `SELECT * FROM users FOR UPDATE NOWAIT`
users.for_update().no_wait().load(&connection)

fn into_boxed<'a, DB>(self) -> IntoBoxed<'a, Self, DB> where
    DB: Backend,
    Self: BoxedDsl<'a, DB>, 

Boxes the pieces of a query into a single type.

This is useful for cases where you want to conditionally modify a query, but need the type to remain the same. The backend must be specified as part of this. It is not possible to box a query and have it be useable on multiple backends.

A boxed query will incur a minor performance penalty, as the query builder can no longer be inlined by the compiler. For most applications this cost will be minimal.

Example

let mut query = users::table.into_boxed();
if let Some(name) = params.get("name") {
    query = query.filter(users::name.eq(name));
}
let users = query.load(&connection);

Diesel queries also have a similar problem to Iterator, where returning them from a function requires exposing the implementation of that function. The helper_types module exists to help with this, but you might want to hide the return type or have it conditionally change. Boxing can achieve both.

Example

fn users_by_name<'a>(name: &'a str) -> users::BoxedQuery<'a, DB> {
    users::table.filter(users::name.eq(name)).into_boxed()
}

assert_eq!(Ok(1), users_by_name("Sean").select(users::id).first(&connection));
assert_eq!(Ok(2), users_by_name("Tess").select(users::id).first(&connection));

fn single_value(self) -> SingleValue<Self> where
    Self: SingleValueDsl, 

Wraps this select statement in parenthesis, allowing it to be used as an expression.

SQL allows queries such as foo = (SELECT ...), as long as the subselect returns only a single column, and 0 or 1 rows. This method indicates that you expect the query to only return a single value (this will be enforced by adding LIMIT 1).

The SQL type of this will always be Nullable, as the query returns NULL if the table is empty or it otherwise returns 0 rows.

Example

insert_into(posts::table)
    .values(posts::user_id.eq(1))
    .execute(&connection)?;
let last_post = posts::table
    .order(posts::id.desc());
let most_recently_active_user = users.select(name)
    .filter(id.nullable().eq(last_post.select(posts::user_id).single_value()))
    .first::<String>(&connection)?;
assert_eq!("Sean", most_recently_active_user);

fn nullable(self) -> NullableSelect<Self> where
    Self: SelectNullableDsl, 

Coerce the SQL type of the select clause to it's nullable equivalent.

This is use full for writing queries that contain subselects on non null fields comparing them to nullable fields.

table! {
    users {
        id -> Integer,
        name -> Text,
    }
}

table! {
    posts {
        id -> Integer,
        by_user -> Nullable<Text>,
    }
}

posts::table.filter(
   posts::by_user.eq_any(users::table.select(users::name).nullable())
).load(&connection)?;

Loading content...

Implementors

impl<T: Table> QueryDsl for T

Loading content...