drizzle_sqlite/builder/select.rs
1use crate::helpers;
2use crate::traits::{SQLiteTable, ToSQLiteSQL};
3use crate::values::SQLiteValue;
4use drizzle_core::{SQL, SQLTable};
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// Const constructors for all marker types
57impl SelectFromSet {
58 #[inline]
59 pub const fn new() -> Self {
60 Self
61 }
62}
63impl SelectJoinSet {
64 #[inline]
65 pub const fn new() -> Self {
66 Self
67 }
68}
69impl SelectWhereSet {
70 #[inline]
71 pub const fn new() -> Self {
72 Self
73 }
74}
75impl SelectGroupSet {
76 #[inline]
77 pub const fn new() -> Self {
78 Self
79 }
80}
81impl SelectOrderSet {
82 #[inline]
83 pub const fn new() -> Self {
84 Self
85 }
86}
87impl SelectLimitSet {
88 #[inline]
89 pub const fn new() -> Self {
90 Self
91 }
92}
93impl SelectOffsetSet {
94 #[inline]
95 pub const fn new() -> Self {
96 Self
97 }
98}
99
100#[doc(hidden)]
101macro_rules! join_impl {
102 () => {
103 join_impl!(natural);
104 join_impl!(natural_left);
105 join_impl!(left);
106 join_impl!(left_outer);
107 join_impl!(natural_left_outer);
108 join_impl!(natural_right);
109 join_impl!(right);
110 join_impl!(right_outer);
111 join_impl!(natural_right_outer);
112 join_impl!(natural_full);
113 join_impl!(full);
114 join_impl!(full_outer);
115 join_impl!(natural_full_outer);
116 join_impl!(inner);
117 join_impl!(cross);
118 };
119 ($type:ident) => {
120 paste! {
121 pub fn [<$type _join>]<U: SQLiteTable<'a>>(
122 self,
123 table: U,
124 condition: impl ToSQLiteSQL<'a>,
125 ) -> SelectBuilder<'a, S, SelectJoinSet, T> {
126 SelectBuilder {
127 sql: self.sql.append(helpers::[<$type _join>](table, condition)),
128 schema: PhantomData,
129 state: PhantomData,
130 table: PhantomData,
131 }
132 }
133 }
134 };
135}
136
137// Mark states that can execute queries as implementing the ExecutableState trait
138impl ExecutableState for SelectFromSet {}
139impl ExecutableState for SelectWhereSet {}
140impl ExecutableState for SelectLimitSet {}
141impl ExecutableState for SelectOffsetSet {}
142impl ExecutableState for SelectOrderSet {}
143impl ExecutableState for SelectGroupSet {}
144impl ExecutableState for SelectJoinSet {}
145
146//------------------------------------------------------------------------------
147// SelectBuilder Definition
148//------------------------------------------------------------------------------
149
150/// Builds a SELECT query specifically for SQLite.
151///
152/// `SelectBuilder` provides a type-safe, fluent API for constructing SELECT statements
153/// with compile-time verification of query structure and table relationships.
154///
155/// ## Type Parameters
156///
157/// - `Schema`: The database schema type, ensuring only valid tables can be referenced
158/// - `State`: The current builder state, enforcing proper query construction order
159/// - `Table`: The primary table being queried (when applicable)
160///
161/// ## Query Building Flow
162///
163/// 1. Start with `QueryBuilder::select()` to specify columns
164/// 2. Add `from()` to specify the source table
165/// 3. Optionally add joins, conditions, grouping, ordering, and limits
166///
167/// ## Basic Usage
168///
169/// ```rust
170/// use drizzle_sqlite::builder::QueryBuilder;
171/// use drizzle_macros::{SQLiteTable, SQLiteSchema};
172/// use drizzle_core::ToSQL;
173///
174/// #[SQLiteTable(name = "users")]
175/// struct User {
176/// #[integer(primary)]
177/// id: i32,
178/// #[text]
179/// name: String,
180/// #[text]
181/// email: Option<String>,
182/// }
183///
184/// #[derive(SQLiteSchema)]
185/// struct Schema {
186/// user: User,
187/// }
188///
189/// let builder = QueryBuilder::new::<Schema>();
190/// let Schema { user } = Schema::new();
191///
192/// // Basic SELECT
193/// let query = builder.select(user.name).from(user);
194/// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
195///
196/// // SELECT with WHERE clause
197/// use drizzle_core::expressions::conditions::gt;
198/// let query = builder
199/// .select((user.id, user.name))
200/// .from(user)
201/// .r#where(gt(user.id, 10));
202/// assert_eq!(
203/// query.to_sql().sql(),
204/// r#"SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?"#
205/// );
206/// ```
207///
208/// ## Advanced Queries
209///
210/// ### JOIN Operations
211/// ```rust
212/// # use drizzle_sqlite::builder::QueryBuilder;
213/// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
214/// # use drizzle_core::{ToSQL, expressions::conditions::eq};
215/// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String }
216/// # #[SQLiteTable(name = "posts")] struct Post { #[integer(primary)] id: i32, #[integer] user_id: i32, #[text] title: String }
217/// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
218/// # let builder = QueryBuilder::new::<Schema>();
219/// # let Schema { user, post } = Schema::new();
220/// let query = builder
221/// .select((user.name, post.title))
222/// .from(user)
223/// .join(post, eq(user.id, post.user_id));
224/// ```
225///
226/// ### Ordering and Limiting
227/// ```rust
228/// # use drizzle_sqlite::builder::QueryBuilder;
229/// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
230/// # use drizzle_core::{ToSQL, OrderBy};
231/// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String }
232/// # #[derive(SQLiteSchema)] struct Schema { user: User }
233/// # let builder = QueryBuilder::new::<Schema>();
234/// # let Schema { user } = Schema::new();
235/// let query = builder
236/// .select(user.name)
237/// .from(user)
238/// .order_by(OrderBy::asc(user.name))
239/// .limit(10);
240/// ```
241pub type SelectBuilder<'a, Schema, State, Table = ()> =
242 super::QueryBuilder<'a, Schema, State, Table>;
243
244//------------------------------------------------------------------------------
245// Initial State Implementation
246//------------------------------------------------------------------------------
247
248impl<'a, S> SelectBuilder<'a, S, SelectInitial> {
249 /// Specifies the table or subquery to select FROM.
250 ///
251 /// This method transitions the builder from the initial state to the FROM state,
252 /// enabling subsequent WHERE, JOIN, ORDER BY, and other clauses.
253 ///
254 /// # Examples
255 ///
256 /// ```rust
257 /// # use drizzle_sqlite::builder::QueryBuilder;
258 /// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
259 /// # use drizzle_core::ToSQL;
260 /// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String }
261 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
262 /// # let builder = QueryBuilder::new::<Schema>();
263 /// # let Schema { user } = Schema::new();
264 /// // Select from a table
265 /// let query = builder.select(user.name).from(user);
266 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
267 /// ```
268 #[inline]
269 pub fn from<T>(self, query: T) -> SelectBuilder<'a, S, SelectFromSet, T>
270 where
271 T: ToSQLiteSQL<'a>,
272 {
273 let sql = self.sql.append(helpers::from(query));
274 SelectBuilder {
275 sql,
276 schema: PhantomData,
277 state: PhantomData,
278 table: PhantomData,
279 }
280 }
281}
282
283//------------------------------------------------------------------------------
284// Post-FROM State Implementation
285//------------------------------------------------------------------------------
286
287impl<'a, S, T> SelectBuilder<'a, S, SelectFromSet, T>
288where
289 T: SQLiteTable<'a>,
290{
291 /// Adds an INNER JOIN clause to the query.
292 ///
293 /// Joins another table to the current query using the specified condition.
294 /// The joined table must be part of the schema and the condition should
295 /// relate columns from both tables.
296 ///
297 /// # Examples
298 ///
299 /// ```rust
300 /// # use drizzle_sqlite::builder::QueryBuilder;
301 /// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
302 /// # use drizzle_core::{ToSQL, expressions::conditions::eq};
303 /// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String }
304 /// # #[SQLiteTable(name = "posts")] struct Post { #[integer(primary)] id: i32, #[integer] user_id: i32, #[text] title: String }
305 /// # #[derive(SQLiteSchema)] struct Schema { user: User, post: Post }
306 /// # let builder = QueryBuilder::new::<Schema>();
307 /// # let Schema { user, post } = Schema::new();
308 /// let query = builder
309 /// .select((user.name, post.title))
310 /// .from(user)
311 /// .join(post, eq(user.id, post.user_id));
312 /// assert_eq!(
313 /// query.to_sql().sql(),
314 /// r#"SELECT "users"."name", "posts"."title" FROM "users" JOIN "posts" ON "users"."id" = "posts"."user_id""#
315 /// );
316 /// ```
317 #[inline]
318 pub fn join<U: SQLiteTable<'a>>(
319 self,
320 table: U,
321 condition: impl ToSQLiteSQL<'a>,
322 ) -> SelectBuilder<'a, S, SelectJoinSet, T> {
323 SelectBuilder {
324 sql: self.sql.append(helpers::join(table, condition)),
325 schema: PhantomData,
326 state: PhantomData,
327 table: PhantomData,
328 }
329 }
330
331 join_impl!();
332
333 /// Adds a WHERE clause to filter query results.
334 ///
335 /// This method applies conditions to filter the rows returned by the query.
336 /// You can use various condition functions from `drizzle_core::expressions::conditions`.
337 ///
338 /// # Examples
339 ///
340 /// ```rust
341 /// # use drizzle_sqlite::builder::QueryBuilder;
342 /// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
343 /// # use drizzle_core::{ToSQL, expressions::conditions::{eq, gt, and}};
344 /// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String, #[integer] age: Option<i32> }
345 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
346 /// # let builder = QueryBuilder::new::<Schema>();
347 /// # let Schema { user } = Schema::new();
348 /// // Single condition
349 /// let query = builder
350 /// .select(user.name)
351 /// .from(user)
352 /// .r#where(gt(user.id, 10));
353 /// assert_eq!(
354 /// query.to_sql().sql(),
355 /// r#"SELECT "users"."name" FROM "users" WHERE "users"."id" > ?"#
356 /// );
357 ///
358 /// // Multiple conditions
359 /// let query = builder
360 /// .select(user.name)
361 /// .from(user)
362 /// .r#where(and([gt(user.id, 10), eq(user.name, "Alice")]));
363 /// ```
364 #[inline]
365 pub fn r#where(
366 self,
367 condition: impl ToSQLiteSQL<'a>,
368 ) -> SelectBuilder<'a, S, SelectWhereSet, T> {
369 SelectBuilder {
370 sql: self.sql.append(helpers::r#where(condition)),
371 schema: PhantomData,
372 state: PhantomData,
373 table: PhantomData,
374 }
375 }
376
377 /// Adds a GROUP BY clause to the query
378 pub fn group_by(
379 self,
380 expressions: Vec<SQL<'a, SQLiteValue<'a>>>,
381 ) -> SelectBuilder<'a, S, SelectGroupSet, T> {
382 SelectBuilder {
383 sql: self.sql.append(helpers::group_by(expressions)),
384 schema: PhantomData,
385 state: PhantomData,
386 table: PhantomData,
387 }
388 }
389
390 /// Limits the number of rows returned
391 #[inline]
392 pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T> {
393 SelectBuilder {
394 sql: self.sql.append(helpers::limit(limit)),
395 schema: PhantomData,
396 state: PhantomData,
397 table: PhantomData,
398 }
399 }
400
401 /// Sets the offset for the query results
402 #[inline]
403 pub fn offset(self, offset: usize) -> SelectBuilder<'a, S, SelectOffsetSet, T> {
404 SelectBuilder {
405 sql: self.sql.append(helpers::offset(offset)),
406 schema: PhantomData,
407 state: PhantomData,
408 table: PhantomData,
409 }
410 }
411
412 /// Sorts the query results
413 #[inline]
414 pub fn order_by<TOrderBy>(
415 self,
416 expressions: TOrderBy,
417 ) -> SelectBuilder<'a, S, SelectOrderSet, T>
418 where
419 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
420 {
421 SelectBuilder {
422 sql: self.sql.append(helpers::order_by(expressions)),
423 schema: PhantomData,
424 state: PhantomData,
425 table: PhantomData,
426 }
427 }
428
429 /// Converts this SELECT query into a CTE (Common Table Expression) with the given name.
430 ///
431 /// The returned `CTEView` provides typed access to the table's columns through
432 /// an aliased table instance, allowing you to reference CTE columns in subsequent queries.
433 ///
434 /// # Type Parameters
435 ///
436 /// The `T` (Table) type parameter from `.from(table)` determines the aliased type,
437 /// enabling type-safe field access on the returned CTE.
438 ///
439 /// # Examples
440 ///
441 /// ```rust
442 /// # use drizzle_sqlite::builder::QueryBuilder;
443 /// # use drizzle_macros::{SQLiteTable, SQLiteSchema};
444 /// # use drizzle_core::ToSQL;
445 /// # #[SQLiteTable(name = "users")] struct User { #[integer(primary)] id: i32, #[text] name: String }
446 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
447 /// # let builder = QueryBuilder::new::<Schema>();
448 /// # let Schema { user } = Schema::new();
449 /// // Create a CTE from a select query
450 /// let active_users = builder
451 /// .select((user.id, user.name))
452 /// .from(user)
453 /// .as_cte("active_users");
454 ///
455 /// // Use the CTE with typed field access
456 /// let query = builder
457 /// .with(&active_users)
458 /// .select(active_users.name) // Deref gives access to aliased table fields
459 /// .from(&active_users);
460 /// assert_eq!(
461 /// query.to_sql().sql(),
462 /// r#"WITH active_users AS (SELECT "users"."id", "users"."name" FROM "users") SELECT "active_users"."name" FROM "active_users""#
463 /// );
464 /// ```
465 #[inline]
466 pub fn as_cte(
467 self,
468 name: &'static str,
469 ) -> super::CTEView<
470 'a,
471 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::Aliased,
472 Self,
473 > {
474 super::CTEView::new(
475 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::alias(name),
476 name,
477 self,
478 )
479 }
480}
481
482//------------------------------------------------------------------------------
483// Post-JOIN State Implementation
484//------------------------------------------------------------------------------
485
486impl<'a, S, T> SelectBuilder<'a, S, SelectJoinSet, T> {
487 /// Adds a WHERE condition after a JOIN
488 #[inline]
489 pub fn r#where(
490 self,
491 condition: SQL<'a, SQLiteValue<'a>>,
492 ) -> SelectBuilder<'a, S, SelectWhereSet, T> {
493 SelectBuilder {
494 sql: self.sql.append(crate::helpers::r#where(condition)),
495 schema: PhantomData,
496 state: PhantomData,
497 table: PhantomData,
498 }
499 }
500 /// Sorts the query results
501 #[inline]
502 pub fn order_by<TOrderBy>(
503 self,
504 expressions: TOrderBy,
505 ) -> SelectBuilder<'a, S, SelectOrderSet, T>
506 where
507 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
508 {
509 SelectBuilder {
510 sql: self.sql.append(helpers::order_by(expressions)),
511 schema: PhantomData,
512 state: PhantomData,
513 table: PhantomData,
514 }
515 }
516 /// Adds a JOIN clause to the query
517 #[inline]
518 pub fn join<U: SQLiteTable<'a>>(
519 self,
520 table: U,
521 condition: impl ToSQLiteSQL<'a>,
522 ) -> SelectBuilder<'a, S, SelectJoinSet, T> {
523 SelectBuilder {
524 sql: self.sql.append(helpers::join(table, condition)),
525 schema: PhantomData,
526 state: PhantomData,
527 table: PhantomData,
528 }
529 }
530 join_impl!();
531}
532
533//------------------------------------------------------------------------------
534// Post-WHERE State Implementation
535//------------------------------------------------------------------------------
536
537impl<'a, S, T> SelectBuilder<'a, S, SelectWhereSet, T> {
538 /// Adds a GROUP BY clause after a WHERE
539 pub fn group_by(
540 self,
541 expressions: Vec<SQL<'a, SQLiteValue<'a>>>,
542 ) -> SelectBuilder<'a, S, SelectGroupSet, T> {
543 SelectBuilder {
544 sql: self.sql.append(helpers::group_by(expressions)),
545 schema: PhantomData,
546 state: PhantomData,
547 table: PhantomData,
548 }
549 }
550
551 /// Adds an ORDER BY clause after a WHERE
552 pub fn order_by<TOrderBy>(
553 self,
554 expressions: TOrderBy,
555 ) -> SelectBuilder<'a, S, SelectOrderSet, T>
556 where
557 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
558 {
559 SelectBuilder {
560 sql: self.sql.append(helpers::order_by(expressions)),
561 schema: PhantomData,
562 state: PhantomData,
563 table: PhantomData,
564 }
565 }
566
567 /// Adds a LIMIT clause after a WHERE
568 pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T> {
569 SelectBuilder {
570 sql: self.sql.append(helpers::limit(limit)),
571 schema: PhantomData,
572 state: PhantomData,
573 table: PhantomData,
574 }
575 }
576}
577
578impl<'a, S, T> SelectBuilder<'a, S, SelectWhereSet, T>
579where
580 T: SQLiteTable<'a>,
581{
582 /// Converts this SELECT query into a CTE (Common Table Expression) with the given name.
583 #[inline]
584 pub fn as_cte(
585 self,
586 name: &'static str,
587 ) -> super::CTEView<
588 'a,
589 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::Aliased,
590 Self,
591 > {
592 super::CTEView::new(
593 <T as SQLTable<'a, crate::common::SQLiteSchemaType, SQLiteValue<'a>>>::alias(name),
594 name,
595 self,
596 )
597 }
598}
599
600//------------------------------------------------------------------------------
601// Post-GROUP BY State Implementation
602//------------------------------------------------------------------------------
603
604impl<'a, S, T> SelectBuilder<'a, S, SelectGroupSet, T> {
605 /// Adds a HAVING clause after GROUP BY
606 pub fn having(
607 self,
608 condition: SQL<'a, SQLiteValue<'a>>,
609 ) -> SelectBuilder<'a, S, SelectGroupSet, T> {
610 SelectBuilder {
611 sql: self.sql.append(helpers::having(condition)),
612 schema: PhantomData,
613 state: PhantomData,
614 table: PhantomData,
615 }
616 }
617
618 /// Adds an ORDER BY clause after GROUP BY
619 pub fn order_by<TOrderBy>(
620 self,
621 expressions: TOrderBy,
622 ) -> SelectBuilder<'a, S, SelectOrderSet, T>
623 where
624 TOrderBy: drizzle_core::ToSQL<'a, SQLiteValue<'a>>,
625 {
626 SelectBuilder {
627 sql: self.sql.append(helpers::order_by(expressions)),
628 schema: PhantomData,
629 state: PhantomData,
630 table: PhantomData,
631 }
632 }
633}
634
635//------------------------------------------------------------------------------
636// Post-ORDER BY State Implementation
637//------------------------------------------------------------------------------
638
639impl<'a, S, T> SelectBuilder<'a, S, SelectOrderSet, T> {
640 /// Adds a LIMIT clause after ORDER BY
641 pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T> {
642 let sql = helpers::limit(limit);
643 println!("LIMIT SQL: {}", sql);
644 SelectBuilder {
645 sql: self.sql.append(sql),
646 schema: PhantomData,
647 state: PhantomData,
648 table: PhantomData,
649 }
650 }
651}
652
653//------------------------------------------------------------------------------
654// Post-LIMIT State Implementation
655//------------------------------------------------------------------------------
656
657impl<'a, S, T> SelectBuilder<'a, S, SelectLimitSet, T> {
658 /// Adds an OFFSET clause after LIMIT
659 pub fn offset(self, offset: usize) -> SelectBuilder<'a, S, SelectOffsetSet, T> {
660 SelectBuilder {
661 sql: self.sql.append(helpers::offset(offset)),
662 schema: PhantomData,
663 state: PhantomData,
664 table: PhantomData,
665 }
666 }
667}