drizzle_sqlite/builder/select.rs
1//! The SELECT builder: [`SelectBuilder`] and its states.
2//!
3//! Start a SELECT with [`QueryBuilder::select`](super::QueryBuilder::select).
4//! [`SelectBuilder`] documents the order in which clauses can be added.
5
6use crate::helpers::{self, JoinArg};
7use crate::values::SQLiteValue;
8use core::marker::PhantomData;
9use drizzle_core::{SQLTable, ToSQL};
10use paste::paste;
11
12//------------------------------------------------------------------------------
13// Type State Markers
14//------------------------------------------------------------------------------
15
16pub use drizzle_core::builder::{
17 SelectFromSet, SelectGroupSet, SelectInitial, SelectJoinSet, SelectLimitSet, SelectOffsetSet,
18 SelectOrderSet, SelectSetOpSet, SelectWhereSet,
19};
20
21/// Clause gate for SELECT methods whose names collide with INSERT/UPDATE/DELETE
22/// builder methods on the shared `QueryBuilder` type.
23///
24/// Coherence can only rule out overlapping inherent impls through a trait
25/// local to this crate, so these clauses use this trait instead of
26/// [`drizzle_core::ClauseAllowed`].
27#[doc(hidden)]
28#[diagnostic::on_unimplemented(
29 message = "builder state `{Self}` does not allow `{C}`",
30 label = "not available at this point of the query",
31 note = "SELECT clauses go in order: FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET",
32 note = "only a SELECT can be a set operand, a subquery, a derived table, or an INSERT source"
33)]
34pub trait SelectClause<C> {}
35
36impl SelectClause<drizzle_core::clause::Where> for SelectFromSet {}
37impl SelectClause<drizzle_core::clause::Where> for SelectJoinSet {}
38impl SelectClause<drizzle_core::clause::OrderBy> for SelectFromSet {}
39impl SelectClause<drizzle_core::clause::OrderBy> for SelectJoinSet {}
40impl SelectClause<drizzle_core::clause::OrderBy> for SelectWhereSet {}
41impl SelectClause<drizzle_core::clause::OrderBy> for SelectGroupSet {}
42// `SelectSetOpSet` takes no plain ORDER BY: a compound query orders by its
43// output columns, which the dedicated `order_by` on that state renders.
44
45/// Clause marker for `OFFSET` without a preceding `LIMIT`.
46#[doc(hidden)]
47#[derive(Debug, Clone, Copy, Default)]
48pub struct StandaloneOffset;
49
50impl drizzle_core::ClauseAllowed<StandaloneOffset> for SelectFromSet {}
51impl drizzle_core::ClauseAllowed<StandaloneOffset> for SelectSetOpSet {}
52
53//------------------------------------------------------------------------------
54// Join macro (generates all join variants)
55//------------------------------------------------------------------------------
56
57#[doc(hidden)]
58macro_rules! join_impl {
59 () => {
60 join_impl!(@natural natural, Join::new().natural(), drizzle_core::InnerJoin);
61 join_impl!(@natural natural_left, Join::new().natural().left(), drizzle_core::LeftJoin);
62 join_impl!(left, Join::new().left(), drizzle_core::LeftJoin);
63 join_impl!(left_outer, Join::new().left().outer(), drizzle_core::LeftJoin);
64 join_impl!(@natural natural_left_outer, Join::new().natural().left().outer(), drizzle_core::LeftJoin);
65 join_impl!(@natural natural_right, Join::new().natural().right(), drizzle_core::RightJoin);
66 join_impl!(right, Join::new().right(), drizzle_core::RightJoin);
67 join_impl!(right_outer, Join::new().right().outer(), drizzle_core::RightJoin);
68 join_impl!(@natural natural_right_outer, Join::new().natural().right().outer(), drizzle_core::RightJoin);
69 join_impl!(@natural natural_full, Join::new().natural().full(), drizzle_core::FullJoin);
70 join_impl!(full, Join::new().full(), drizzle_core::FullJoin);
71 join_impl!(full_outer, Join::new().full().outer(), drizzle_core::FullJoin);
72 join_impl!(@natural natural_full_outer, Join::new().natural().full().outer(), drizzle_core::FullJoin);
73 join_impl!(inner, Join::new().inner(), drizzle_core::InnerJoin);
74 };
75 (@natural $type:ident, $join_expr:expr, $kind:ty) => {
76 paste! {
77 #[doc = concat!("Adds a `", stringify!($type), "` join (`NATURAL`).")]
78 ///
79 /// A natural join matches the columns both sides share by name,
80 /// so it takes a table or derived table and no ON condition.
81 #[allow(clippy::type_complexity)]
82 pub fn [<$type _join>]<J: helpers::JoinSource<'a>>(
83 self,
84 source: J,
85 ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as drizzle_core::JoinStep<R, J::JoinedTable, $kind>>::Marker, <M as drizzle_core::JoinStep<R, J::JoinedTable, $kind>>::Row, G>
86 where
87 M: drizzle_core::JoinStep<R, J::JoinedTable, $kind>,
88 {
89 use drizzle_core::{Join, ToSQL};
90 SelectBuilder {
91 sql: self
92 .sql
93 .append($join_expr.to_sql())
94 .append(drizzle_core::SQL::raw(" "))
95 .append(source.into_join_source_sql()),
96 schema: PhantomData,
97 state: PhantomData,
98 table: PhantomData,
99 marker: PhantomData,
100 row: PhantomData,
101 grouped: PhantomData,
102 }
103 }
104 }
105 };
106 ($type:ident, $join_expr:expr, $kind:ty) => {
107 paste! {
108 #[doc = concat!("Adds a `", stringify!($type), "` join.")]
109 ///
110 /// Pass `(table, condition)` for an explicit ON condition, or a
111 /// bare table to join on its foreign key to the previous table.
112 /// See [`join`](Self::join) for an example.
113 #[allow(clippy::type_complexity)]
114 pub fn [<$type _join>]<J: JoinArg<'a, T>>(
115 self,
116 arg: J,
117 ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as drizzle_core::JoinStep<R, J::JoinedTable, $kind, J::OnSources>>::Marker, <M as drizzle_core::JoinStep<R, J::JoinedTable, $kind, J::OnSources>>::Row, G>
118 where
119 M: drizzle_core::JoinStep<R, J::JoinedTable, $kind, J::OnSources>,
120 {
121 use drizzle_core::Join;
122 SelectBuilder {
123 sql: self.sql.append(arg.into_join_sql($join_expr)),
124 schema: PhantomData,
125 state: PhantomData,
126 table: PhantomData,
127 marker: PhantomData,
128 row: PhantomData,
129 grouped: PhantomData,
130 }
131 }
132 }
133 };
134}
135
136//------------------------------------------------------------------------------
137// SelectBuilder Definition
138//------------------------------------------------------------------------------
139
140/// A SELECT query being built for `SQLite`.
141///
142/// This is [`QueryBuilder`](super::QueryBuilder) in one of the `Select*`
143/// states. Start it with [`QueryBuilder::select`](super::QueryBuilder::select)
144/// or [`select_distinct`](super::QueryBuilder::select_distinct), then call
145/// [`from`](Self::from).
146///
147/// # Clause order
148///
149/// Clauses must be added in SQL order. Each method is only available in the
150/// states listed here:
151///
152/// | After | You can call |
153/// |---|---|
154/// | `select` | `from` |
155/// | `from` | joins, `where`, `group_by`, `order_by`, `limit`, `offset`, set operations |
156/// | a join | more joins, `where`, `group_by`, `order_by`, `limit`, set operations |
157/// | `where` | `group_by`, `order_by`, `limit`, set operations |
158/// | `group_by` | `having`, `order_by`, `limit`, set operations |
159/// | `having` | `having`, `order_by`, `limit`, set operations |
160/// | `order_by` | `limit`, set operations |
161/// | `limit` | `offset`, set operations |
162/// | `offset` | set operations |
163/// | a set operation | more set operations, `order_by`, `limit`, `offset` |
164///
165/// `offset` without `limit` is only offered right after `from` or a set
166/// operation; elsewhere, add a `limit` first. Every state after `from` can
167/// be executed, used as a subquery, or named as a derived table with
168/// [`alias`](Self::alias). Every state except a compound query can become a
169/// CTE with [`into_cte`](Self::into_cte).
170///
171/// # Examples
172///
173/// ```rust
174/// # mod drizzle {
175/// # pub mod core { pub use drizzle_core::*; }
176/// # pub mod error { pub use drizzle_core::error::*; }
177/// # pub mod types { pub use drizzle_types::*; }
178/// # pub mod migrations { pub use drizzle_migrations::*; }
179/// # pub use drizzle_types::Dialect;
180/// # pub use drizzle_types as ddl;
181/// # pub mod sqlite {
182/// # pub use drizzle_sqlite::{*, attrs::*};
183/// # #[cfg(feature = "rusqlite")]
184/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
185/// # #[cfg(feature = "libsql")]
186/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
187/// # #[cfg(feature = "turso")]
188/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
189/// # pub mod prelude {
190/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
191/// # pub use drizzle_sqlite::{*, attrs::*};
192/// # pub use drizzle_core::*;
193/// # }
194/// # }
195/// # }
196/// use drizzle::sqlite::prelude::*;
197/// use drizzle::sqlite::builder::QueryBuilder;
198///
199/// #[SQLiteTable(name = "users")]
200/// struct User {
201/// #[column(primary)]
202/// id: i32,
203/// name: String,
204/// email: Option<String>,
205/// }
206///
207/// #[derive(SQLiteSchema)]
208/// struct Schema {
209/// user: User,
210/// }
211///
212/// let builder = QueryBuilder::new::<Schema>();
213/// let Schema { user } = Schema::new();
214///
215/// // Basic SELECT
216/// let query = builder.select(user.name).from(user);
217/// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
218///
219/// // SELECT with WHERE clause
220/// use drizzle::core::expr::gt;
221/// let query = builder
222/// .select((user.id, user.name))
223/// .from(user)
224/// .r#where(gt(user.id, 10));
225/// assert_eq!(
226/// query.to_sql().sql(),
227/// r#"SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?"#
228/// );
229/// ```
230///
231/// Joins:
232///
233/// ```rust
234/// # mod drizzle {
235/// # pub mod core { pub use drizzle_core::*; }
236/// # pub mod error { pub use drizzle_core::error::*; }
237/// # pub mod types { pub use drizzle_types::*; }
238/// # pub mod migrations { pub use drizzle_migrations::*; }
239/// # pub use drizzle_types::Dialect;
240/// # pub use drizzle_types as ddl;
241/// # pub mod sqlite {
242/// # pub use drizzle_sqlite::{*, attrs::*};
243/// # #[cfg(feature = "rusqlite")]
244/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
245/// # #[cfg(feature = "libsql")]
246/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
247/// # #[cfg(feature = "turso")]
248/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
249/// # pub mod prelude {
250/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
251/// # pub use drizzle_sqlite::{*, attrs::*};
252/// # pub use drizzle_core::*;
253/// # }
254/// # }
255/// # }
256/// # use drizzle::sqlite::prelude::*;
257/// # use drizzle::core::expr::eq;
258/// # use drizzle::sqlite::builder::QueryBuilder;
259/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
260/// # #[SQLiteTable(name = "posts")] struct Post { #[column(primary)] id: i32, user_id: i32, title: String }
261/// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
262/// # let builder = QueryBuilder::new::<Schema>();
263/// # let Schema { user, post } = Schema::new();
264/// let query = builder
265/// .select((user.name, post.title))
266/// .from(user)
267/// .join((post, eq(user.id, post.user_id)));
268/// assert_eq!(
269/// query.to_sql().sql(),
270/// r#"SELECT "users"."name", "posts"."title" FROM "users" JOIN "posts" ON "users"."id" = "posts"."user_id""#
271/// );
272/// ```
273///
274/// Ordering and pagination:
275///
276/// ```rust
277/// # mod drizzle {
278/// # pub mod core { pub use drizzle_core::*; }
279/// # pub mod error { pub use drizzle_core::error::*; }
280/// # pub mod types { pub use drizzle_types::*; }
281/// # pub mod migrations { pub use drizzle_migrations::*; }
282/// # pub use drizzle_types::Dialect;
283/// # pub use drizzle_types as ddl;
284/// # pub mod sqlite {
285/// # pub use drizzle_sqlite::{*, attrs::*};
286/// # #[cfg(feature = "rusqlite")]
287/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
288/// # #[cfg(feature = "libsql")]
289/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
290/// # #[cfg(feature = "turso")]
291/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
292/// # pub mod prelude {
293/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
294/// # pub use drizzle_sqlite::{*, attrs::*};
295/// # pub use drizzle_core::*;
296/// # }
297/// # }
298/// # }
299/// # use drizzle::sqlite::prelude::*;
300/// # use drizzle::sqlite::builder::QueryBuilder;
301/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
302/// # #[derive(SQLiteSchema)] struct Schema { user: User }
303/// # let builder = QueryBuilder::new::<Schema>();
304/// # let Schema { user } = Schema::new();
305/// let query = builder
306/// .select(user.name)
307/// .from(user)
308/// .order_by(asc(user.name))
309/// .limit(10)
310/// .offset(20);
311/// assert_eq!(
312/// query.to_sql().sql(),
313/// r#"SELECT "users"."name" FROM "users" ORDER BY "users"."name" ASC LIMIT 10 OFFSET 20"#
314/// );
315/// ```
316///
317/// # Compile-time checks
318///
319/// A clause added out of order does not compile:
320///
321/// ```rust,compile_fail
322/// # mod drizzle {
323/// # pub mod core { pub use drizzle_core::*; }
324/// # pub mod error { pub use drizzle_core::error::*; }
325/// # pub mod types { pub use drizzle_types::*; }
326/// # pub mod migrations { pub use drizzle_migrations::*; }
327/// # pub use drizzle_types::Dialect;
328/// # pub use drizzle_types as ddl;
329/// # pub mod sqlite {
330/// # pub use drizzle_sqlite::*;
331/// # #[cfg(feature = "rusqlite")]
332/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
333/// # #[cfg(feature = "libsql")]
334/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
335/// # #[cfg(feature = "turso")]
336/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
337/// # pub mod prelude {
338/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
339/// # pub use drizzle_sqlite::{*, attrs::*};
340/// # pub use drizzle_core::*;
341/// # }
342/// # }
343/// # }
344/// # use drizzle::sqlite::prelude::*;
345/// # use drizzle::core::expr::gt;
346/// # use drizzle::sqlite::builder::QueryBuilder;
347/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
348/// # #[derive(SQLiteSchema)] struct Schema { user: User }
349/// # let builder = QueryBuilder::new::<Schema>();
350/// # let Schema { user } = Schema::new();
351/// // WHERE cannot follow LIMIT.
352/// let query = builder.select(user.name).from(user).limit(10).r#where(gt(user.id, 1));
353/// ```
354///
355/// `having` needs a `group_by` first, and a WHERE or HAVING condition must
356/// be a boolean expression. Column references are scope-checked: a query
357/// that names a table missing from its FROM and JOIN clauses is rejected when
358/// it is executed (`.all()`, `.get()`, ...), used as a derived table, or used
359/// as an INSERT source.
360pub type SelectBuilder<'a, Schema, State, Table = (), Marker = (), Row = (), Grouped = ()> =
361 super::QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>;
362
363//------------------------------------------------------------------------------
364// Initial State: .from()
365//------------------------------------------------------------------------------
366
367impl<'a, S, M> SelectBuilder<'a, S, SelectInitial, (), M> {
368 /// Sets the FROM source: a table, a CTE, or a derived table made with
369 /// [`alias`](Self::alias).
370 ///
371 /// The result row type is inferred from the selected columns and this
372 /// source. With `select(())`, the row is the table's generated select
373 /// model.
374 ///
375 /// # Examples
376 ///
377 /// ```rust
378 /// # mod drizzle {
379 /// # pub mod core { pub use drizzle_core::*; }
380 /// # pub mod error { pub use drizzle_core::error::*; }
381 /// # pub mod types { pub use drizzle_types::*; }
382 /// # pub mod migrations { pub use drizzle_migrations::*; }
383 /// # pub use drizzle_types::Dialect;
384 /// # pub use drizzle_types as ddl;
385 /// # pub mod sqlite {
386 /// # pub use drizzle_sqlite::{*, attrs::*};
387 /// # #[cfg(feature = "rusqlite")]
388 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
389 /// # #[cfg(feature = "libsql")]
390 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
391 /// # #[cfg(feature = "turso")]
392 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
393 /// # pub mod prelude {
394 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
395 /// # pub use drizzle_sqlite::{*, attrs::*};
396 /// # pub use drizzle_core::*;
397 /// # }
398 /// # }
399 /// # }
400 /// # use drizzle::sqlite::prelude::*;
401 /// # use drizzle::sqlite::builder::QueryBuilder;
402 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
403 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
404 /// # let builder = QueryBuilder::new::<Schema>();
405 /// # let Schema { user } = Schema::new();
406 /// // Select from a table
407 /// let query = builder.select(user.name).from(user);
408 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
409 /// ```
410 #[inline]
411 #[allow(clippy::type_complexity)]
412 pub fn from<T>(
413 self,
414 query: T,
415 ) -> SelectBuilder<
416 'a,
417 S,
418 SelectFromSet,
419 T,
420 drizzle_core::FromMarker<M, T>,
421 <M as drizzle_core::ResolveRow<T>>::Row,
422 >
423 where
424 T: ToSQL<'a, SQLiteValue<'a>> + drizzle_core::ScopeEntry,
425 M: drizzle_core::ResolveRow<T>,
426 {
427 let sql = self.sql.append(helpers::from(query));
428 SelectBuilder {
429 sql,
430 schema: PhantomData,
431 state: PhantomData,
432 table: PhantomData,
433 marker: PhantomData,
434 row: PhantomData,
435 grouped: PhantomData,
436 }
437 }
438}
439
440//------------------------------------------------------------------------------
441// Capability-gated methods (generic over State)
442//------------------------------------------------------------------------------
443
444// JOIN (available from SelectFromSet and SelectJoinSet)
445impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
446where
447 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Join>,
448{
449 /// Adds a `JOIN` (an inner join).
450 ///
451 /// Pass `(table, condition)` for an explicit ON condition, or a bare table
452 /// to join on its foreign key to the previous table. A derived table
453 /// (see [`alias`](Self::alias)) also works in the tuple form.
454 ///
455 /// The other join methods (`left_join`, `right_join`, `full_join`,
456 /// `inner_join`, the `_outer` and `natural_` variants, and
457 /// [`cross_join`](Self::cross_join)) take the same arguments. After a
458 /// LEFT, RIGHT or FULL join, selected columns of the side that may be
459 /// missing decode as `Option`; with `select(())`, that side's whole
460 /// model is an `Option` in the row.
461 ///
462 /// The condition may only read tables already in the query; this is
463 /// checked when the query is executed.
464 ///
465 /// # Examples
466 ///
467 /// ```rust
468 /// # mod drizzle {
469 /// # pub mod core { pub use drizzle_core::*; }
470 /// # pub mod error { pub use drizzle_core::error::*; }
471 /// # pub mod types { pub use drizzle_types::*; }
472 /// # pub mod migrations { pub use drizzle_migrations::*; }
473 /// # pub use drizzle_types::Dialect;
474 /// # pub use drizzle_types as ddl;
475 /// # pub mod sqlite {
476 /// # pub use drizzle_sqlite::{*, attrs::*};
477 /// # #[cfg(feature = "rusqlite")]
478 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
479 /// # #[cfg(feature = "libsql")]
480 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
481 /// # #[cfg(feature = "turso")]
482 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
483 /// # pub mod prelude {
484 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
485 /// # pub use drizzle_sqlite::{*, attrs::*};
486 /// # pub use drizzle_core::*;
487 /// # }
488 /// # }
489 /// # }
490 /// # use drizzle::sqlite::prelude::*;
491 /// # use drizzle::core::expr::eq;
492 /// # use drizzle::sqlite::builder::QueryBuilder;
493 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
494 /// # #[SQLiteTable(name = "posts")] struct Post { #[column(primary)] id: i32, user_id: i32, title: String }
495 /// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
496 /// # let builder = QueryBuilder::new::<Schema>();
497 /// # let Schema { user, post } = Schema::new();
498 /// let query = builder
499 /// .select((user.name, post.title))
500 /// .from(user)
501 /// .join((post, eq(user.id, post.user_id)));
502 /// assert_eq!(
503 /// query.to_sql().sql(),
504 /// r#"SELECT "users"."name", "posts"."title" FROM "users" JOIN "posts" ON "users"."id" = "posts"."user_id""#
505 /// );
506 /// ```
507 #[inline]
508 #[allow(clippy::type_complexity)]
509 pub fn join<J: JoinArg<'a, T>>(
510 self,
511 arg: J,
512 ) -> SelectBuilder<
513 'a,
514 S,
515 SelectJoinSet,
516 J::JoinedTable,
517 <M as drizzle_core::JoinStep<R, J::JoinedTable, drizzle_core::InnerJoin, J::OnSources>>::Marker,
518 <M as drizzle_core::JoinStep<R, J::JoinedTable, drizzle_core::InnerJoin, J::OnSources>>::Row,
519 G,
520 >
521 where
522 M: drizzle_core::JoinStep<R, J::JoinedTable, drizzle_core::InnerJoin, J::OnSources>,
523{
524 SelectBuilder {
525 sql: self
526 .sql
527 .append(arg.into_join_sql(drizzle_core::Join::new())),
528 schema: PhantomData,
529 state: PhantomData,
530 table: PhantomData,
531 marker: PhantomData,
532 row: PhantomData,
533 grouped: PhantomData,
534 }
535 }
536
537 join_impl!();
538
539 /// Adds a `CROSS JOIN`, which pairs every row with every row of `arg`.
540 ///
541 /// A bare table renders `CROSS JOIN`. For backwards compatibility,
542 /// `(table, condition)` renders the equivalent `INNER JOIN ... ON ...`.
543 #[allow(clippy::type_complexity)]
544 pub fn cross_join<Arg: helpers::CrossJoinArg<'a, T>>(
545 self,
546 arg: Arg,
547 ) -> SelectBuilder<
548 'a,
549 S,
550 SelectJoinSet,
551 Arg::JoinedTable,
552 <M as drizzle_core::JoinStep<
553 R,
554 Arg::JoinedTable,
555 drizzle_core::InnerJoin,
556 Arg::OnSources,
557 >>::Marker,
558 <M as drizzle_core::JoinStep<
559 R,
560 Arg::JoinedTable,
561 drizzle_core::InnerJoin,
562 Arg::OnSources,
563 >>::Row,
564 G,
565 >
566 where
567 M: drizzle_core::JoinStep<R, Arg::JoinedTable, drizzle_core::InnerJoin, Arg::OnSources>,
568 {
569 SelectBuilder {
570 sql: self.sql.append(arg.into_cross_join_sql()),
571 schema: PhantomData,
572 state: PhantomData,
573 table: PhantomData,
574 marker: PhantomData,
575 row: PhantomData,
576 grouped: PhantomData,
577 }
578 }
579}
580
581// WHERE (available from SelectFromSet and SelectJoinSet)
582impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
583where
584 State: SelectClause<drizzle_core::clause::Where>,
585{
586 /// Adds a WHERE clause.
587 ///
588 /// The condition must be a boolean expression. Combine conditions with
589 /// `and` and `or` from `drizzle_core::expr`.
590 ///
591 /// # Examples
592 ///
593 /// ```rust
594 /// # mod drizzle {
595 /// # pub mod core { pub use drizzle_core::*; }
596 /// # pub mod error { pub use drizzle_core::error::*; }
597 /// # pub mod types { pub use drizzle_types::*; }
598 /// # pub mod migrations { pub use drizzle_migrations::*; }
599 /// # pub use drizzle_types::Dialect;
600 /// # pub use drizzle_types as ddl;
601 /// # pub mod sqlite {
602 /// # pub use drizzle_sqlite::{*, attrs::*};
603 /// # #[cfg(feature = "rusqlite")]
604 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
605 /// # #[cfg(feature = "libsql")]
606 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
607 /// # #[cfg(feature = "turso")]
608 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
609 /// # pub mod prelude {
610 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
611 /// # pub use drizzle_sqlite::{*, attrs::*};
612 /// # pub use drizzle_core::*;
613 /// # }
614 /// # }
615 /// # }
616 /// # use drizzle::sqlite::prelude::*;
617 /// # use drizzle::core::expr::{gt, and, eq};
618 /// # use drizzle::sqlite::builder::QueryBuilder;
619 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String, age: Option<i32> }
620 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
621 /// # let builder = QueryBuilder::new::<Schema>();
622 /// # let Schema { user } = Schema::new();
623 /// // Single condition
624 /// let query = builder
625 /// .select(user.name)
626 /// .from(user)
627 /// .r#where(gt(user.id, 10));
628 /// assert_eq!(
629 /// query.to_sql().sql(),
630 /// r#"SELECT "users"."name" FROM "users" WHERE "users"."id" > ?"#
631 /// );
632 ///
633 /// // Multiple conditions
634 /// let query = builder
635 /// .select(user.name)
636 /// .from(user)
637 /// .r#where(and(gt(user.id, 10), eq(user.name, "Alice")));
638 /// assert_eq!(
639 /// query.to_sql().sql(),
640 /// r#"SELECT "users"."name" FROM "users" WHERE ("users"."id" > ? AND "users"."name" = ?)"#
641 /// );
642 /// ```
643 #[inline]
644 #[allow(clippy::type_complexity)]
645 pub fn r#where<E>(
646 self,
647 condition: E,
648 ) -> SelectBuilder<
649 'a,
650 S,
651 SelectWhereSet,
652 T,
653 <M as drizzle_core::HasScope>::With<E::Sources>,
654 R,
655 G,
656 >
657 where
658 M: drizzle_core::HasScope,
659 E: drizzle_core::expr::Expr<'a, SQLiteValue<'a>>,
660 E::SQLType: drizzle_core::types::BooleanLike,
661 {
662 SelectBuilder {
663 sql: self.sql.append(helpers::r#where(condition)),
664 schema: PhantomData,
665 state: PhantomData,
666 table: PhantomData,
667 marker: PhantomData,
668 row: PhantomData,
669 grouped: PhantomData,
670 }
671 }
672}
673
674// GROUP BY (available from SelectFromSet, SelectJoinSet, SelectWhereSet)
675impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
676where
677 State: drizzle_core::ClauseAllowed<drizzle_core::clause::GroupBy>,
678{
679 /// Adds a GROUP BY clause. Pass one expression or a tuple.
680 ///
681 /// Every selected column that is not inside an aggregate must appear in
682 /// the GROUP BY list; this is checked when the query is executed. One
683 /// exception: grouping by a table's single-column primary key determines
684 /// the whole row, so any column of that table may be selected. Prefer
685 /// `.group_by(table.pk)` over listing every selected column; it also
686 /// lets `SQLite` read groups in key order instead of sorting them in a
687 /// temporary B-tree.
688 ///
689 /// # Examples
690 ///
691 /// ```rust
692 /// # mod drizzle {
693 /// # pub mod core { pub use drizzle_core::*; }
694 /// # pub mod error { pub use drizzle_core::error::*; }
695 /// # pub mod types { pub use drizzle_types::*; }
696 /// # pub mod migrations { pub use drizzle_migrations::*; }
697 /// # pub use drizzle_types::Dialect;
698 /// # pub use drizzle_types as ddl;
699 /// # pub mod sqlite {
700 /// # pub use drizzle_sqlite::*;
701 /// # #[cfg(feature = "rusqlite")]
702 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
703 /// # #[cfg(feature = "libsql")]
704 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
705 /// # #[cfg(feature = "turso")]
706 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
707 /// # pub mod prelude {
708 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
709 /// # pub use drizzle_sqlite::{*, attrs::*};
710 /// # pub use drizzle_core::*;
711 /// # }
712 /// # }
713 /// # }
714 /// # use drizzle::sqlite::prelude::*;
715 /// # use drizzle::core::expr::{count, gt};
716 /// # use drizzle::sqlite::builder::QueryBuilder;
717 /// # #[SQLiteTable(name = "posts")] struct Post { #[column(primary)] id: i32, user_id: i32, title: String }
718 /// # #[derive(SQLiteSchema)] struct Schema { post: Post }
719 /// # let builder = QueryBuilder::new::<Schema>();
720 /// # let Schema { post } = Schema::new();
721 /// let query = builder
722 /// .select((post.user_id, count(post.id)))
723 /// .from(post)
724 /// .group_by(post.user_id)
725 /// .having(gt(count(post.id), 5));
726 /// assert_eq!(
727 /// query.to_sql().sql(),
728 /// r#"SELECT "posts"."user_id", COUNT ("posts"."id") FROM "posts" GROUP BY "posts"."user_id" HAVING COUNT ("posts"."id")> ?"#
729 /// );
730 /// ```
731 #[allow(clippy::type_complexity)]
732 pub fn group_by<Gr>(
733 self,
734 columns: Gr,
735 ) -> SelectBuilder<
736 'a,
737 S,
738 SelectGroupSet,
739 T,
740 <M as drizzle_core::HasScope>::With<Gr::Sources>,
741 R,
742 Gr::Columns,
743 >
744 where
745 M: drizzle_core::HasScope,
746 Gr: drizzle_core::IntoGroupBy<'a, SQLiteValue<'a>>,
747 {
748 SelectBuilder {
749 sql: self.sql.append(helpers::group_by_expr(columns)),
750 schema: PhantomData,
751 state: PhantomData,
752 table: PhantomData,
753 marker: PhantomData,
754 row: PhantomData,
755 grouped: PhantomData,
756 }
757 }
758}
759
760// HAVING (available only from SelectGroupSet)
761impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
762where
763 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Having>,
764{
765 /// Adds a HAVING clause, which filters groups.
766 ///
767 /// Only available after [`group_by`](Self::group_by). The condition must
768 /// be a boolean expression and may use aggregates. See `group_by` for an
769 /// example.
770 #[allow(clippy::type_complexity)]
771 pub fn having<E>(
772 self,
773 condition: E,
774 ) -> SelectBuilder<
775 'a,
776 S,
777 SelectGroupSet,
778 T,
779 <M as drizzle_core::HasScope>::With<E::Sources>,
780 R,
781 G,
782 >
783 where
784 M: drizzle_core::HasScope,
785 E: drizzle_core::expr::Expr<'a, SQLiteValue<'a>>,
786 E::SQLType: drizzle_core::types::BooleanLike,
787 {
788 SelectBuilder {
789 sql: self.sql.append(helpers::having(condition)),
790 schema: PhantomData,
791 state: PhantomData,
792 table: PhantomData,
793 marker: PhantomData,
794 row: PhantomData,
795 grouped: PhantomData,
796 }
797 }
798}
799
800// ORDER BY (available from many states)
801impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
802where
803 State: SelectClause<drizzle_core::clause::OrderBy>,
804{
805 /// Adds an ORDER BY clause.
806 ///
807 /// Pass one ordering term or a tuple. Wrap a column in `asc` or `desc`
808 /// to set the direction.
809 ///
810 /// # Examples
811 ///
812 /// ```rust
813 /// # mod drizzle {
814 /// # pub mod core { pub use drizzle_core::*; }
815 /// # pub mod error { pub use drizzle_core::error::*; }
816 /// # pub mod types { pub use drizzle_types::*; }
817 /// # pub mod migrations { pub use drizzle_migrations::*; }
818 /// # pub use drizzle_types::Dialect;
819 /// # pub use drizzle_types as ddl;
820 /// # pub mod sqlite {
821 /// # pub use drizzle_sqlite::*;
822 /// # #[cfg(feature = "rusqlite")]
823 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
824 /// # #[cfg(feature = "libsql")]
825 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
826 /// # #[cfg(feature = "turso")]
827 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
828 /// # pub mod prelude {
829 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
830 /// # pub use drizzle_sqlite::{*, attrs::*};
831 /// # pub use drizzle_core::*;
832 /// # }
833 /// # }
834 /// # }
835 /// # use drizzle::sqlite::prelude::*;
836 /// # use drizzle::sqlite::builder::QueryBuilder;
837 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
838 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
839 /// # let builder = QueryBuilder::new::<Schema>();
840 /// # let Schema { user } = Schema::new();
841 /// let query = builder
842 /// .select(user.name)
843 /// .from(user)
844 /// .order_by((desc(user.name), asc(user.id)));
845 /// assert_eq!(
846 /// query.to_sql().sql(),
847 /// r#"SELECT "users"."name" FROM "users" ORDER BY "users"."name" DESC, "users"."id" ASC"#
848 /// );
849 /// ```
850 #[inline]
851 pub fn order_by<TOrderBy>(
852 self,
853 expressions: TOrderBy,
854 ) -> SelectBuilder<
855 'a,
856 S,
857 SelectOrderSet,
858 T,
859 <M as drizzle_core::HasScope>::With<TOrderBy::Sources>,
860 R,
861 G,
862 >
863 where
864 M: drizzle_core::HasScope,
865 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>> + drizzle_core::expr::ExprSources,
866 {
867 SelectBuilder {
868 sql: self.sql.append(helpers::order_by(expressions)),
869 schema: PhantomData,
870 state: PhantomData,
871 table: PhantomData,
872 marker: PhantomData,
873 row: PhantomData,
874 grouped: PhantomData,
875 }
876 }
877}
878
879// ORDER BY on a compound query: the combined rows carry no table scope, so the
880// ordering terms are rendered as output column names.
881impl<'a, S, T, M, R, G> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
882 /// Sorts a compound (`UNION` / `INTERSECT` / `EXCEPT`) result by its
883 /// output columns.
884 ///
885 /// Column references are written without their table name, because the
886 /// combined rows no longer belong to one table (turso rejects the
887 /// qualified form). See [`union`](Self::union) for an example.
888 #[inline]
889 pub fn order_by<TOrderBy>(
890 self,
891 expressions: TOrderBy,
892 ) -> SelectBuilder<'a, S, SelectOrderSet, T, M, R, G>
893 where
894 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
895 {
896 SelectBuilder {
897 sql: self
898 .sql
899 .append(drizzle_core::helpers::set_order_by(expressions)),
900 schema: PhantomData,
901 state: PhantomData,
902 table: PhantomData,
903 marker: PhantomData,
904 row: PhantomData,
905 grouped: PhantomData,
906 }
907 }
908}
909
910// LIMIT (available from many states)
911impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
912where
913 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Limit>,
914{
915 /// Adds a LIMIT clause.
916 ///
917 /// Pass a non-negative integer, which is written into the SQL, or an
918 /// integer placeholder, which is bound when the query runs. See
919 /// [`SelectBuilder`] for an example.
920 ///
921 /// # Panics
922 ///
923 /// Panics when a signed numeric argument is negative or a numeric value
924 /// does not fit in `usize`.
925 #[inline]
926 #[must_use]
927 #[track_caller]
928 pub fn limit<P>(self, limit: P) -> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G>
929 where
930 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
931 {
932 SelectBuilder {
933 sql: self.sql.append(helpers::limit(limit)),
934 schema: PhantomData,
935 state: PhantomData,
936 table: PhantomData,
937 marker: PhantomData,
938 row: PhantomData,
939 grouped: PhantomData,
940 }
941 }
942}
943
944// OFFSET without LIMIT (available from SelectFromSet and SelectSetOpSet)
945impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
946where
947 State: drizzle_core::ClauseAllowed<StandaloneOffset>,
948{
949 /// Skips the first `offset` rows without limiting the row count.
950 ///
951 /// `SQLite` only accepts `OFFSET` after a `LIMIT`, so this renders
952 /// `LIMIT -1 OFFSET n`; a negative limit means no limit. Only available
953 /// right after `from` or a set operation; elsewhere call
954 /// [`limit`](Self::limit) first.
955 ///
956 /// # Examples
957 ///
958 /// ```rust
959 /// # mod drizzle {
960 /// # pub mod core { pub use drizzle_core::*; }
961 /// # pub mod error { pub use drizzle_core::error::*; }
962 /// # pub mod types { pub use drizzle_types::*; }
963 /// # pub mod migrations { pub use drizzle_migrations::*; }
964 /// # pub use drizzle_types::Dialect;
965 /// # pub use drizzle_types as ddl;
966 /// # pub mod sqlite {
967 /// # pub use drizzle_sqlite::*;
968 /// # #[cfg(feature = "rusqlite")]
969 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
970 /// # #[cfg(feature = "libsql")]
971 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
972 /// # #[cfg(feature = "turso")]
973 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
974 /// # pub mod prelude {
975 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
976 /// # pub use drizzle_sqlite::{*, attrs::*};
977 /// # pub use drizzle_core::*;
978 /// # }
979 /// # }
980 /// # }
981 /// # use drizzle::sqlite::prelude::*;
982 /// # use drizzle::sqlite::builder::QueryBuilder;
983 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
984 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
985 /// # let builder = QueryBuilder::new::<Schema>();
986 /// # let Schema { user } = Schema::new();
987 /// let query = builder.select(user.name).from(user).offset(5);
988 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users" LIMIT -1 OFFSET 5"#);
989 /// ```
990 ///
991 /// # Panics
992 ///
993 /// Panics when a signed numeric argument is negative or a numeric value
994 /// does not fit in `usize`.
995 #[inline]
996 #[must_use]
997 #[track_caller]
998 pub fn offset<P>(self, offset: P) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>
999 where
1000 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
1001 {
1002 SelectBuilder {
1003 sql: self.sql.append(helpers::standalone_offset(offset)),
1004 schema: PhantomData,
1005 state: PhantomData,
1006 table: PhantomData,
1007 marker: PhantomData,
1008 row: PhantomData,
1009 grouped: PhantomData,
1010 }
1011 }
1012}
1013
1014// OFFSET after LIMIT
1015impl<'a, S, T, M, R, G> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G> {
1016 /// Adds an OFFSET clause after LIMIT.
1017 ///
1018 /// # Panics
1019 ///
1020 /// Panics when a signed numeric argument is negative or a numeric value
1021 /// does not fit in `usize`.
1022 #[inline]
1023 #[must_use]
1024 #[track_caller]
1025 pub fn offset<P>(self, offset: P) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>
1026 where
1027 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
1028 {
1029 SelectBuilder {
1030 sql: self.sql.append(helpers::offset(offset)),
1031 schema: PhantomData,
1032 state: PhantomData,
1033 table: PhantomData,
1034 marker: PhantomData,
1035 row: PhantomData,
1036 grouped: PhantomData,
1037 }
1038 }
1039}
1040
1041//------------------------------------------------------------------------------
1042// CTE support
1043//------------------------------------------------------------------------------
1044
1045impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
1046where
1047 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>,
1048 M: drizzle_core::DerivedSelection<'a, SQLiteValue<'a>, crate::common::SQLiteSchemaType, T>,
1049{
1050 /// Names this query so it can be used as a derived table in `from` or a
1051 /// join.
1052 ///
1053 /// `name` is a value of a [`Tag`](drizzle_core::Tag) type; its `NAME`
1054 /// becomes the SQL alias. The result exposes the selected columns, so
1055 /// the outer query can reference them with typed accessors.
1056 ///
1057 /// # Panics
1058 ///
1059 /// Panics when the projection contains duplicate output names. Name a
1060 /// computed expression with [`drizzle_core::expr::AliasExt::named`] to
1061 /// make each output unique.
1062 #[inline]
1063 #[must_use]
1064 pub fn alias<Name, AggProof>(
1065 self,
1066 _name: Name,
1067 ) -> drizzle_core::Derived<
1068 'a,
1069 SQLiteValue<'a>,
1070 Name,
1071 <M as drizzle_core::DerivedSelection<
1072 'a,
1073 SQLiteValue<'a>,
1074 crate::common::SQLiteSchemaType,
1075 T,
1076 >>::Projection,
1077 Self,
1078 >
1079 where
1080 Name: drizzle_core::Tag,
1081 <M as drizzle_core::DerivedSelection<
1082 'a,
1083 SQLiteValue<'a>,
1084 crate::common::SQLiteSchemaType,
1085 T,
1086 >>::Projection: drizzle_core::DerivedProjection<Name>,
1087 M: drizzle_core::row::MarkerAggValidFor<G, AggProof>,
1088 {
1089 // SAFETY: The executable-state, aggregate, and projection bounds
1090 // above prove that this query matches the derived projection; its
1091 // scope travels in `Self`'s sources and is checked where it is used.
1092 unsafe { drizzle_core::Derived::new_unchecked(self) }
1093 }
1094}
1095
1096impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
1097where
1098 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Simple>,
1099 T: SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>,
1100{
1101 /// Turns this SELECT into a common table expression named `Tag::NAME`.
1102 ///
1103 /// The result derefs to an aliased copy of the FROM table, so you can
1104 /// select its columns with the usual field access. Pass it to
1105 /// [`QueryBuilder::with`](super::QueryBuilder::with) and then use it in
1106 /// `from`. Not available on a compound query (after a set operation).
1107 /// See [`QueryBuilder::with`](super::QueryBuilder::with) for an example.
1108 #[inline]
1109 #[must_use]
1110 pub fn into_cte<Tag: drizzle_core::Tag + 'static>(
1111 self,
1112 ) -> super::CTEView<
1113 'a,
1114 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::Aliased<Tag>,
1115 Self,
1116 > {
1117 let name = Tag::NAME;
1118 super::CTEView::new(
1119 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::alias::<Tag>(),
1120 name,
1121 self,
1122 )
1123 }
1124}
1125
1126//------------------------------------------------------------------------------
1127// Set operation support (UNION / INTERSECT / EXCEPT)
1128//------------------------------------------------------------------------------
1129
1130impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
1131where
1132 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Compound>,
1133{
1134 /// Combines this query with `other` using UNION, which drops duplicate
1135 /// rows.
1136 ///
1137 /// Both queries must select the same row type. After a set operation you
1138 /// can chain more set operations, then `order_by`, `limit` and `offset`
1139 /// for the combined result.
1140 ///
1141 /// # Examples
1142 ///
1143 /// ```rust
1144 /// # mod drizzle {
1145 /// # pub mod core { pub use drizzle_core::*; }
1146 /// # pub mod error { pub use drizzle_core::error::*; }
1147 /// # pub mod types { pub use drizzle_types::*; }
1148 /// # pub mod migrations { pub use drizzle_migrations::*; }
1149 /// # pub use drizzle_types::Dialect;
1150 /// # pub use drizzle_types as ddl;
1151 /// # pub mod sqlite {
1152 /// # pub use drizzle_sqlite::*;
1153 /// # #[cfg(feature = "rusqlite")]
1154 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
1155 /// # #[cfg(feature = "libsql")]
1156 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
1157 /// # #[cfg(feature = "turso")]
1158 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
1159 /// # pub mod prelude {
1160 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
1161 /// # pub use drizzle_sqlite::{*, attrs::*};
1162 /// # pub use drizzle_core::*;
1163 /// # }
1164 /// # }
1165 /// # }
1166 /// # use drizzle::sqlite::prelude::*;
1167 /// # use drizzle::core::expr::{eq, gt};
1168 /// # use drizzle::sqlite::builder::QueryBuilder;
1169 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
1170 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
1171 /// # let builder = QueryBuilder::new::<Schema>();
1172 /// # let Schema { user } = Schema::new();
1173 /// let query = builder
1174 /// .select(user.name)
1175 /// .from(user)
1176 /// .r#where(eq(user.id, 1))
1177 /// .union(builder.select(user.name).from(user).r#where(gt(user.id, 100)))
1178 /// .order_by(asc(user.name));
1179 /// assert_eq!(
1180 /// query.to_sql().sql(),
1181 /// r#"SELECT "users"."name" FROM "users" WHERE "users"."id" = ? UNION SELECT "users"."name" FROM "users" WHERE "users"."id" > ? ORDER BY "name" ASC"#
1182 /// );
1183 /// ```
1184 #[allow(clippy::type_complexity)]
1185 pub fn union<M2>(
1186 self,
1187 other: impl IntoSelect<'a, S, M2, R>,
1188 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as drizzle_core::SetOperand<M2>>::Combined, R, G>
1189 where
1190 M: drizzle_core::SetOperand<M2>,
1191 {
1192 SelectBuilder {
1193 sql: helpers::union(self.sql, other.into_select()),
1194 schema: PhantomData,
1195 state: PhantomData,
1196 table: PhantomData,
1197 marker: PhantomData,
1198 row: PhantomData,
1199 grouped: PhantomData,
1200 }
1201 }
1202
1203 /// Combines this query with `other` using UNION ALL, which keeps
1204 /// duplicate rows. See [`union`](Self::union).
1205 #[allow(clippy::type_complexity)]
1206 pub fn union_all<M2>(
1207 self,
1208 other: impl IntoSelect<'a, S, M2, R>,
1209 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as drizzle_core::SetOperand<M2>>::Combined, R, G>
1210 where
1211 M: drizzle_core::SetOperand<M2>,
1212 {
1213 SelectBuilder {
1214 sql: helpers::union_all(self.sql, other.into_select()),
1215 schema: PhantomData,
1216 state: PhantomData,
1217 table: PhantomData,
1218 marker: PhantomData,
1219 row: PhantomData,
1220 grouped: PhantomData,
1221 }
1222 }
1223
1224 /// Keeps only rows that `other` also returns (INTERSECT). See
1225 /// [`union`](Self::union).
1226 #[allow(clippy::type_complexity)]
1227 pub fn intersect<M2>(
1228 self,
1229 other: impl IntoSelect<'a, S, M2, R>,
1230 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as drizzle_core::SetOperand<M2>>::Combined, R, G>
1231 where
1232 M: drizzle_core::SetOperand<M2>,
1233 {
1234 SelectBuilder {
1235 sql: helpers::intersect(self.sql, other.into_select()),
1236 schema: PhantomData,
1237 state: PhantomData,
1238 table: PhantomData,
1239 marker: PhantomData,
1240 row: PhantomData,
1241 grouped: PhantomData,
1242 }
1243 }
1244
1245 /// Keeps only rows that `other` does not return (EXCEPT). See
1246 /// [`union`](Self::union).
1247 #[allow(clippy::type_complexity)]
1248 pub fn except<M2>(
1249 self,
1250 other: impl IntoSelect<'a, S, M2, R>,
1251 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as drizzle_core::SetOperand<M2>>::Combined, R, G>
1252 where
1253 M: drizzle_core::SetOperand<M2>,
1254 {
1255 SelectBuilder {
1256 sql: helpers::except(self.sql, other.into_select()),
1257 schema: PhantomData,
1258 state: PhantomData,
1259 table: PhantomData,
1260 marker: PhantomData,
1261 row: PhantomData,
1262 grouped: PhantomData,
1263 }
1264 }
1265}
1266
1267//------------------------------------------------------------------------------
1268// Expr impl for subquery usage
1269//------------------------------------------------------------------------------
1270
1271impl<'a, S, State, T, M, R, G> drizzle_core::expr::Expr<'a, SQLiteValue<'a>>
1272 for SelectBuilder<'a, S, State, T, M, R, G>
1273where
1274 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>,
1275 M: drizzle_core::expr::SubqueryType<'a, SQLiteValue<'a>> + drizzle_core::SelectSources,
1276{
1277 type SQLType = <M as drizzle_core::expr::SubqueryType<'a, SQLiteValue<'a>>>::SQLType;
1278 type Nullable = drizzle_core::expr::Null;
1279 type Aggregate = drizzle_core::expr::Scalar;
1280}
1281
1282impl<S, State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>, T, M, R, G>
1283 drizzle_core::expr::SelectQuery for SelectBuilder<'_, S, State, T, M, R, G>
1284{
1285}
1286
1287impl<S, State, T, M, R, G> drizzle_core::expr::ExprSources
1288 for SelectBuilder<'_, S, State, T, M, R, G>
1289where
1290 M: drizzle_core::SelectSources,
1291{
1292 type Sources = M::Sources;
1293}
1294
1295//------------------------------------------------------------------------------
1296// IntoSelect conversion trait
1297//------------------------------------------------------------------------------
1298
1299/// A query that can be the right-hand side of a set operation.
1300///
1301/// Implemented for completed [`SelectBuilder`]s and for the driver
1302/// builders in the `drizzle` crate that wrap one.
1303pub trait IntoSelect<'a, S, M, R> {
1304 /// Builder state of the converted query.
1305 type State: drizzle_core::ClauseAllowed<drizzle_core::clause::Compound>;
1306 /// FROM table of the converted query.
1307 type Table;
1308 /// Returns the underlying [`SelectBuilder`].
1309 fn into_select(self) -> SelectBuilder<'a, S, Self::State, Self::Table, M, R>;
1310}
1311
1312impl<'a, S, State: drizzle_core::ClauseAllowed<drizzle_core::clause::Compound>, T, M, R, G>
1313 IntoSelect<'a, S, M, R> for SelectBuilder<'a, S, State, T, M, R, G>
1314{
1315 type State = State;
1316 type Table = T;
1317 fn into_select(self) -> SelectBuilder<'a, S, State, T, M, R> {
1318 SelectBuilder {
1319 sql: self.sql,
1320 schema: PhantomData,
1321 state: PhantomData,
1322 table: PhantomData,
1323 marker: PhantomData,
1324 row: PhantomData,
1325 grouped: PhantomData,
1326 }
1327 }
1328}
1329
1330mod insert_select_private {
1331 pub trait Sealed {}
1332}
1333
1334/// A completed SELECT that can supply rows to an INSERT.
1335#[doc(hidden)]
1336pub trait CompletedSelect<'a, S, R>: insert_select_private::Sealed {
1337 type Marker;
1338 type Grouped;
1339
1340 fn into_select_sql(self) -> drizzle_core::SQL<'a, SQLiteValue<'a>>;
1341}
1342
1343/// Converts a completed SELECT or attached SELECT wrapper into its checked source.
1344#[doc(hidden)]
1345pub trait IntoSelectQuery<'a, S, R> {
1346 type Marker;
1347 type Grouped;
1348 type Select: CompletedSelect<'a, S, R, Marker = Self::Marker, Grouped = Self::Grouped>;
1349
1350 fn into_select_query(self) -> Self::Select;
1351}
1352
1353impl<'a, S, State, T, M, R, G> insert_select_private::Sealed
1354 for SelectBuilder<'a, S, State, T, M, R, G>
1355where
1356 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>,
1357{
1358}
1359
1360impl<'a, S, State, T, M, R, G> CompletedSelect<'a, S, R> for SelectBuilder<'a, S, State, T, M, R, G>
1361where
1362 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>,
1363{
1364 type Marker = M;
1365 type Grouped = G;
1366
1367 fn into_select_sql(self) -> drizzle_core::SQL<'a, SQLiteValue<'a>> {
1368 self.sql
1369 }
1370}
1371
1372impl<'a, S, State, T, M, R, G> IntoSelectQuery<'a, S, R> for SelectBuilder<'a, S, State, T, M, R, G>
1373where
1374 State: drizzle_core::ClauseAllowed<drizzle_core::clause::Source>,
1375{
1376 type Marker = M;
1377 type Grouped = G;
1378 type Select = Self;
1379
1380 fn into_select_query(self) -> Self::Select {
1381 self
1382 }
1383}