diesel/query_dsl/mod.rs
1//! Traits that construct SELECT statements
2//!
3//! Traits in this module have methods that generally map to the keyword for the corresponding clause in SQL,
4//! unless it conflicts with a Rust keyword (such as `WHERE`/`where`).
5//!
6//! Methods for constructing queries lives on the [`QueryDsl`] trait.
7//! Methods for executing queries live on [`RunQueryDsl`].
8//!
9//! See also [`expression_methods`][expression_methods] and [`dsl`][dsl].
10//!
11//! [expression_methods]: super::expression_methods
12//! [dsl]: super::dsl
13
14use crate::backend::Backend;
15use crate::connection::Connection;
16use crate::expression::count::CountStar;
17use crate::expression::Expression;
18use crate::helper_types::*;
19use crate::query_builder::locking_clause as lock;
20use crate::query_source::{joins, Table};
21use crate::result::QueryResult;
22
23mod belonging_to_dsl;
24#[doc(hidden)]
25pub mod boxed_dsl;
26mod combine_dsl;
27mod distinct_dsl;
28#[doc(hidden)]
29pub mod filter_dsl;
30mod group_by_dsl;
31mod having_dsl;
32mod join_dsl;
33#[doc(hidden)]
34pub mod limit_dsl;
35#[doc(hidden)]
36pub mod load_dsl;
37mod locking_dsl;
38mod nullable_select_dsl;
39mod offset_dsl;
40pub(crate) mod order_dsl;
41#[doc(hidden)]
42pub mod positional_order_dsl;
43mod save_changes_dsl;
44#[doc(hidden)]
45pub mod select_dsl;
46mod single_value_dsl;
47
48pub use self::belonging_to_dsl::BelongingToDsl;
49pub use self::combine_dsl::CombineDsl;
50pub use self::join_dsl::{InternalJoinDsl, JoinOnDsl, JoinWithImplicitOnClause};
51#[cfg(feature = "i-implement-a-third-party-backend-and-opt-into-breaking-changes")]
52pub use self::load_dsl::CompatibleType;
53#[doc(hidden)]
54pub use self::load_dsl::LoadQuery;
55pub use self::save_changes_dsl::{SaveChangesDsl, UpdateAndFetchResults};
56
57/// The traits used by `QueryDsl`.
58///
59/// Each trait in this module represents exactly one method from `QueryDsl`.
60/// Apps should general rely on `QueryDsl` directly, rather than these traits.
61/// However, generic code may need to include a where clause that references
62/// these traits.
63pub mod methods {
64 pub use super::boxed_dsl::BoxedDsl;
65 pub use super::distinct_dsl::*;
66 #[doc(inline)]
67 pub use super::filter_dsl::*;
68 pub use super::group_by_dsl::GroupByDsl;
69 pub use super::having_dsl::HavingDsl;
70 pub use super::limit_dsl::LimitDsl;
71 pub use super::load_dsl::{ExecuteDsl, LoadQuery};
72 pub use super::locking_dsl::{LockingDsl, ModifyLockDsl};
73 pub use super::nullable_select_dsl::SelectNullableDsl;
74 pub use super::offset_dsl::OffsetDsl;
75 pub use super::order_dsl::{OrderDsl, ThenOrderDsl};
76 pub use super::select_dsl::SelectDsl;
77 pub use super::single_value_dsl::SingleValueDsl;
78
79 #[cfg(all(feature = "with-deprecated", not(feature = "without-deprecated")))]
80 #[doc(hidden)]
81 #[allow(deprecated)]
82 #[deprecated(note = "Use `LoadQuery::RowIter` directly")]
83 pub use super::load_dsl::LoadRet;
84}
85
86/// Methods used to construct select statements.
87pub trait QueryDsl: Sized {
88 /// Adds the `DISTINCT` keyword to a query.
89 ///
90 /// This method will override any previous distinct clause that was present.
91 /// For example, on PostgreSQL, `foo.distinct_on(bar).distinct()` will
92 /// create the same query as `foo.distinct()`.
93 ///
94 /// # Example
95 ///
96 /// ```rust
97 /// # include!("../doctest_setup.rs");
98 /// #
99 /// # fn main() {
100 /// # run_test().unwrap();
101 /// # }
102 /// #
103 /// # fn run_test() -> QueryResult<()> {
104 /// # use schema::users::dsl::*;
105 /// # let connection = &mut establish_connection();
106 /// # diesel::sql_query("DELETE FROM users").execute(connection).unwrap();
107 /// diesel::insert_into(users)
108 /// .values(&vec![name.eq("Sean"); 3])
109 /// .execute(connection)?;
110 /// let names = users.select(name).load::<String>(connection)?;
111 /// let distinct_names = users.select(name).distinct().load::<String>(connection)?;
112 ///
113 /// assert_eq!(vec!["Sean"; 3], names);
114 /// assert_eq!(vec!["Sean"; 1], distinct_names);
115 /// # Ok(())
116 /// # }
117 /// ```
118 fn distinct(self) -> Distinct<Self>
119 where
120 Self: methods::DistinctDsl,
121 {
122 methods::DistinctDsl::distinct(self)
123 }
124
125 /// Adds the `DISTINCT ON` clause to a query.
126 ///
127 /// # Example
128 ///
129 /// ```rust
130 /// # include!("../doctest_setup.rs");
131 /// # use schema::animals;
132 /// #
133 /// # #[derive(Queryable, Debug, PartialEq)]
134 /// # struct Animal {
135 /// # species: String,
136 /// # name: Option<String>,
137 /// # legs: i32,
138 /// # }
139 /// #
140 /// # impl Animal {
141 /// # fn new<S: Into<String>>(species: S, name: Option<&str>, legs: i32) -> Self {
142 /// # Animal {
143 /// # species: species.into(),
144 /// # name: name.map(Into::into),
145 /// # legs
146 /// # }
147 /// # }
148 /// # }
149 /// #
150 /// # fn main() {
151 /// # use self::animals::dsl::*;
152 /// # let connection = &mut establish_connection();
153 /// # diesel::sql_query("DELETE FROM animals").execute(connection).unwrap();
154 /// diesel::insert_into(animals)
155 /// .values(&vec![
156 /// (species.eq("dog"), name.eq(Some("Jack")), legs.eq(4)),
157 /// (species.eq("dog"), name.eq(None), legs.eq(4)),
158 /// (species.eq("spider"), name.eq(None), legs.eq(8)),
159 /// ])
160 /// .execute(connection)
161 /// .unwrap();
162 /// let all_animals = animals.select((species, name, legs)).load(connection);
163 /// let distinct_animals = animals
164 /// .select((species, name, legs))
165 /// .order_by((species, legs))
166 /// .distinct_on(species)
167 /// .load(connection);
168 ///
169 /// assert_eq!(Ok(vec![Animal::new("dog", Some("Jack"), 4),
170 /// Animal::new("dog", None, 4),
171 /// Animal::new("spider", None, 8)]), all_animals);
172 /// assert_eq!(Ok(vec![Animal::new("dog", Some("Jack"), 4),
173 /// Animal::new("spider", None, 8)]), distinct_animals);
174 /// # }
175 /// ```
176 #[cfg(feature = "postgres_backend")]
177 fn distinct_on<Expr>(self, expr: Expr) -> DistinctOn<Self, Expr>
178 where
179 Self: methods::DistinctOnDsl<Expr>,
180 {
181 methods::DistinctOnDsl::distinct_on(self, expr)
182 }
183
184 // FIXME: Needs usage example and doc rewrite
185 /// Adds a `SELECT` clause to the query.
186 ///
187 /// If there was already a select clause present, it will be overridden.
188 /// For example, `foo.select(bar).select(baz)` will produce the same
189 /// query as `foo.select(baz)`.
190 ///
191 /// By default, the select clause will be roughly equivalent to `SELECT *`
192 /// (however, Diesel will list all columns to ensure that they are in the
193 /// order we expect).
194 ///
195 /// `select` has slightly stricter bounds on its arguments than other
196 /// methods. In particular, when used with a left outer join, `.nullable`
197 /// must be called on columns that come from the right side of a join. It
198 /// can be called on the column itself, or on an expression containing that
199 /// column. `title.nullable()`, `lower(title).nullable()`, and `(id,
200 /// title).nullable()` would all be valid.
201 ///
202 /// In order to use this method with columns from different tables
203 /// a method like [`.inner_join`] or [`.left_join`] needs to be called before
204 /// calling [`.select`] (See examples below).
205 /// This is because you can only access columns from tables
206 /// that appear in your query before that function call.
207 ///
208 /// **Note:** When using `select` with `group_by`, the `group_by` call must appear
209 /// before the `select` call in the query chain.
210 ///
211 /// [`.inner_join`]: QueryDsl::inner_join()
212 /// [`.left_join`]: QueryDsl::left_join()
213 /// [`.select`]: QueryDsl::select()
214 ///
215 /// # Examples
216 ///
217 /// ```rust
218 /// # include!("../doctest_setup.rs");
219 /// # use schema::users;
220 /// #
221 /// # fn main() {
222 /// # run_test().unwrap();
223 /// # }
224 /// #
225 /// # fn run_test() -> QueryResult<()> {
226 /// # use self::users::dsl::*;
227 /// # let connection = &mut establish_connection();
228 /// // By default, all columns will be selected
229 /// let all_users = users.load::<(i32, String)>(connection)?;
230 /// assert_eq!(vec![(1, String::from("Sean")), (2, String::from("Tess"))], all_users);
231 ///
232 /// let all_names = users.select(name).load::<String>(connection)?;
233 /// assert_eq!(vec!["Sean", "Tess"], all_names);
234 /// # Ok(())
235 /// # }
236 /// ```
237 ///
238 /// ### When used with a left join
239 ///
240 /// ```rust
241 /// # include!("../doctest_setup.rs");
242 /// # use schema::{users, posts};
243 /// #
244 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
245 /// # struct User {
246 /// # id: i32,
247 /// # name: String,
248 /// # }
249 /// #
250 /// # impl User {
251 /// # fn new(id: i32, name: &str) -> Self {
252 /// # User {
253 /// # id,
254 /// # name: name.into(),
255 /// # }
256 /// # }
257 /// # }
258 /// #
259 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
260 /// # struct Post {
261 /// # id: i32,
262 /// # user_id: i32,
263 /// # title: String,
264 /// # }
265 /// #
266 /// # impl Post {
267 /// # fn new(id: i32, user_id: i32, title: &str) -> Self {
268 /// # Post {
269 /// # id,
270 /// # user_id,
271 /// # title: title.into(),
272 /// # }
273 /// # }
274 /// # }
275 /// #
276 /// # fn main() {
277 /// # run_test().unwrap();
278 /// # }
279 /// #
280 /// # fn run_test() -> QueryResult<()> {
281 /// # let connection = &mut establish_connection();
282 /// # diesel::sql_query("DELETE FROM posts").execute(connection)?;
283 /// # diesel::insert_into(posts::table)
284 /// # .values((posts::user_id.eq(1), posts::title.eq("Sean's Post")))
285 /// # .execute(connection)?;
286 /// # let post_id = posts::table.select(posts::id)
287 /// # .first::<i32>(connection)?;
288 /// let join = users::table.left_join(posts::table);
289 ///
290 /// // By default, all columns from both tables are selected.
291 /// // If no explicit select clause is used this means that the result
292 /// // type of this query must contain all fields from the original schema in order.
293 /// let all_data = join.load::<(User, Option<Post>)>(connection)?;
294 /// let expected_data = vec![
295 /// (User::new(1, "Sean"), Some(Post::new(post_id, 1, "Sean's Post"))),
296 /// (User::new(2, "Tess"), None),
297 /// ];
298 /// assert_eq!(expected_data, all_data);
299 ///
300 /// // Since `posts` is on the right side of a left join, `.nullable` is
301 /// // needed.
302 /// let names_and_titles = join.select((users::name, posts::title.nullable()))
303 /// .load::<(String, Option<String>)>(connection)?;
304 /// let expected_data = vec![
305 /// (String::from("Sean"), Some(String::from("Sean's Post"))),
306 /// (String::from("Tess"), None),
307 /// ];
308 /// assert_eq!(expected_data, names_and_titles);
309 /// # Ok(())
310 /// # }
311 /// ```
312 fn select<Selection>(self, selection: Selection) -> Select<Self, Selection>
313 where
314 Selection: Expression,
315 Self: methods::SelectDsl<Selection>,
316 {
317 methods::SelectDsl::select(self, selection)
318 }
319
320 /// Get the count of a query. This is equivalent to `.select(count_star())`
321 ///
322 /// # Example
323 ///
324 /// ```rust
325 /// # include!("../doctest_setup.rs");
326 /// #
327 /// # fn main() {
328 /// # use schema::users::dsl::*;
329 /// # let connection = &mut establish_connection();
330 /// let count = users.count().get_result(connection);
331 /// assert_eq!(Ok(2), count);
332 /// # }
333 /// ```
334 fn count(self) -> Select<Self, CountStar>
335 where
336 Self: methods::SelectDsl<CountStar>,
337 {
338 use crate::dsl::count_star;
339
340 QueryDsl::select(self, count_star())
341 }
342
343 /// Join two tables using a SQL `INNER JOIN`.
344 ///
345 /// If you have invoked [`joinable!`] for the two tables, you can pass that
346 /// table directly. Otherwise you will need to use [`.on`] to specify the `ON`
347 /// clause.
348 ///
349 /// [`joinable!`]: crate::joinable!
350 /// [`.on`]: JoinOnDsl::on()
351 ///
352 /// You can join to as many tables as you'd like in a query, with the
353 /// restriction that no table can appear in the query more than once. For
354 /// tables that appear more than once in a single query the usage of [`alias!`](crate::alias!)
355 /// is required.
356 ///
357 /// You will also need to call [`allow_tables_to_appear_in_same_query!`].
358 /// If you are using `diesel print-schema`, this will
359 /// have been generated for you.
360 /// See the documentation for [`allow_tables_to_appear_in_same_query!`] for
361 /// details.
362 ///
363 /// Diesel expects multi-table joins to be semantically grouped based on the
364 /// relationships. For example, `users.inner_join(posts.inner_join(comments))`
365 /// is not the same as `users.inner_join(posts).inner_join(comments)`. The first
366 /// would deserialize into `(User, (Post, Comment))` and generate the following
367 /// SQL:
368 ///
369 /// ```sql
370 /// SELECT * FROM users
371 /// INNER JOIN (
372 /// posts
373 /// INNER JOIN comments ON comments.post_id = posts.id
374 /// ) ON posts.user_id = users.id
375 ///
376 /// ```
377 ///
378 /// While the second query would deserialize into `(User, Post, Comment)` and
379 /// generate the following SQL:
380 ///
381 /// ```sql
382 /// SELECT * FROM users
383 /// INNER JOIN posts ON posts.user_id = users.id
384 /// INNER JOIN comments ON comments.user_id = users.id
385 /// ```
386 ///
387 /// The exact generated SQL may change in future diesel version as long as the
388 /// generated query continues to produce same results. The currently generated
389 /// SQL is referred as ["explicit join"](https://www.postgresql.org/docs/current/explicit-joins.html)
390 /// by the PostgreSQL documentation and may have implications on the chosen query plan
391 /// for large numbers of joins in the same query. Checkout the documentation of the
392 /// [`join_collapse_limit` parameter](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JOIN-COLLAPSE-LIMIT)
393 /// to control this behaviour.
394 ///
395 /// [associations]: crate::associations
396 /// [`allow_tables_to_appear_in_same_query!`]: crate::allow_tables_to_appear_in_same_query!
397 ///
398 /// Note that in order to use this method with [`.select`], you will need to use it before calling
399 /// [`.select`] (See examples below). This is because you can only access columns from tables
400 /// that appear in your query before the call to [`.select`].
401 ///
402 /// [`.select`]: QueryDsl::select()
403 ///
404 /// # Examples
405 ///
406 /// ### With implicit `ON` clause
407 ///
408 /// ```rust
409 /// # include!("../doctest_setup.rs");
410 /// # use schema::{users, posts};
411 /// # /*
412 /// joinable!(posts -> users (user_id));
413 /// allow_tables_to_appear_in_same_query!(users, posts);
414 /// # */
415 ///
416 /// # fn main() {
417 /// # use self::users::dsl::{users, name};
418 /// # use self::posts::dsl::{posts, user_id, title};
419 /// # let connection = &mut establish_connection();
420 /// let data = users.inner_join(posts)
421 /// .select((name, title))
422 /// .load(connection);
423 ///
424 /// let expected_data = vec![
425 /// (String::from("Sean"), String::from("My first post")),
426 /// (String::from("Sean"), String::from("About Rust")),
427 /// (String::from("Tess"), String::from("My first post too")),
428 /// ];
429 /// assert_eq!(Ok(expected_data), data);
430 /// # }
431 /// ```
432 ///
433 /// ### With explicit `ON` clause
434 ///
435 /// ```rust
436 /// # include!("../doctest_setup.rs");
437 /// # use schema::{users, posts};
438 /// #
439 /// # /*
440 /// allow_tables_to_appear_in_same_query!(users, posts);
441 /// # */
442 ///
443 /// # fn main() {
444 /// # use self::users::dsl::{users, name};
445 /// # use self::posts::dsl::{posts, user_id, title};
446 /// # let connection = &mut establish_connection();
447 /// diesel::insert_into(posts)
448 /// .values(&vec![
449 /// (user_id.eq(1), title.eq("Sean's post")),
450 /// (user_id.eq(2), title.eq("Sean is a jerk")),
451 /// ])
452 /// .execute(connection)
453 /// .unwrap();
454 ///
455 /// let data = users
456 /// .inner_join(posts.on(title.like(name.concat("%"))))
457 /// .select((name, title))
458 /// .load(connection);
459 /// let expected_data = vec![
460 /// (String::from("Sean"), String::from("Sean's post")),
461 /// (String::from("Sean"), String::from("Sean is a jerk")),
462 /// ];
463 /// assert_eq!(Ok(expected_data), data);
464 /// # }
465 /// ```
466 ///
467 /// ### With explicit `ON` clause (struct)
468 ///
469 /// ```rust
470 /// # include!("../doctest_setup.rs");
471 /// # use schema::{users, posts};
472 /// #
473 /// # /*
474 /// allow_tables_to_appear_in_same_query!(users, posts);
475 /// # */
476 ///
477 /// # fn main() {
478 /// # use self::users::dsl::{users, name};
479 /// # use self::posts::dsl::{posts, user_id, title};
480 /// # let connection = &mut establish_connection();
481 /// #[derive(Debug, PartialEq, Queryable)]
482 /// struct User {
483 /// id: i32,
484 /// name: String,
485 /// }
486 ///
487 /// #[derive(Debug, PartialEq, Queryable)]
488 /// struct Post {
489 /// id: i32,
490 /// user_id: i32,
491 /// title: String,
492 /// }
493 ///
494 /// diesel::insert_into(posts)
495 /// .values(&vec![
496 /// (user_id.eq(1), title.eq("Sean's post")),
497 /// (user_id.eq(2), title.eq("Sean is a jerk")),
498 /// ])
499 /// .execute(connection)
500 /// .unwrap();
501 ///
502 /// // By default, all columns from both tables are selected.
503 /// // If no explicit select clause is used this means that the
504 /// // result type of this query must contain all fields from the
505 /// // original schema in order.
506 /// let data = users
507 /// .inner_join(posts.on(title.like(name.concat("%"))))
508 /// .load::<(User, Post)>(connection); // type could be elided
509 /// let expected_data = vec![
510 /// (
511 /// User { id: 1, name: String::from("Sean") },
512 /// Post { id: 4, user_id: 1, title: String::from("Sean's post") },
513 /// ),
514 /// (
515 /// User { id: 1, name: String::from("Sean") },
516 /// Post { id: 5, user_id: 2, title: String::from("Sean is a jerk") },
517 /// ),
518 /// ];
519 /// assert_eq!(Ok(expected_data), data);
520 /// # }
521 /// ```
522 fn inner_join<Rhs>(self, rhs: Rhs) -> InnerJoin<Self, Rhs>
523 where
524 Self: JoinWithImplicitOnClause<Rhs, joins::Inner>,
525 {
526 self.join_with_implicit_on_clause(rhs, joins::Inner)
527 }
528
529 /// Join two tables using a SQL `LEFT OUTER JOIN`.
530 ///
531 /// Behaves similarly to [`inner_join`], but will produce a left join
532 /// instead. See [`inner_join`] for usage examples.
533 ///
534 /// [`inner_join`]: QueryDsl::inner_join()
535 ///
536 /// Columns in the right hand table will become `Nullable` which means
537 /// you must call `nullable()` on the corresponding fields in the select
538 /// clause:
539 ///
540 /// ### Selecting after a left join
541 ///
542 /// ```rust
543 /// # include!("../doctest_setup.rs");
544 /// # use schema::{users, posts};
545 /// #
546 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
547 /// # struct User {
548 /// # id: i32,
549 /// # name: String,
550 /// # }
551 /// #
552 /// # impl User {
553 /// # fn new(id: i32, name: &str) -> Self {
554 /// # User {
555 /// # id,
556 /// # name: name.into(),
557 /// # }
558 /// # }
559 /// # }
560 /// #
561 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
562 /// # struct Post {
563 /// # id: i32,
564 /// # user_id: i32,
565 /// # title: String,
566 /// # }
567 /// #
568 /// # impl Post {
569 /// # fn new(id: i32, user_id: i32, title: &str) -> Self {
570 /// # Post {
571 /// # id,
572 /// # user_id,
573 /// # title: title.into(),
574 /// # }
575 /// # }
576 /// # }
577 /// #
578 /// # fn main() {
579 /// # run_test().unwrap();
580 /// # }
581 /// #
582 /// # fn run_test() -> QueryResult<()> {
583 /// # let connection = &mut establish_connection();
584 /// # diesel::sql_query("DELETE FROM posts").execute(connection)?;
585 /// # diesel::insert_into(posts::table)
586 /// # .values((posts::user_id.eq(1), posts::title.eq("Sean's Post")))
587 /// # .execute(connection)?;
588 /// # let post_id = posts::table.select(posts::id)
589 /// # .first::<i32>(connection)?;
590 /// let join = users::table.left_join(posts::table);
591 ///
592 /// // Since `posts` is on the right side of a left join, `.nullable` is
593 /// // needed.
594 /// let names_and_titles = join.select((users::name, posts::title.nullable()))
595 /// .load::<(String, Option<String>)>(connection)?;
596 /// let expected_data = vec![
597 /// (String::from("Sean"), Some(String::from("Sean's Post"))),
598 /// (String::from("Tess"), None),
599 /// ];
600 /// assert_eq!(expected_data, names_and_titles);
601 /// # Ok(())
602 /// # }
603 /// ```
604 fn left_outer_join<Rhs>(self, rhs: Rhs) -> LeftJoin<Self, Rhs>
605 where
606 Self: JoinWithImplicitOnClause<Rhs, joins::LeftOuter>,
607 {
608 self.join_with_implicit_on_clause(rhs, joins::LeftOuter)
609 }
610
611 /// Alias for [`left_outer_join`].
612 ///
613 /// [`left_outer_join`]: QueryDsl::left_outer_join()
614 fn left_join<Rhs>(self, rhs: Rhs) -> LeftJoin<Self, Rhs>
615 where
616 Self: JoinWithImplicitOnClause<Rhs, joins::LeftOuter>,
617 {
618 self.left_outer_join(rhs)
619 }
620
621 /// Adds to the `WHERE` clause of a query.
622 ///
623 /// If there is already a `WHERE` clause, the result will be `old AND new`.
624 ///
625 /// Note that in order to use this method with columns from different tables, you need to call
626 /// [`.inner_join`] or [`.left_join`] beforehand.
627 /// This is because you can only access columns from tables
628 /// that appear in your query before the call to [`.filter`].
629 ///
630 /// [`.inner_join`]: QueryDsl::inner_join()
631 /// [`.left_join`]: QueryDsl::left_join()
632 /// [`.filter`]: QueryDsl::filter()
633 ///
634 /// # Example:
635 ///
636 /// ```rust
637 /// # include!("../doctest_setup.rs");
638 /// #
639 /// # fn main() {
640 /// # use schema::users::dsl::*;
641 /// # let connection = &mut establish_connection();
642 /// let seans_id = users.filter(name.eq("Sean")).select(id)
643 /// .first(connection);
644 /// assert_eq!(Ok(1), seans_id);
645 /// let tess_id = users.filter(name.eq("Tess")).select(id)
646 /// .first(connection);
647 /// assert_eq!(Ok(2), tess_id);
648 /// # }
649 /// ```
650 #[doc(alias = "where")]
651 fn filter<Predicate>(self, predicate: Predicate) -> Filter<Self, Predicate>
652 where
653 Self: methods::FilterDsl<Predicate>,
654 {
655 methods::FilterDsl::filter(self, predicate)
656 }
657
658 /// Adds to the `WHERE` clause of a query using `OR`
659 ///
660 /// If there is already a `WHERE` clause, the result will be `(old OR new)`.
661 /// Calling `foo.filter(bar).or_filter(baz)`
662 /// is identical to `foo.filter(bar.or(baz))`.
663 /// However, the second form is much harder to do dynamically.
664 ///
665 /// # Example:
666 ///
667 /// ```rust
668 /// # include!("../doctest_setup.rs");
669 /// #
670 /// # fn main() {
671 /// # run_test().unwrap();
672 /// # }
673 /// #
674 /// # fn run_test() -> QueryResult<()> {
675 /// # use schema::animals::dsl::*;
676 /// # let connection = &mut establish_connection();
677 /// # diesel::delete(animals).execute(connection)?;
678 /// diesel::insert_into(animals)
679 /// .values(&vec![
680 /// (species.eq("cat"), legs.eq(4), name.eq("Sinatra")),
681 /// (species.eq("dog"), legs.eq(3), name.eq("Fido")),
682 /// (species.eq("spider"), legs.eq(8), name.eq("Charlotte")),
683 /// ])
684 /// .execute(connection)?;
685 ///
686 /// let good_animals = animals
687 /// .filter(name.eq("Fido"))
688 /// .or_filter(legs.eq(4))
689 /// .select(name)
690 /// .get_results::<Option<String>>(connection)?;
691 /// let expected = vec![
692 /// Some(String::from("Sinatra")),
693 /// Some(String::from("Fido")),
694 /// ];
695 /// assert_eq!(expected, good_animals);
696 /// # Ok(())
697 /// # }
698 /// ```
699 #[doc(alias = "where")]
700 fn or_filter<Predicate>(self, predicate: Predicate) -> OrFilter<Self, Predicate>
701 where
702 Self: methods::OrFilterDsl<Predicate>,
703 {
704 methods::OrFilterDsl::or_filter(self, predicate)
705 }
706
707 /// Attempts to find a single record from the given table by primary key.
708 ///
709 /// # Example
710 ///
711 /// ```rust
712 /// # include!("../doctest_setup.rs");
713 /// #
714 /// # fn main() {
715 /// # use schema::users::dsl::*;
716 /// # use diesel::result::Error::NotFound;
717 /// # let connection = &mut establish_connection();
718 /// let sean = (1, "Sean".to_string());
719 /// let tess = (2, "Tess".to_string());
720 /// assert_eq!(Ok(sean), users.find(1).first(connection));
721 /// assert_eq!(Ok(tess), users.find(2).first(connection));
722 /// assert_eq!(Err::<(i32, String), _>(NotFound), users.find(3).first(connection));
723 /// # }
724 /// ```
725 fn find<PK>(self, id: PK) -> Find<Self, PK>
726 where
727 Self: methods::FindDsl<PK>,
728 {
729 methods::FindDsl::find(self, id)
730 }
731
732 /// Sets the order clause of a query.
733 ///
734 /// If there was already an order clause, it will be overridden. See
735 /// also:
736 /// [`.desc()`](crate::expression_methods::ExpressionMethods::desc())
737 /// and
738 /// [`.asc()`](crate::expression_methods::ExpressionMethods::asc())
739 ///
740 /// Ordering by multiple columns can be achieved by passing a tuple of those
741 /// columns.
742 /// To construct an order clause of an unknown number of columns,
743 /// see [`QueryDsl::then_order_by`](QueryDsl::then_order_by())
744 ///
745 /// # Examples
746 ///
747 /// ```rust
748 /// # include!("../doctest_setup.rs");
749 /// #
750 /// # fn main() {
751 /// # run_test();
752 /// # }
753 /// #
754 /// # fn run_test() -> QueryResult<()> {
755 /// # use schema::users::dsl::*;
756 /// # let connection = &mut establish_connection();
757 /// # diesel::sql_query("DELETE FROM users").execute(connection)?;
758 /// diesel::insert_into(users)
759 /// .values(&vec![
760 /// name.eq("Saul"),
761 /// name.eq("Steve"),
762 /// name.eq("Stan"),
763 /// ])
764 /// .execute(connection)?;
765 ///
766 /// let ordered_names = users.select(name)
767 /// .order(name.desc())
768 /// .load::<String>(connection)?;
769 /// assert_eq!(vec!["Steve", "Stan", "Saul"], ordered_names);
770 ///
771 /// diesel::insert_into(users).values(name.eq("Stan")).execute(connection)?;
772 ///
773 /// let data = users.select((name, id))
774 /// .order((name.asc(), id.desc()))
775 /// .load(connection)?;
776 /// let expected_data = vec![
777 /// (String::from("Saul"), 3),
778 /// (String::from("Stan"), 6),
779 /// (String::from("Stan"), 5),
780 /// (String::from("Steve"), 4),
781 /// ];
782 /// assert_eq!(expected_data, data);
783 /// # Ok(())
784 /// # }
785 /// ```
786 fn order<Expr>(self, expr: Expr) -> Order<Self, Expr>
787 where
788 Expr: Expression,
789 Self: methods::OrderDsl<Expr>,
790 {
791 methods::OrderDsl::order(self, expr)
792 }
793
794 /// Alias for `order`
795 fn order_by<Expr>(self, expr: Expr) -> OrderBy<Self, Expr>
796 where
797 Expr: Expression,
798 Self: methods::OrderDsl<Expr>,
799 {
800 QueryDsl::order(self, expr)
801 }
802
803 /// Appends to the `ORDER BY` clause of this SQL query.
804 ///
805 /// Unlike `.order`, this method will append rather than replace.
806 /// In other words,
807 /// `.order_by(foo).order_by(bar)` is equivalent to `.order_by(bar)`.
808 /// In contrast,
809 /// `.order_by(foo).then_order_by(bar)` is equivalent to `.order((foo, bar))`.
810 /// This method is only present on boxed queries.
811 ///
812 /// # Examples
813 ///
814 /// ```rust
815 /// # include!("../doctest_setup.rs");
816 /// #
817 /// # fn main() {
818 /// # run_test();
819 /// # }
820 /// #
821 /// # fn run_test() -> QueryResult<()> {
822 /// # use schema::users::dsl::*;
823 /// # let connection = &mut establish_connection();
824 /// # diesel::sql_query("DELETE FROM users").execute(connection)?;
825 /// diesel::insert_into(users)
826 /// .values(&vec![
827 /// name.eq("Saul"),
828 /// name.eq("Steve"),
829 /// name.eq("Stan"),
830 /// name.eq("Stan"),
831 /// ])
832 /// .execute(connection)?;
833 ///
834 /// let data = users.select((name, id))
835 /// .order_by(name.asc())
836 /// .then_order_by(id.desc())
837 /// .load(connection)?;
838 /// let expected_data = vec![
839 /// (String::from("Saul"), 3),
840 /// (String::from("Stan"), 6),
841 /// (String::from("Stan"), 5),
842 /// (String::from("Steve"), 4),
843 /// ];
844 /// assert_eq!(expected_data, data);
845 /// # Ok(())
846 /// # }
847 /// ```
848 fn then_order_by<Order>(self, order: Order) -> ThenOrderBy<Self, Order>
849 where
850 Self: methods::ThenOrderDsl<Order>,
851 {
852 methods::ThenOrderDsl::then_order_by(self, order)
853 }
854
855 /// Sets the limit clause of the query.
856 ///
857 /// If there was already a limit clause, it will be overridden.
858 ///
859 /// # Example
860 ///
861 /// ```rust
862 /// # include!("../doctest_setup.rs");
863 /// # use schema::users;
864 /// #
865 /// # fn main() {
866 /// # run_test().unwrap();
867 /// # }
868 /// #
869 /// # fn run_test() -> QueryResult<()> {
870 /// # use self::users::dsl::*;
871 /// # let connection = &mut establish_connection();
872 /// # diesel::delete(users).execute(connection)?;
873 /// # diesel::insert_into(users)
874 /// # .values(&vec![
875 /// # name.eq("Sean"),
876 /// # name.eq("Bastien"),
877 /// # name.eq("Pascal"),
878 /// # ])
879 /// # .execute(connection)?;
880 /// #
881 /// // Using a limit
882 /// let limited = users.select(name)
883 /// .order(id)
884 /// .limit(1)
885 /// .load::<String>(connection)?;
886 ///
887 /// // Without a limit
888 /// let no_limit = users.select(name)
889 /// .order(id)
890 /// .load::<String>(connection)?;
891 ///
892 /// assert_eq!(vec!["Sean"], limited);
893 /// assert_eq!(vec!["Sean", "Bastien", "Pascal"], no_limit);
894 /// # Ok(())
895 /// # }
896 /// ```
897 fn limit(self, limit: i64) -> Limit<Self>
898 where
899 Self: methods::LimitDsl,
900 {
901 methods::LimitDsl::limit(self, limit)
902 }
903
904 /// Sets the offset clause of the query.
905 ///
906 /// If there was already a offset clause, it will be overridden.
907 ///
908 /// # Example
909 ///
910 /// ```rust
911 /// # include!("../doctest_setup.rs");
912 /// # use schema::users;
913 /// #
914 /// # fn main() {
915 /// # run_test().unwrap();
916 /// # }
917 /// #
918 /// # fn run_test() -> QueryResult<()> {
919 /// # use self::users::dsl::*;
920 /// # let connection = &mut establish_connection();
921 /// # diesel::delete(users).execute(connection)?;
922 /// # diesel::insert_into(users)
923 /// # .values(&vec![
924 /// # name.eq("Sean"),
925 /// # name.eq("Bastien"),
926 /// # name.eq("Pascal"),
927 /// # ])
928 /// # .execute(connection)?;
929 /// #
930 /// // Using an offset
931 /// let offset = users.select(name)
932 /// .order(id)
933 /// .limit(2)
934 /// .offset(1)
935 /// .load::<String>(connection)?;
936 ///
937 /// // No Offset
938 /// let no_offset = users.select(name)
939 /// .order(id)
940 /// .limit(2)
941 /// .load::<String>(connection)?;
942 ///
943 /// assert_eq!(vec!["Bastien", "Pascal"], offset);
944 /// assert_eq!(vec!["Sean", "Bastien"], no_offset);
945 /// # Ok(())
946 /// # }
947 /// ```
948 fn offset(self, offset: i64) -> Offset<Self>
949 where
950 Self: methods::OffsetDsl,
951 {
952 methods::OffsetDsl::offset(self, offset)
953 }
954
955 /// Sets the `group by` clause of a query.
956 ///
957 /// **Note:** Queries having a `group by` clause require a custom select clause.
958 /// Use [`QueryDsl::select()`] to specify one.
959 ///
960 /// **Note:** When using `group_by` with `select`, the `group_by` call must appear
961 /// before the `select` call in the query chain.
962 ///
963 /// If there was already a group by clause, it will be overridden.
964 /// Grouping by multiple columns can be achieved by passing a tuple of those
965 /// columns.
966 ///
967 /// Diesel follows postgresql's group by semantic, this means any column
968 /// appearing in a group by clause is considered to be aggregated. If a
969 /// primary key is part of the group by clause every column from the
970 /// corresponding table is considered to be aggregated. Select clauses
971 /// cannot mix aggregated and non aggregated expressions.
972 ///
973 /// For group by clauses containing columns from more than one table it
974 /// is required to call [`allow_columns_to_appear_in_same_group_by_clause!`]
975 ///
976 /// [`allow_columns_to_appear_in_same_group_by_clause!`]: crate::allow_columns_to_appear_in_same_group_by_clause!
977 ///
978 /// # Examples
979 /// ```rust
980 /// # include!("../doctest_setup.rs");
981 /// # fn main() {
982 /// # run_test();
983 /// # }
984 /// #
985 /// # fn run_test() -> QueryResult<()> {
986 /// # use crate::schema::{users, posts};
987 /// # use diesel::dsl::count;
988 /// # let connection = &mut establish_connection();
989 /// let data = users::table.inner_join(posts::table)
990 /// .group_by(users::id)
991 /// .select((users::name, count(posts::id)))
992 /// # .order_by(users::id.asc())
993 /// .load::<(String, i64)>(connection)?;
994 ///
995 /// assert_eq!(vec![(String::from("Sean"), 2), (String::from("Tess"), 1)], data);
996 /// # Ok(())
997 /// # }
998 /// ```
999 fn group_by<GB>(self, group_by: GB) -> GroupBy<Self, GB>
1000 where
1001 GB: Expression,
1002 Self: methods::GroupByDsl<GB>,
1003 {
1004 methods::GroupByDsl::group_by(self, group_by)
1005 }
1006
1007 /// Adds to the `HAVING` clause of a query.
1008 ///
1009 /// # Examples
1010 /// ```rust
1011 /// # include!("../doctest_setup.rs");
1012 /// # fn main() {
1013 /// # run_test();
1014 /// # }
1015 /// #
1016 /// # fn run_test() -> QueryResult<()> {
1017 /// # use crate::schema::{users, posts};
1018 /// # use diesel::dsl::count;
1019 /// # let connection = &mut establish_connection();
1020 /// let data = users::table.inner_join(posts::table)
1021 /// .group_by(users::id)
1022 /// .having(count(posts::id).gt(1))
1023 /// .select((users::name, count(posts::id)))
1024 /// .load::<(String, i64)>(connection)?;
1025 ///
1026 /// assert_eq!(vec![(String::from("Sean"), 2)], data);
1027 /// # Ok(())
1028 /// # }
1029 /// ```
1030 fn having<Predicate>(self, predicate: Predicate) -> Having<Self, Predicate>
1031 where
1032 Self: methods::HavingDsl<Predicate>,
1033 {
1034 methods::HavingDsl::having(self, predicate)
1035 }
1036
1037 /// Adds `FOR UPDATE` to the end of the select statement.
1038 ///
1039 /// This method is only available for MySQL and PostgreSQL. SQLite does not
1040 /// provide any form of row locking.
1041 ///
1042 /// Additionally, `.for_update` cannot be used on queries with a distinct
1043 /// clause, group by clause, having clause, or any unions. Queries with
1044 /// a `FOR UPDATE` clause cannot be boxed.
1045 ///
1046 /// # Example
1047 ///
1048 /// ```
1049 /// # include!("../doctest_setup.rs");
1050 /// # fn main() {
1051 /// # run_test();
1052 /// # }
1053 /// #
1054 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1055 /// # fn run_test() -> QueryResult<()> {
1056 /// # use crate::schema::users;
1057 /// # let connection = &mut establish_connection();
1058 /// // Executes `SELECT * FROM users FOR UPDATE`
1059 /// let users_for_update = users::table.for_update().load(connection)?;
1060 /// # let u: Vec<(i32, String)> = users_for_update;
1061 /// # Ok(())
1062 /// # }
1063 /// # #[cfg(feature = "sqlite")]
1064 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1065 /// ```
1066 fn for_update(self) -> ForUpdate<Self>
1067 where
1068 Self: methods::LockingDsl<lock::ForUpdate>,
1069 {
1070 methods::LockingDsl::with_lock(self, lock::ForUpdate)
1071 }
1072
1073 /// Adds `FOR NO KEY UPDATE` to the end of the select statement.
1074 ///
1075 /// This method is only available for PostgreSQL. SQLite does not
1076 /// provide any form of row locking, and MySQL does not support anything
1077 /// finer than row-level locking.
1078 ///
1079 /// Additionally, `.for_no_key_update` cannot be used on queries with a distinct
1080 /// clause, group by clause, having clause, or any unions. Queries with
1081 /// a `FOR NO KEY UPDATE` clause cannot be boxed.
1082 ///
1083 /// # Example
1084 ///
1085 /// ```
1086 /// # include!("../doctest_setup.rs");
1087 /// # fn main() {
1088 /// # run_test();
1089 /// # }
1090 /// #
1091 /// # #[cfg(feature = "postgres")]
1092 /// # fn run_test() -> QueryResult<()> {
1093 /// # use crate::schema::users;
1094 /// # let connection = &mut establish_connection();
1095 /// // Executes `SELECT * FROM users FOR NO KEY UPDATE`
1096 /// let users_for_no_key_update = users::table.for_no_key_update().load(connection)?;
1097 /// # let u: Vec<(i32, String)> = users_for_no_key_update;
1098 /// # Ok(())
1099 /// # }
1100 /// # #[cfg(not(feature = "postgres"))]
1101 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1102 /// ```
1103 fn for_no_key_update(self) -> ForNoKeyUpdate<Self>
1104 where
1105 Self: methods::LockingDsl<lock::ForNoKeyUpdate>,
1106 {
1107 methods::LockingDsl::with_lock(self, lock::ForNoKeyUpdate)
1108 }
1109
1110 /// Adds `FOR SHARE` to the end of the select statement.
1111 ///
1112 /// This method is only available for MySQL and PostgreSQL. SQLite does not
1113 /// provide any form of row locking.
1114 ///
1115 /// Additionally, `.for_share` cannot be used on queries with a distinct
1116 /// clause, group by clause, having clause, or any unions. Queries with
1117 /// a `FOR SHARE` clause cannot be boxed.
1118 ///
1119 /// # Example
1120 ///
1121 /// ```
1122 /// # include!("../doctest_setup.rs");
1123 /// # fn main() {
1124 /// # run_test();
1125 /// # }
1126 /// #
1127 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1128 /// # fn run_test() -> QueryResult<()> {
1129 /// # use crate::schema::users;
1130 /// # let connection = &mut establish_connection();
1131 /// // Executes `SELECT * FROM users FOR SHARE`
1132 /// let users_for_share = users::table.for_share().load(connection)?;
1133 /// # let u: Vec<(i32, String)> = users_for_share;
1134 /// # Ok(())
1135 /// # }
1136 /// # #[cfg(feature = "sqlite")]
1137 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1138 /// ```
1139 fn for_share(self) -> ForShare<Self>
1140 where
1141 Self: methods::LockingDsl<lock::ForShare>,
1142 {
1143 methods::LockingDsl::with_lock(self, lock::ForShare)
1144 }
1145
1146 /// Adds `FOR KEY SHARE` to the end of the select statement.
1147 ///
1148 /// This method is only available for PostgreSQL. SQLite does not
1149 /// provide any form of row locking, and MySQL does not support anything
1150 /// finer than row-level locking.
1151 ///
1152 /// Additionally, `.for_key_share` cannot be used on queries with a distinct
1153 /// clause, group by clause, having clause, or any unions. Queries with
1154 /// a `FOR KEY SHARE` clause cannot be boxed.
1155 ///
1156 /// # Example
1157 ///
1158 /// ```
1159 /// # include!("../doctest_setup.rs");
1160 /// # fn main() {
1161 /// # run_test();
1162 /// # }
1163 ///
1164 /// # #[cfg(feature = "postgres")]
1165 /// # fn run_test() -> QueryResult<()> {
1166 /// # use crate::schema::users;
1167 /// # let connection = &mut establish_connection();
1168 /// // Executes `SELECT * FROM users FOR KEY SHARE`
1169 /// let users_for_key_share = users::table.for_key_share().load(connection)?;
1170 /// # let u: Vec<(i32, String)> = users_for_key_share;
1171 /// # Ok(())
1172 /// # }
1173 /// # #[cfg(not(feature = "postgres"))]
1174 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1175 /// ```
1176 fn for_key_share(self) -> ForKeyShare<Self>
1177 where
1178 Self: methods::LockingDsl<lock::ForKeyShare>,
1179 {
1180 methods::LockingDsl::with_lock(self, lock::ForKeyShare)
1181 }
1182
1183 /// Adds `SKIP LOCKED` to the end of a `FOR UPDATE` clause.
1184 ///
1185 /// This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.
1186 ///
1187 /// # Example
1188 ///
1189 /// ```
1190 /// # include!("../doctest_setup.rs");
1191 /// # fn main() {
1192 /// # run_test();
1193 /// # }
1194 /// #
1195 /// # #[cfg(any(feature = "postgres", feature = "mysql"))]
1196 /// # fn run_test() -> QueryResult<()> {
1197 /// # use crate::schema::users;
1198 /// # let connection = &mut establish_connection();
1199 /// // Executes `SELECT * FROM users FOR UPDATE SKIP LOCKED`
1200 /// let user_skipped_locked = users::table.for_update().skip_locked().load(connection)?;
1201 /// # let u: Vec<(i32, String)> = user_skipped_locked;
1202 /// # Ok(())
1203 /// # }
1204 /// # #[cfg(feature = "sqlite")]
1205 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1206 /// ```
1207 fn skip_locked(self) -> SkipLocked<Self>
1208 where
1209 Self: methods::ModifyLockDsl<lock::SkipLocked>,
1210 {
1211 methods::ModifyLockDsl::modify_lock(self, lock::SkipLocked)
1212 }
1213
1214 /// Adds `NOWAIT` to the end of a `FOR UPDATE` clause.
1215 ///
1216 /// This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.
1217 ///
1218 /// # Example
1219 ///
1220 /// ```
1221 /// # include!("../doctest_setup.rs");
1222 /// # fn main() {
1223 /// # run_test();
1224 /// # }
1225 /// #
1226 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1227 /// # fn run_test() -> QueryResult<()> {
1228 /// # use crate::schema::users;
1229 /// # let connection = &mut establish_connection();
1230 /// // Executes `SELECT * FROM users FOR UPDATE NOWAIT`
1231 /// let users_no_wait = users::table.for_update().no_wait().load(connection)?;
1232 /// # let u: Vec<(i32, String)> = users_no_wait;
1233 /// # Ok(())
1234 /// # }
1235 /// # #[cfg(feature = "sqlite")]
1236 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1237 /// ```
1238 fn no_wait(self) -> NoWait<Self>
1239 where
1240 Self: methods::ModifyLockDsl<lock::NoWait>,
1241 {
1242 methods::ModifyLockDsl::modify_lock(self, lock::NoWait)
1243 }
1244
1245 /// Boxes the pieces of a query into a single type.
1246 ///
1247 /// This is useful for cases where you want to conditionally modify a query,
1248 /// but need the type to remain the same. The backend must be specified as
1249 /// part of this. It is not possible to box a query and have it be useable
1250 /// on multiple backends.
1251 ///
1252 /// A boxed query will incur a minor performance penalty, as the query builder
1253 /// can no longer be inlined by the compiler. For most applications this cost
1254 /// will be minimal.
1255 ///
1256 /// ### Example
1257 ///
1258 /// ```rust
1259 /// # include!("../doctest_setup.rs");
1260 /// # use schema::users;
1261 /// #
1262 /// # fn main() {
1263 /// # use std::collections::HashMap;
1264 /// # let connection = &mut establish_connection();
1265 /// # let mut params = HashMap::new();
1266 /// # params.insert("name", "Sean");
1267 /// let mut query = users::table.into_boxed();
1268 /// if let Some(name) = params.get("name") {
1269 /// query = query.filter(users::name.eq(name));
1270 /// }
1271 /// let users = query.load(connection);
1272 /// # let expected = vec![(1, String::from("Sean"))];
1273 /// # assert_eq!(Ok(expected), users);
1274 /// # }
1275 /// ```
1276 ///
1277 /// Diesel queries also have a similar problem to [`Iterator`], where
1278 /// returning them from a function requires exposing the implementation of that
1279 /// function. The [`helper_types`][helper_types] module exists to help with this,
1280 /// but you might want to hide the return type or have it conditionally change.
1281 /// Boxing can achieve both.
1282 ///
1283 /// [helper_types]: crate::helper_types
1284 ///
1285 /// ### Example
1286 ///
1287 /// ```rust
1288 /// # include!("../doctest_setup.rs");
1289 /// # use schema::users;
1290 /// #
1291 /// # fn main() {
1292 /// # let connection = &mut establish_connection();
1293 /// fn users_by_name(name: &str) -> users::BoxedQuery<DB> {
1294 /// users::table.filter(users::name.eq(name)).into_boxed()
1295 /// }
1296 ///
1297 /// assert_eq!(Ok(1), users_by_name("Sean").select(users::id).first(connection));
1298 /// assert_eq!(Ok(2), users_by_name("Tess").select(users::id).first(connection));
1299 /// # }
1300 /// ```
1301 fn into_boxed<'a, DB>(self) -> IntoBoxed<'a, Self, DB>
1302 where
1303 DB: Backend,
1304 Self: methods::BoxedDsl<'a, DB>,
1305 {
1306 methods::BoxedDsl::internal_into_boxed(self)
1307 }
1308
1309 /// Wraps this select statement in parenthesis, allowing it to be used
1310 /// as an expression.
1311 ///
1312 /// SQL allows queries such as `foo = (SELECT ...)`, as long as the
1313 /// subselect returns only a single column, and 0 or 1 rows. This method
1314 /// indicates that you expect the query to only return a single value (this
1315 /// will be enforced by adding `LIMIT 1`).
1316 ///
1317 /// The SQL type of this will always be `Nullable`, as the query returns
1318 /// `NULL` if the table is empty or it otherwise returns 0 rows.
1319 ///
1320 /// # Example
1321 ///
1322 /// ```rust
1323 /// # include!("../doctest_setup.rs");
1324 /// #
1325 /// # fn main() {
1326 /// # run_test();
1327 /// # }
1328 /// #
1329 /// # fn run_test() -> QueryResult<()> {
1330 /// # use diesel::insert_into;
1331 /// # use schema::users::dsl::*;
1332 /// # use schema::posts;
1333 /// # let connection = &mut establish_connection();
1334 /// insert_into(posts::table)
1335 /// .values(posts::user_id.eq(1))
1336 /// .execute(connection)?;
1337 /// let last_post = posts::table
1338 /// .order(posts::id.desc());
1339 /// let most_recently_active_user = users.select(name)
1340 /// .filter(id.nullable().eq(last_post.select(posts::user_id).single_value()))
1341 /// .first::<String>(connection)?;
1342 /// assert_eq!("Sean", most_recently_active_user);
1343 /// # Ok(())
1344 /// # }
1345 /// ```
1346 fn single_value(self) -> SingleValue<Self>
1347 where
1348 Self: methods::SingleValueDsl,
1349 {
1350 methods::SingleValueDsl::single_value(self)
1351 }
1352
1353 /// Coerce the SQL type of the select clause to it's nullable equivalent.
1354 ///
1355 /// This is useful for writing queries that contain subselects on non null
1356 /// fields comparing them to nullable fields.
1357 /// ```rust
1358 /// # include!("../doctest_setup.rs");
1359 /// #
1360 /// # fn main() {
1361 /// # run_test();
1362 /// # }
1363 /// #
1364 /// # fn run_test() -> QueryResult<()> {
1365 /// # let connection = &mut establish_connection();
1366 /// table! {
1367 /// users {
1368 /// id -> Integer,
1369 /// name -> Text,
1370 /// }
1371 /// }
1372 ///
1373 /// table! {
1374 /// posts {
1375 /// id -> Integer,
1376 /// by_user -> Nullable<Text>,
1377 /// }
1378 /// }
1379 ///
1380 /// allow_tables_to_appear_in_same_query!(users, posts);
1381 ///
1382 /// # let _: Vec<(i32, Option<String>)> =
1383 /// posts::table.filter(
1384 /// posts::by_user.eq_any(users::table.select(users::name).nullable())
1385 /// ).load(connection)?;
1386 /// # Ok(())
1387 /// # }
1388 fn nullable(self) -> NullableSelect<Self>
1389 where
1390 Self: methods::SelectNullableDsl,
1391 {
1392 methods::SelectNullableDsl::nullable(self)
1393 }
1394}
1395
1396impl<T: Table> QueryDsl for T {}
1397
1398/// Methods used to execute queries.
1399pub trait RunQueryDsl<Conn>: Sized {
1400 /// Executes the given command, returning the number of rows affected.
1401 ///
1402 /// `execute` is usually used in conjunction with [`insert_into`](crate::insert_into()),
1403 /// [`update`](crate::update()) and [`delete`](crate::delete()) where the number of
1404 /// affected rows is often enough information.
1405 ///
1406 /// When asking the database to return data from a query, [`load`](crate::query_dsl::RunQueryDsl::load()) should
1407 /// probably be used instead.
1408 ///
1409 /// # Example
1410 ///
1411 /// ```rust
1412 /// # include!("../doctest_setup.rs");
1413 /// #
1414 /// # fn main() {
1415 /// # run_test();
1416 /// # }
1417 /// #
1418 /// # fn run_test() -> QueryResult<()> {
1419 /// # use diesel::insert_into;
1420 /// # use schema::users::dsl::*;
1421 /// # let connection = &mut establish_connection();
1422 /// let inserted_rows = insert_into(users)
1423 /// .values(name.eq("Ruby"))
1424 /// .execute(connection)?;
1425 /// assert_eq!(1, inserted_rows);
1426 ///
1427 /// let inserted_rows = insert_into(users)
1428 /// .values(&vec![name.eq("Jim"), name.eq("James")])
1429 /// .execute(connection)?;
1430 /// assert_eq!(2, inserted_rows);
1431 /// # Ok(())
1432 /// # }
1433 /// ```
1434 fn execute(self, conn: &mut Conn) -> QueryResult<usize>
1435 where
1436 Conn: Connection,
1437 Self: methods::ExecuteDsl<Conn>,
1438 {
1439 methods::ExecuteDsl::execute(self, conn)
1440 }
1441
1442 /// Executes the given query, returning a [`Vec`] with the returned rows.
1443 ///
1444 /// When using the query builder, the return type can be
1445 /// a tuple of the values, or a struct which implements [`Queryable`].
1446 ///
1447 /// When this method is called on [`sql_query`],
1448 /// the return type can only be a struct which implements [`QueryableByName`]
1449 ///
1450 /// For insert, update, and delete operations where only a count of affected is needed,
1451 /// [`execute`] should be used instead.
1452 ///
1453 /// [`Queryable`]: crate::deserialize::Queryable
1454 /// [`QueryableByName`]: crate::deserialize::QueryableByName
1455 /// [`execute`]: crate::query_dsl::RunQueryDsl::execute()
1456 /// [`sql_query`]: crate::sql_query()
1457 ///
1458 /// ## How to resolve compiler errors while loading data from the database
1459 ///
1460 /// In case you getting uncomprehensable compiler errors while loading data
1461 /// from the database into a type using [`#[derive(Queryable)]`](derive@crate::prelude::Queryable)
1462 /// you might want to consider
1463 /// using [`#[derive(Selectable)]`](derive@crate::prelude::Selectable) +
1464 /// `#[diesel(check_for_backend(YourBackendType))]`
1465 /// to check for mismatching fields at compile time. This drastically improves
1466 /// the quality of the generated error messages by pointing to concrete type mismatches at
1467 /// field level.You need to specify the concrete database backend
1468 /// this specific struct is indented to be used with, as otherwise rustc cannot correctly
1469 /// identify the required deserialization implementation.
1470 ///
1471 /// # Examples
1472 ///
1473 /// ## Returning a single field
1474 ///
1475 /// ```rust
1476 /// # include!("../doctest_setup.rs");
1477 /// #
1478 /// # fn main() {
1479 /// # run_test();
1480 /// # }
1481 /// #
1482 /// # fn run_test() -> QueryResult<()> {
1483 /// # use diesel::insert_into;
1484 /// # use schema::users::dsl::*;
1485 /// # let connection = &mut establish_connection();
1486 /// let data = users.select(name)
1487 /// .load::<String>(connection)?;
1488 /// assert_eq!(vec!["Sean", "Tess"], data);
1489 /// # Ok(())
1490 /// # }
1491 /// ```
1492 ///
1493 /// ## Returning a tuple
1494 ///
1495 /// ```rust
1496 /// # include!("../doctest_setup.rs");
1497 /// #
1498 /// # fn main() {
1499 /// # run_test();
1500 /// # }
1501 /// #
1502 /// # fn run_test() -> QueryResult<()> {
1503 /// # use diesel::insert_into;
1504 /// # use schema::users::dsl::*;
1505 /// # let connection = &mut establish_connection();
1506 /// let data = users
1507 /// .load::<(i32, String)>(connection)?;
1508 /// let expected_data = vec![
1509 /// (1, String::from("Sean")),
1510 /// (2, String::from("Tess")),
1511 /// ];
1512 /// assert_eq!(expected_data, data);
1513 /// # Ok(())
1514 /// # }
1515 /// ```
1516 ///
1517 /// ## Returning a struct
1518 ///
1519 /// ```rust
1520 /// # include!("../doctest_setup.rs");
1521 /// #
1522 /// #[derive(Queryable, PartialEq, Debug)]
1523 /// struct User {
1524 /// id: i32,
1525 /// name: String,
1526 /// }
1527 ///
1528 /// # fn main() {
1529 /// # run_test();
1530 /// # }
1531 /// #
1532 /// # fn run_test() -> QueryResult<()> {
1533 /// # use diesel::insert_into;
1534 /// # use schema::users::dsl::*;
1535 /// # let connection = &mut establish_connection();
1536 /// let data = users
1537 /// .load::<User>(connection)?;
1538 /// let expected_data = vec![
1539 /// User { id: 1, name: String::from("Sean") },
1540 /// User { id: 2, name: String::from("Tess") },
1541 /// ];
1542 /// assert_eq!(expected_data, data);
1543 /// # Ok(())
1544 /// # }
1545 /// ```
1546 fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
1547 where
1548 Self: LoadQuery<'query, Conn, U>,
1549 {
1550 self.internal_load(conn)?.collect()
1551 }
1552
1553 /// Executes the given query, returning an [`Iterator`] with the returned rows.
1554 ///
1555 /// The iterator's item is [`QueryResult<U>`](crate::result::QueryResult).
1556 ///
1557 /// You should normally prefer to use [`RunQueryDsl::load`] instead. This method
1558 /// is provided for situations where the result needs to be collected into a different
1559 /// container than a [`Vec`]
1560 ///
1561 /// When using the query builder, the return type can be
1562 /// a tuple of the values, or a struct which implements [`Queryable`].
1563 /// This type is specified by the first generic type of this function.
1564 ///
1565 /// The second generic type parameter specifies the so called loading mode,
1566 /// which describes how the connection implementation loads data from the database.
1567 /// All connections should provide a implementation for
1568 /// [`DefaultLoadingMode`](crate::connection::DefaultLoadingMode).
1569 ///
1570 /// They may provide additional modes. Checkout the documentation of the concrete
1571 /// connection types for details. For connection implementations that provide
1572 /// more than one loading mode it is **required** to specify this generic parameter.
1573 /// This is currently true for `PgConnection`.
1574 ///
1575 /// When this method is called on [`sql_query`],
1576 /// the return type can only be a struct which implements [`QueryableByName`]
1577 ///
1578 /// For insert, update, and delete operations where only a count of affected is needed,
1579 /// [`execute`] should be used instead.
1580 ///
1581 /// [`Queryable`]: crate::deserialize::Queryable
1582 /// [`QueryableByName`]: crate::deserialize::QueryableByName
1583 /// [`execute`]: crate::query_dsl::RunQueryDsl::execute()
1584 /// [`sql_query`]: crate::sql_query()
1585 ///
1586 /// # Examples
1587 ///
1588 /// ## Returning a single field
1589 ///
1590 /// ```rust
1591 /// # include!("../doctest_setup.rs");
1592 /// #
1593 /// # fn main() {
1594 /// # run_test();
1595 /// # }
1596 /// #
1597 /// # fn run_test() -> QueryResult<()> {
1598 /// # use diesel::insert_into;
1599 /// # use schema::users::dsl::*;
1600 /// # let connection = &mut establish_connection();
1601 /// use diesel::connection::DefaultLoadingMode;
1602 ///
1603 /// let data = users.select(name)
1604 /// .load_iter::<String, DefaultLoadingMode>(connection)?
1605 /// .collect::<QueryResult<Vec<_>>>()?;
1606 /// assert_eq!(vec!["Sean", "Tess"], data);
1607 /// # Ok(())
1608 /// # }
1609 /// ```
1610 ///
1611 /// ## Returning a tuple
1612 ///
1613 /// ```rust
1614 /// # include!("../doctest_setup.rs");
1615 /// #
1616 /// # fn main() {
1617 /// # run_test();
1618 /// # }
1619 /// #
1620 /// # fn run_test() -> QueryResult<()> {
1621 /// # use diesel::insert_into;
1622 /// # use schema::users::dsl::*;
1623 /// # let connection = &mut establish_connection();
1624 /// use diesel::connection::DefaultLoadingMode;
1625 ///
1626 /// let data = users
1627 /// .load_iter::<(i32, String), DefaultLoadingMode>(connection)?
1628 /// .collect::<QueryResult<Vec<_>>>()?;
1629 /// let expected_data = vec![
1630 /// (1, String::from("Sean")),
1631 /// (2, String::from("Tess")),
1632 /// ];
1633 /// assert_eq!(expected_data, data);
1634 /// # Ok(())
1635 /// # }
1636 /// ```
1637 ///
1638 /// ## Returning a struct
1639 ///
1640 /// ```rust
1641 /// # include!("../doctest_setup.rs");
1642 /// #
1643 /// #[derive(Queryable, PartialEq, Debug)]
1644 /// struct User {
1645 /// id: i32,
1646 /// name: String,
1647 /// }
1648 ///
1649 /// # fn main() {
1650 /// # run_test();
1651 /// # }
1652 /// #
1653 /// # fn run_test() -> QueryResult<()> {
1654 /// # use diesel::insert_into;
1655 /// # use schema::users::dsl::*;
1656 /// # let connection = &mut establish_connection();
1657 /// use diesel::connection::DefaultLoadingMode;
1658 ///
1659 /// let data = users
1660 /// .load_iter::<User, DefaultLoadingMode>(connection)?
1661 /// .collect::<QueryResult<Vec<_>>>()?;
1662 /// let expected_data = vec![
1663 /// User { id: 1, name: String::from("Sean") },
1664 /// User { id: 2, name: String::from("Tess") },
1665 /// ];
1666 /// assert_eq!(expected_data, data);
1667 /// # Ok(())
1668 /// # }
1669 /// ```
1670 fn load_iter<'conn, 'query: 'conn, U, B>(
1671 self,
1672 conn: &'conn mut Conn,
1673 ) -> QueryResult<Self::RowIter<'conn>>
1674 where
1675 U: 'conn,
1676 Self: LoadQuery<'query, Conn, U, B> + 'conn,
1677 {
1678 self.internal_load(conn)
1679 }
1680
1681 /// Runs the command, and returns the affected row.
1682 ///
1683 /// `Err(NotFound)` will be returned if the query affected 0 rows. You can
1684 /// call `.optional()` on the result of this if the command was optional to
1685 /// get back a `Result<Option<U>>`
1686 ///
1687 /// When this method is called on an insert, update, or delete statement,
1688 /// it will implicitly add a `RETURNING *` to the query,
1689 /// unless a returning clause was already specified.
1690 ///
1691 /// This method only returns the first row that was affected, even if more
1692 /// rows are affected.
1693 ///
1694 /// # Example
1695 ///
1696 /// ```rust
1697 /// # include!("../doctest_setup.rs");
1698 /// #
1699 /// # fn main() {
1700 /// # run_test();
1701 /// # }
1702 /// #
1703 /// # #[cfg(feature = "postgres")]
1704 /// # fn run_test() -> QueryResult<()> {
1705 /// # use diesel::{insert_into, update};
1706 /// # use schema::users::dsl::*;
1707 /// # let connection = &mut establish_connection();
1708 /// let inserted_row = insert_into(users)
1709 /// .values(name.eq("Ruby"))
1710 /// .get_result(connection)?;
1711 /// assert_eq!((3, String::from("Ruby")), inserted_row);
1712 ///
1713 /// // This will return `NotFound`, as there is no user with ID 4
1714 /// let update_result = update(users.find(4))
1715 /// .set(name.eq("Jim"))
1716 /// .get_result::<(i32, String)>(connection);
1717 /// assert_eq!(Err(diesel::NotFound), update_result);
1718 /// # Ok(())
1719 /// # }
1720 /// #
1721 /// # #[cfg(not(feature = "postgres"))]
1722 /// # fn run_test() -> QueryResult<()> {
1723 /// # Ok(())
1724 /// # }
1725 /// ```
1726 fn get_result<'query, U>(self, conn: &mut Conn) -> QueryResult<U>
1727 where
1728 Self: LoadQuery<'query, Conn, U>,
1729 {
1730 match self.internal_load(conn)?.next() {
1731 Some(v) => v,
1732 None => Err(crate::result::Error::NotFound),
1733 }
1734 }
1735
1736 /// Runs the command, returning an `Vec` with the affected rows.
1737 ///
1738 /// This method is an alias for [`load`], but with a name that makes more
1739 /// sense for insert, update, and delete statements.
1740 ///
1741 /// [`load`]: crate::query_dsl::RunQueryDsl::load()
1742 fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
1743 where
1744 Self: LoadQuery<'query, Conn, U>,
1745 {
1746 self.load(conn)
1747 }
1748
1749 /// Attempts to load a single record.
1750 ///
1751 /// This method is equivalent to `.limit(1).get_result()`
1752 ///
1753 /// Returns `Ok(record)` if found, and `Err(NotFound)` if no results are
1754 /// returned. If the query truly is optional, you can call `.optional()` on
1755 /// the result of this to get a `Result<Option<U>>`.
1756 ///
1757 /// # Example:
1758 ///
1759 /// ```rust
1760 /// # include!("../doctest_setup.rs");
1761 /// # fn main() {
1762 /// # run_test();
1763 /// # }
1764 /// #
1765 /// # fn run_test() -> QueryResult<()> {
1766 /// # use schema::users::dsl::*;
1767 /// # let connection = &mut establish_connection();
1768 /// diesel::insert_into(users)
1769 /// .values(&vec![name.eq("Sean"), name.eq("Pascal")])
1770 /// .execute(connection)?;
1771 ///
1772 /// let first_name = users.order(id).select(name).first(connection);
1773 /// assert_eq!(Ok(String::from("Sean")), first_name);
1774 ///
1775 /// let not_found = users
1776 /// .filter(name.eq("Foo"))
1777 /// .first::<(i32, String)>(connection);
1778 /// assert_eq!(Err(diesel::NotFound), not_found);
1779 /// # Ok(())
1780 /// # }
1781 /// ```
1782 fn first<'query, U>(self, conn: &mut Conn) -> QueryResult<U>
1783 where
1784 Self: methods::LimitDsl,
1785 Limit<Self>: LoadQuery<'query, Conn, U>,
1786 {
1787 methods::LimitDsl::limit(self, 1).get_result(conn)
1788 }
1789}
1790
1791// Note: We could have a blanket `AsQuery` impl here, which would apply to
1792// everything we want it to. However, when a query is invalid, we specifically
1793// want the error to happen on the where clause of the method instead of trait
1794// resolution. Otherwise our users will get an error saying `<3 page long type>:
1795// ExecuteDsl is not satisfied` instead of a specific error telling them what
1796// part of their query is wrong.
1797impl<T, Conn> RunQueryDsl<Conn> for T where T: Table {}