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