drizzle_sqlite/builder/select.rs
1use crate::helpers::{self, JoinArg};
2use crate::values::SQLiteValue;
3use core::marker::PhantomData;
4use drizzle_core::{SQLTable, ToSQL};
5use paste::paste;
6
7//------------------------------------------------------------------------------
8// Type State Markers
9//------------------------------------------------------------------------------
10
11pub use drizzle_core::builder::{
12 AsCteState, SelectFromSet, SelectGroupSet, SelectInitial, SelectJoinSet, SelectLimitSet,
13 SelectOffsetSet, SelectOrderSet, SelectSetOpSet, SelectWhereSet,
14};
15
16/// States that accept a plain `ORDER BY`.
17///
18/// `SelectSetOpSet` is excluded on purpose: a compound query orders by its
19/// output columns, which the dedicated `order_by` on that state renders.
20pub trait SelectOrderAllowed {}
21impl SelectOrderAllowed for SelectFromSet {}
22impl SelectOrderAllowed for SelectJoinSet {}
23impl SelectOrderAllowed for SelectWhereSet {}
24impl SelectOrderAllowed for SelectGroupSet {}
25
26#[doc(hidden)]
27pub trait SelectWhereAllowed: drizzle_core::WhereAllowed {}
28
29impl SelectWhereAllowed for SelectFromSet {}
30impl SelectWhereAllowed for SelectJoinSet {}
31
32//------------------------------------------------------------------------------
33// Join macro (generates all join variants)
34//------------------------------------------------------------------------------
35
36#[doc(hidden)]
37macro_rules! join_impl {
38 () => {
39 join_impl!(@natural natural, Join::new().natural(), drizzle_core::AfterJoin);
40 join_impl!(@natural natural_left, Join::new().natural().left(), drizzle_core::AfterLeftJoin);
41 join_impl!(left, Join::new().left(), drizzle_core::AfterLeftJoin);
42 join_impl!(left_outer, Join::new().left().outer(), drizzle_core::AfterLeftJoin);
43 join_impl!(@natural natural_left_outer, Join::new().natural().left().outer(), drizzle_core::AfterLeftJoin);
44 join_impl!(@natural natural_right, Join::new().natural().right(), drizzle_core::AfterRightJoin);
45 join_impl!(right, Join::new().right(), drizzle_core::AfterRightJoin);
46 join_impl!(right_outer, Join::new().right().outer(), drizzle_core::AfterRightJoin);
47 join_impl!(@natural natural_right_outer, Join::new().natural().right().outer(), drizzle_core::AfterRightJoin);
48 join_impl!(@natural natural_full, Join::new().natural().full(), drizzle_core::AfterFullJoin);
49 join_impl!(full, Join::new().full(), drizzle_core::AfterFullJoin);
50 join_impl!(full_outer, Join::new().full().outer(), drizzle_core::AfterFullJoin);
51 join_impl!(@natural natural_full_outer, Join::new().natural().full().outer(), drizzle_core::AfterFullJoin);
52 join_impl!(inner, Join::new().inner(), drizzle_core::AfterJoin);
53 };
54 (@natural $type:ident, $join_expr:expr, $join_trait:path) => {
55 paste! {
56 /// Adds a NATURAL join. The database matches the columns both
57 /// sides share by name, so it takes a source and no ON condition.
58 #[allow(clippy::type_complexity)]
59 pub fn [<$type _join>]<J: helpers::JoinSource<'a>>(
60 self,
61 source: J,
62 ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as drizzle_core::ScopePush<J::JoinedTable>>::Out, <M as $join_trait<R, J::JoinedTable>>::NewRow, G>
63 where
64 M: $join_trait<R, J::JoinedTable> + drizzle_core::ScopePush<J::JoinedTable>,
65 {
66 use drizzle_core::{Join, ToSQL};
67 SelectBuilder {
68 sql: self
69 .sql
70 .append($join_expr.to_sql())
71 .append(drizzle_core::SQL::raw(" "))
72 .append(source.into_join_source_sql()),
73 schema: PhantomData,
74 state: PhantomData,
75 table: PhantomData,
76 marker: PhantomData,
77 row: PhantomData,
78 grouped: PhantomData,
79 }
80 }
81 }
82 };
83 ($type:ident, $join_expr:expr, $join_trait:path) => {
84 paste! {
85 #[allow(clippy::type_complexity)]
86 pub fn [<$type _join>]<J: JoinArg<'a, T>>(
87 self,
88 arg: J,
89 ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as drizzle_core::ScopePush<J::JoinedTable>>::Out, <M as $join_trait<R, J::JoinedTable>>::NewRow, G>
90 where
91 M: $join_trait<R, J::JoinedTable> + drizzle_core::ScopePush<J::JoinedTable>,
92 {
93 use drizzle_core::Join;
94 SelectBuilder {
95 sql: self.sql.append(arg.into_join_sql($join_expr)),
96 schema: PhantomData,
97 state: PhantomData,
98 table: PhantomData,
99 marker: PhantomData,
100 row: PhantomData,
101 grouped: PhantomData,
102 }
103 }
104 }
105 };
106}
107
108//------------------------------------------------------------------------------
109// SelectBuilder Definition
110//------------------------------------------------------------------------------
111
112/// Builds a SELECT query specifically for `SQLite`.
113///
114/// `SelectBuilder` provides a type-safe, fluent API for constructing SELECT statements
115/// with compile-time verification of query structure and table relationships.
116///
117/// ## Type Parameters
118///
119/// - `Schema`: The database schema type, ensuring only valid tables can be referenced
120/// - `State`: The current builder state, enforcing proper query construction order
121/// - `Table`: The primary table being queried (when applicable)
122///
123/// ## Query Building Flow
124///
125/// 1. Start with `QueryBuilder::select()` to specify columns
126/// 2. Add `from()` to specify the source table
127/// 3. Optionally add joins, conditions, grouping, ordering, and limits
128///
129/// ## Basic Usage
130///
131/// ```rust
132/// # mod drizzle {
133/// # pub mod core { pub use drizzle_core::*; }
134/// # pub mod error { pub use drizzle_core::error::*; }
135/// # pub mod types { pub use drizzle_types::*; }
136/// # pub mod migrations { pub use drizzle_migrations::*; }
137/// # pub use drizzle_types::Dialect;
138/// # pub use drizzle_types as ddl;
139/// # pub mod sqlite {
140/// # pub use drizzle_sqlite::{*, attrs::*};
141/// # #[cfg(feature = "rusqlite")]
142/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
143/// # #[cfg(feature = "libsql")]
144/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
145/// # #[cfg(feature = "turso")]
146/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
147/// # pub mod prelude {
148/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
149/// # pub use drizzle_sqlite::{*, attrs::*};
150/// # pub use drizzle_core::*;
151/// # }
152/// # }
153/// # }
154/// use drizzle::sqlite::prelude::*;
155/// use drizzle::sqlite::builder::QueryBuilder;
156///
157/// #[SQLiteTable(name = "users")]
158/// struct User {
159/// #[column(primary)]
160/// id: i32,
161/// name: String,
162/// email: Option<String>,
163/// }
164///
165/// #[derive(SQLiteSchema)]
166/// struct Schema {
167/// user: User,
168/// }
169///
170/// let builder = QueryBuilder::new::<Schema>();
171/// let Schema { user } = Schema::new();
172///
173/// // Basic SELECT
174/// let query = builder.select(user.name).from(user);
175/// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
176///
177/// // SELECT with WHERE clause
178/// use drizzle::core::expr::gt;
179/// let query = builder
180/// .select((user.id, user.name))
181/// .from(user)
182/// .r#where(gt(user.id, 10));
183/// assert_eq!(
184/// query.to_sql().sql(),
185/// r#"SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?"#
186/// );
187/// ```
188///
189/// ## Advanced Queries
190///
191/// ```rust
192/// # mod drizzle {
193/// # pub mod core { pub use drizzle_core::*; }
194/// # pub mod error { pub use drizzle_core::error::*; }
195/// # pub mod types { pub use drizzle_types::*; }
196/// # pub mod migrations { pub use drizzle_migrations::*; }
197/// # pub use drizzle_types::Dialect;
198/// # pub use drizzle_types as ddl;
199/// # pub mod sqlite {
200/// # pub use drizzle_sqlite::{*, attrs::*};
201/// # #[cfg(feature = "rusqlite")]
202/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
203/// # #[cfg(feature = "libsql")]
204/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
205/// # #[cfg(feature = "turso")]
206/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
207/// # pub mod prelude {
208/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
209/// # pub use drizzle_sqlite::{*, attrs::*};
210/// # pub use drizzle_core::*;
211/// # }
212/// # }
213/// # }
214/// # use drizzle::sqlite::prelude::*;
215/// # use drizzle::core::expr::eq;
216/// # use drizzle::sqlite::builder::QueryBuilder;
217/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
218/// # #[SQLiteTable(name = "posts")] struct Post { #[column(primary)] id: i32, user_id: i32, title: String }
219/// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
220/// # let builder = QueryBuilder::new::<Schema>();
221/// # let Schema { user, post } = Schema::new();
222/// let query = builder
223/// .select((user.name, post.title))
224/// .from(user)
225/// .join((post, eq(user.id, post.user_id)));
226/// ```
227///
228/// ```rust
229/// # mod drizzle {
230/// # pub mod core { pub use drizzle_core::*; }
231/// # pub mod error { pub use drizzle_core::error::*; }
232/// # pub mod types { pub use drizzle_types::*; }
233/// # pub mod migrations { pub use drizzle_migrations::*; }
234/// # pub use drizzle_types::Dialect;
235/// # pub use drizzle_types as ddl;
236/// # pub mod sqlite {
237/// # pub use drizzle_sqlite::{*, attrs::*};
238/// # #[cfg(feature = "rusqlite")]
239/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
240/// # #[cfg(feature = "libsql")]
241/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
242/// # #[cfg(feature = "turso")]
243/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
244/// # pub mod prelude {
245/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
246/// # pub use drizzle_sqlite::{*, attrs::*};
247/// # pub use drizzle_core::*;
248/// # }
249/// # }
250/// # }
251/// # use drizzle::sqlite::prelude::*;
252/// # use drizzle::sqlite::builder::QueryBuilder;
253/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
254/// # #[derive(SQLiteSchema)] struct Schema { user: User }
255/// # let builder = QueryBuilder::new::<Schema>();
256/// # let Schema { user } = Schema::new();
257/// let query = builder
258/// .select(user.name)
259/// .from(user)
260/// .order_by(asc(user.name))
261/// .limit(10);
262/// ```
263pub type SelectBuilder<'a, Schema, State, Table = (), Marker = (), Row = (), Grouped = ()> =
264 super::QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>;
265
266//------------------------------------------------------------------------------
267// Initial State: .from()
268//------------------------------------------------------------------------------
269
270impl<'a, S, M> SelectBuilder<'a, S, SelectInitial, (), M> {
271 /// Specifies the table or subquery to select FROM.
272 ///
273 /// This method transitions the builder from the initial state to the FROM state,
274 /// enabling subsequent WHERE, JOIN, ORDER BY, and other clauses.
275 ///
276 /// The row type `R` is resolved from the select marker `M` and the table `T`
277 /// via the `ResolveRow` trait.
278 ///
279 /// # Examples
280 ///
281 /// ```rust
282 /// # mod drizzle {
283 /// # pub mod core { pub use drizzle_core::*; }
284 /// # pub mod error { pub use drizzle_core::error::*; }
285 /// # pub mod types { pub use drizzle_types::*; }
286 /// # pub mod migrations { pub use drizzle_migrations::*; }
287 /// # pub use drizzle_types::Dialect;
288 /// # pub use drizzle_types as ddl;
289 /// # pub mod sqlite {
290 /// # pub use drizzle_sqlite::{*, attrs::*};
291 /// # #[cfg(feature = "rusqlite")]
292 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
293 /// # #[cfg(feature = "libsql")]
294 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
295 /// # #[cfg(feature = "turso")]
296 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
297 /// # pub mod prelude {
298 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
299 /// # pub use drizzle_sqlite::{*, attrs::*};
300 /// # pub use drizzle_core::*;
301 /// # }
302 /// # }
303 /// # }
304 /// # use drizzle::sqlite::prelude::*;
305 /// # use drizzle::sqlite::builder::QueryBuilder;
306 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
307 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
308 /// # let builder = QueryBuilder::new::<Schema>();
309 /// # let Schema { user } = Schema::new();
310 /// // Select from a table
311 /// let query = builder.select(user.name).from(user);
312 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
313 /// ```
314 #[inline]
315 #[allow(clippy::type_complexity)]
316 pub fn from<T>(
317 self,
318 query: T,
319 ) -> SelectBuilder<
320 'a,
321 S,
322 SelectFromSet,
323 T,
324 drizzle_core::Scoped<M, drizzle_core::Cons<T, drizzle_core::Nil>>,
325 <M as drizzle_core::ResolveRow<T>>::Row,
326 >
327 where
328 T: ToSQL<'a, SQLiteValue<'a>>,
329 M: drizzle_core::ResolveRow<T>,
330 {
331 let sql = self.sql.append(helpers::from(query));
332 SelectBuilder {
333 sql,
334 schema: PhantomData,
335 state: PhantomData,
336 table: PhantomData,
337 marker: PhantomData,
338 row: PhantomData,
339 grouped: PhantomData,
340 }
341 }
342}
343
344//------------------------------------------------------------------------------
345// Capability-gated methods (generic over State)
346//------------------------------------------------------------------------------
347
348// JOIN (available from SelectFromSet and SelectJoinSet)
349impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
350where
351 State: drizzle_core::JoinAllowed,
352{
353 /// Adds an INNER JOIN clause to the query.
354 ///
355 /// Joins another table to the current query using the specified condition.
356 /// The joined table must be part of the schema and the condition should
357 /// relate columns from both tables.
358 ///
359 /// ```rust
360 /// # mod drizzle {
361 /// # pub mod core { pub use drizzle_core::*; }
362 /// # pub mod error { pub use drizzle_core::error::*; }
363 /// # pub mod types { pub use drizzle_types::*; }
364 /// # pub mod migrations { pub use drizzle_migrations::*; }
365 /// # pub use drizzle_types::Dialect;
366 /// # pub use drizzle_types as ddl;
367 /// # pub mod sqlite {
368 /// # pub use drizzle_sqlite::{*, attrs::*};
369 /// # #[cfg(feature = "rusqlite")]
370 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
371 /// # #[cfg(feature = "libsql")]
372 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
373 /// # #[cfg(feature = "turso")]
374 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
375 /// # pub mod prelude {
376 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
377 /// # pub use drizzle_sqlite::{*, attrs::*};
378 /// # pub use drizzle_core::*;
379 /// # }
380 /// # }
381 /// # }
382 /// # use drizzle::sqlite::prelude::*;
383 /// # use drizzle::core::expr::eq;
384 /// # use drizzle::sqlite::builder::QueryBuilder;
385 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
386 /// # #[SQLiteTable(name = "posts")] struct Post { #[column(primary)] id: i32, user_id: i32, title: String }
387 /// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
388 /// # let builder = QueryBuilder::new::<Schema>();
389 /// # let Schema { user, post } = Schema::new();
390 /// let query = builder
391 /// .select((user.name, post.title))
392 /// .from(user)
393 /// .join((post, eq(user.id, post.user_id)));
394 /// assert_eq!(
395 /// query.to_sql().sql(),
396 /// r#"SELECT "users"."name", "posts"."title" FROM "users" JOIN "posts" ON "users"."id" = "posts"."user_id""#
397 /// );
398 /// ```
399 #[inline]
400 #[allow(clippy::type_complexity)]
401 pub fn join<J: JoinArg<'a, T>>(
402 self,
403 arg: J,
404 ) -> SelectBuilder<
405 'a,
406 S,
407 SelectJoinSet,
408 J::JoinedTable,
409 <M as drizzle_core::ScopePush<J::JoinedTable>>::Out,
410 <M as drizzle_core::AfterJoin<R, J::JoinedTable>>::NewRow,
411 G,
412 >
413 where
414 M: drizzle_core::AfterJoin<R, J::JoinedTable> + drizzle_core::ScopePush<J::JoinedTable>,
415 {
416 SelectBuilder {
417 sql: self
418 .sql
419 .append(arg.into_join_sql(drizzle_core::Join::new())),
420 schema: PhantomData,
421 state: PhantomData,
422 table: PhantomData,
423 marker: PhantomData,
424 row: PhantomData,
425 grouped: PhantomData,
426 }
427 }
428
429 join_impl!();
430
431 /// Adds a cross join.
432 ///
433 /// A bare source renders `CROSS JOIN`. For backwards compatibility,
434 /// `(source, predicate)` renders the equivalent `INNER JOIN ... ON ...`.
435 #[allow(clippy::type_complexity)]
436 pub fn cross_join<Arg: helpers::CrossJoinArg<'a, T>>(
437 self,
438 arg: Arg,
439 ) -> SelectBuilder<
440 'a,
441 S,
442 SelectJoinSet,
443 Arg::JoinedTable,
444 <M as drizzle_core::ScopePush<Arg::JoinedTable>>::Out,
445 <M as drizzle_core::AfterJoin<R, Arg::JoinedTable>>::NewRow,
446 G,
447 >
448 where
449 M: drizzle_core::AfterJoin<R, Arg::JoinedTable> + drizzle_core::ScopePush<Arg::JoinedTable>,
450 {
451 SelectBuilder {
452 sql: self.sql.append(arg.into_cross_join_sql()),
453 schema: PhantomData,
454 state: PhantomData,
455 table: PhantomData,
456 marker: PhantomData,
457 row: PhantomData,
458 grouped: PhantomData,
459 }
460 }
461}
462
463// WHERE (available from SelectFromSet and SelectJoinSet)
464impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
465where
466 State: SelectWhereAllowed,
467{
468 /// Adds a WHERE clause to filter query results.
469 ///
470 /// ```rust
471 /// # mod drizzle {
472 /// # pub mod core { pub use drizzle_core::*; }
473 /// # pub mod error { pub use drizzle_core::error::*; }
474 /// # pub mod types { pub use drizzle_types::*; }
475 /// # pub mod migrations { pub use drizzle_migrations::*; }
476 /// # pub use drizzle_types::Dialect;
477 /// # pub use drizzle_types as ddl;
478 /// # pub mod sqlite {
479 /// # pub use drizzle_sqlite::{*, attrs::*};
480 /// # #[cfg(feature = "rusqlite")]
481 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
482 /// # #[cfg(feature = "libsql")]
483 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
484 /// # #[cfg(feature = "turso")]
485 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
486 /// # pub mod prelude {
487 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
488 /// # pub use drizzle_sqlite::{*, attrs::*};
489 /// # pub use drizzle_core::*;
490 /// # }
491 /// # }
492 /// # }
493 /// # use drizzle::sqlite::prelude::*;
494 /// # use drizzle::core::expr::{gt, and, eq};
495 /// # use drizzle::sqlite::builder::QueryBuilder;
496 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String, age: Option<i32> }
497 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
498 /// # let builder = QueryBuilder::new::<Schema>();
499 /// # let Schema { user } = Schema::new();
500 /// // Single condition
501 /// let query = builder
502 /// .select(user.name)
503 /// .from(user)
504 /// .r#where(gt(user.id, 10));
505 /// assert_eq!(
506 /// query.to_sql().sql(),
507 /// r#"SELECT "users"."name" FROM "users" WHERE "users"."id" > ?"#
508 /// );
509 ///
510 /// // Multiple conditions
511 /// let query = builder
512 /// .select(user.name)
513 /// .from(user)
514 /// .r#where(and(gt(user.id, 10), eq(user.name, "Alice")));
515 /// ```
516 #[inline]
517 pub fn r#where<E>(self, condition: E) -> SelectBuilder<'a, S, SelectWhereSet, T, M, R, G>
518 where
519 E: drizzle_core::expr::Expr<'a, SQLiteValue<'a>>,
520 E::SQLType: drizzle_core::types::BooleanLike,
521 {
522 SelectBuilder {
523 sql: self.sql.append(helpers::r#where(condition)),
524 schema: PhantomData,
525 state: PhantomData,
526 table: PhantomData,
527 marker: PhantomData,
528 row: PhantomData,
529 grouped: PhantomData,
530 }
531 }
532}
533
534// GROUP BY (available from SelectFromSet, SelectJoinSet, SelectWhereSet)
535impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
536where
537 State: drizzle_core::GroupByAllowed,
538{
539 /// Adds a GROUP BY clause to the query.
540 ///
541 /// Non-aggregate columns in SELECT must appear in the GROUP BY list, with
542 /// one exception: grouping by a table's single-column primary key
543 /// functionally determines the whole row (SQL:1999), so any scalar column
544 /// of that table may be selected. Prefer `.group_by(table.pk)` over
545 /// listing every selected column — it also lets `SQLite` stream groups in
546 /// key order instead of sorting through a temp B-tree.
547 pub fn group_by<Gr>(
548 self,
549 columns: Gr,
550 ) -> SelectBuilder<'a, S, SelectGroupSet, T, M, R, Gr::Columns>
551 where
552 Gr: drizzle_core::IntoGroupBy<'a, SQLiteValue<'a>>,
553 {
554 SelectBuilder {
555 sql: self.sql.append(helpers::group_by_expr(columns)),
556 schema: PhantomData,
557 state: PhantomData,
558 table: PhantomData,
559 marker: PhantomData,
560 row: PhantomData,
561 grouped: PhantomData,
562 }
563 }
564}
565
566// HAVING (available only from SelectGroupSet)
567impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
568where
569 State: drizzle_core::HavingAllowed,
570{
571 /// Adds a HAVING clause after GROUP BY.
572 pub fn having<E>(self, condition: E) -> SelectBuilder<'a, S, SelectGroupSet, T, M, R, G>
573 where
574 E: drizzle_core::expr::Expr<'a, SQLiteValue<'a>>,
575 E::SQLType: drizzle_core::types::BooleanLike,
576 {
577 SelectBuilder {
578 sql: self.sql.append(helpers::having(condition)),
579 schema: PhantomData,
580 state: PhantomData,
581 table: PhantomData,
582 marker: PhantomData,
583 row: PhantomData,
584 grouped: PhantomData,
585 }
586 }
587}
588
589// ORDER BY (available from many states)
590impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
591where
592 State: SelectOrderAllowed,
593{
594 /// Sorts the query results.
595 #[inline]
596 pub fn order_by<TOrderBy>(
597 self,
598 expressions: TOrderBy,
599 ) -> SelectBuilder<'a, S, SelectOrderSet, T, M, R, G>
600 where
601 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
602 {
603 SelectBuilder {
604 sql: self.sql.append(helpers::order_by(expressions)),
605 schema: PhantomData,
606 state: PhantomData,
607 table: PhantomData,
608 marker: PhantomData,
609 row: PhantomData,
610 grouped: PhantomData,
611 }
612 }
613}
614
615// ORDER BY on a compound query: the combined rows carry no table scope, so the
616// ordering terms are rendered as output column names.
617impl<'a, S, T, M, R, G> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
618 /// Sorts a compound (`UNION` / `INTERSECT` / `EXCEPT`) result by its
619 /// output columns. Column references are rendered unqualified, which is
620 /// the only spelling PostgreSQL and turso accept here.
621 #[inline]
622 pub fn order_by<TOrderBy>(
623 self,
624 expressions: TOrderBy,
625 ) -> SelectBuilder<'a, S, SelectOrderSet, T, M, R, G>
626 where
627 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
628 {
629 SelectBuilder {
630 sql: self
631 .sql
632 .append(drizzle_core::helpers::set_order_by(expressions)),
633 schema: PhantomData,
634 state: PhantomData,
635 table: PhantomData,
636 marker: PhantomData,
637 row: PhantomData,
638 grouped: PhantomData,
639 }
640 }
641}
642
643// LIMIT (available from many states)
644impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
645where
646 State: drizzle_core::LimitAllowed,
647{
648 /// Limits the number of rows returned.
649 ///
650 /// # Panics
651 ///
652 /// Panics when a signed numeric argument is negative or a numeric value
653 /// does not fit in `usize`.
654 #[inline]
655 #[must_use]
656 #[track_caller]
657 pub fn limit<P>(self, limit: P) -> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G>
658 where
659 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
660 {
661 SelectBuilder {
662 sql: self.sql.append(helpers::limit(limit)),
663 schema: PhantomData,
664 state: PhantomData,
665 table: PhantomData,
666 marker: PhantomData,
667 row: PhantomData,
668 grouped: PhantomData,
669 }
670 }
671}
672
673/// States that accept an `OFFSET` without a preceding `LIMIT`.
674#[doc(hidden)]
675pub trait SelectStandaloneOffsetAllowed: drizzle_core::OffsetAllowed {}
676impl SelectStandaloneOffsetAllowed for SelectFromSet {}
677impl SelectStandaloneOffsetAllowed for SelectSetOpSet {}
678
679// OFFSET without LIMIT (available from SelectFromSet and SelectSetOpSet)
680impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
681where
682 State: SelectStandaloneOffsetAllowed,
683{
684 /// Sets the offset for the query results.
685 ///
686 /// `SQLite` only accepts `OFFSET` after a `LIMIT`, so this renders
687 /// `LIMIT -1 OFFSET n`; a negative limit means no limit.
688 ///
689 /// # Panics
690 ///
691 /// Panics when a signed numeric argument is negative or a numeric value
692 /// does not fit in `usize`.
693 #[inline]
694 #[must_use]
695 #[track_caller]
696 pub fn offset<P>(self, offset: P) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>
697 where
698 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
699 {
700 SelectBuilder {
701 sql: self.sql.append(helpers::standalone_offset(offset)),
702 schema: PhantomData,
703 state: PhantomData,
704 table: PhantomData,
705 marker: PhantomData,
706 row: PhantomData,
707 grouped: PhantomData,
708 }
709 }
710}
711
712// OFFSET after LIMIT
713impl<'a, S, T, M, R, G> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G> {
714 /// Sets the offset for the query results.
715 ///
716 /// # Panics
717 ///
718 /// Panics when a signed numeric argument is negative or a numeric value
719 /// does not fit in `usize`.
720 #[inline]
721 #[must_use]
722 #[track_caller]
723 pub fn offset<P>(self, offset: P) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>
724 where
725 P: drizzle_core::PaginationArg<'a, SQLiteValue<'a>>,
726 {
727 SelectBuilder {
728 sql: self.sql.append(helpers::offset(offset)),
729 schema: PhantomData,
730 state: PhantomData,
731 table: PhantomData,
732 marker: PhantomData,
733 row: PhantomData,
734 grouped: PhantomData,
735 }
736 }
737}
738
739//------------------------------------------------------------------------------
740// CTE support
741//------------------------------------------------------------------------------
742
743impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
744where
745 State: drizzle_core::ExecutableState,
746 M: drizzle_core::DerivedSelection<'a, SQLiteValue<'a>, crate::common::SQLiteSchemaType, T>,
747{
748 /// Names this completed query so it can be used as a derived source.
749 ///
750 /// # Panics
751 ///
752 /// Panics when the projection contains duplicate output names. Name a
753 /// computed expression with [`drizzle_core::expr::NamedExt::named`] to
754 /// make each output unique.
755 #[inline]
756 #[must_use]
757 pub fn alias<Name, ScopeProof, AggProof>(
758 self,
759 _name: Name,
760 ) -> drizzle_core::Derived<
761 'a,
762 SQLiteValue<'a>,
763 Name,
764 <M as drizzle_core::DerivedSelection<
765 'a,
766 SQLiteValue<'a>,
767 crate::common::SQLiteSchemaType,
768 T,
769 >>::Projection,
770 Self,
771 >
772 where
773 Name: drizzle_core::Tag,
774 <M as drizzle_core::DerivedSelection<
775 'a,
776 SQLiteValue<'a>,
777 crate::common::SQLiteSchemaType,
778 T,
779 >>::Projection: drizzle_core::DerivedProjection<Name>,
780 M: drizzle_core::row::MarkerScopeValidFor<ScopeProof>
781 + drizzle_core::row::MarkerAggValidFor<G, AggProof>,
782 {
783 // SAFETY: The executable-state, scope, aggregate, and projection
784 // bounds above prove that this query matches the derived projection.
785 unsafe { drizzle_core::Derived::new_unchecked(self) }
786 }
787}
788
789impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
790where
791 State: AsCteState,
792 T: SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>,
793{
794 /// Converts this SELECT query into a typed CTE using alias tag name.
795 #[inline]
796 #[must_use]
797 pub fn into_cte<Tag: drizzle_core::Tag + 'static>(
798 self,
799 ) -> super::CTEView<
800 'a,
801 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::Aliased<Tag>,
802 Self,
803 > {
804 let name = Tag::NAME;
805 super::CTEView::new(
806 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::alias::<Tag>(),
807 name,
808 self,
809 )
810 }
811}
812
813//------------------------------------------------------------------------------
814// Set operation support (UNION / INTERSECT / EXCEPT)
815//------------------------------------------------------------------------------
816
817impl<'a, S, State, T, M, R, G> SelectBuilder<'a, S, State, T, M, R, G>
818where
819 State: drizzle_core::ExecutableState,
820{
821 /// Combines this query with another using UNION.
822 pub fn union(
823 self,
824 other: impl IntoSelect<'a, S, M, R>,
825 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
826 SelectBuilder {
827 sql: helpers::union(self.sql, other.into_select()),
828 schema: PhantomData,
829 state: PhantomData,
830 table: PhantomData,
831 marker: PhantomData,
832 row: PhantomData,
833 grouped: PhantomData,
834 }
835 }
836
837 /// Combines this query with another using UNION ALL.
838 pub fn union_all(
839 self,
840 other: impl IntoSelect<'a, S, M, R>,
841 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
842 SelectBuilder {
843 sql: helpers::union_all(self.sql, other.into_select()),
844 schema: PhantomData,
845 state: PhantomData,
846 table: PhantomData,
847 marker: PhantomData,
848 row: PhantomData,
849 grouped: PhantomData,
850 }
851 }
852
853 /// Combines this query with another using INTERSECT.
854 pub fn intersect(
855 self,
856 other: impl IntoSelect<'a, S, M, R>,
857 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
858 SelectBuilder {
859 sql: helpers::intersect(self.sql, other.into_select()),
860 schema: PhantomData,
861 state: PhantomData,
862 table: PhantomData,
863 marker: PhantomData,
864 row: PhantomData,
865 grouped: PhantomData,
866 }
867 }
868
869 /// Combines this query with another using EXCEPT.
870 pub fn except(
871 self,
872 other: impl IntoSelect<'a, S, M, R>,
873 ) -> SelectBuilder<'a, S, SelectSetOpSet, T, M, R, G> {
874 SelectBuilder {
875 sql: helpers::except(self.sql, other.into_select()),
876 schema: PhantomData,
877 state: PhantomData,
878 table: PhantomData,
879 marker: PhantomData,
880 row: PhantomData,
881 grouped: PhantomData,
882 }
883 }
884}
885
886//------------------------------------------------------------------------------
887// Expr impl for subquery usage
888//------------------------------------------------------------------------------
889
890impl<'a, S, State, T, M, R, G> drizzle_core::expr::Expr<'a, SQLiteValue<'a>>
891 for SelectBuilder<'a, S, State, T, M, R, G>
892where
893 State: drizzle_core::ExecutableState,
894 M: drizzle_core::expr::SubqueryType<'a, SQLiteValue<'a>>,
895{
896 type SQLType = <M as drizzle_core::expr::SubqueryType<'a, SQLiteValue<'a>>>::SQLType;
897 type Nullable = drizzle_core::expr::Null;
898 type Aggregate = drizzle_core::expr::Scalar;
899}
900
901//------------------------------------------------------------------------------
902// IntoSelect conversion trait
903//------------------------------------------------------------------------------
904
905/// Conversion trait for types that can become a `SelectBuilder`.
906/// Used by set operations to accept both raw `SelectBuilder` and `DrizzleBuilder`.
907pub trait IntoSelect<'a, S, M, R> {
908 type State: drizzle_core::ExecutableState;
909 type Table;
910 fn into_select(self) -> SelectBuilder<'a, S, Self::State, Self::Table, M, R>;
911}
912
913impl<'a, S, State: drizzle_core::ExecutableState, T, M, R, G> IntoSelect<'a, S, M, R>
914 for SelectBuilder<'a, S, State, T, M, R, G>
915{
916 type State = State;
917 type Table = T;
918 fn into_select(self) -> SelectBuilder<'a, S, State, T, M, R> {
919 SelectBuilder {
920 sql: self.sql,
921 schema: PhantomData,
922 state: PhantomData,
923 table: PhantomData,
924 marker: PhantomData,
925 row: PhantomData,
926 grouped: PhantomData,
927 }
928 }
929}
930
931mod insert_select_private {
932 use super::{
933 SelectFromSet, SelectGroupSet, SelectJoinSet, SelectLimitSet, SelectOffsetSet,
934 SelectOrderSet, SelectSetOpSet, SelectWhereSet,
935 };
936
937 pub trait Sealed {}
938 pub trait Completed: drizzle_core::ExecutableState {}
939
940 impl Completed for SelectFromSet {}
941 impl Completed for SelectJoinSet {}
942 impl Completed for SelectWhereSet {}
943 impl Completed for SelectGroupSet {}
944 impl Completed for SelectOrderSet {}
945 impl Completed for SelectLimitSet {}
946 impl Completed for SelectOffsetSet {}
947 impl Completed for SelectSetOpSet {}
948}
949
950/// A completed SELECT that can supply rows to an INSERT.
951#[doc(hidden)]
952pub trait CompletedSelect<'a, S, R>: insert_select_private::Sealed {
953 type Marker;
954 type Grouped;
955
956 fn into_select_sql(self) -> drizzle_core::SQL<'a, SQLiteValue<'a>>;
957}
958
959/// Converts a completed SELECT or attached SELECT wrapper into its checked source.
960#[doc(hidden)]
961pub trait IntoSelectQuery<'a, S, R> {
962 type Marker;
963 type Grouped;
964 type Select: CompletedSelect<'a, S, R, Marker = Self::Marker, Grouped = Self::Grouped>;
965
966 fn into_select_query(self) -> Self::Select;
967}
968
969impl<'a, S, State, T, M, R, G> insert_select_private::Sealed
970 for SelectBuilder<'a, S, State, T, M, R, G>
971where
972 State: insert_select_private::Completed,
973{
974}
975
976impl<'a, S, State, T, M, R, G> CompletedSelect<'a, S, R> for SelectBuilder<'a, S, State, T, M, R, G>
977where
978 State: insert_select_private::Completed,
979{
980 type Marker = M;
981 type Grouped = G;
982
983 fn into_select_sql(self) -> drizzle_core::SQL<'a, SQLiteValue<'a>> {
984 self.sql
985 }
986}
987
988impl<'a, S, State, T, M, R, G> IntoSelectQuery<'a, S, R> for SelectBuilder<'a, S, State, T, M, R, G>
989where
990 State: insert_select_private::Completed,
991{
992 type Marker = M;
993 type Grouped = G;
994 type Select = Self;
995
996 fn into_select_query(self) -> Self::Select {
997 self
998 }
999}