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