bottle_orm/query_builder.rs
1//! # Query Builder Module
2//!
3//! This module provides a fluent interface for constructing and executing SQL queries.
4//! It handles SELECT, INSERT, filtering (WHERE), pagination (LIMIT/OFFSET), and ordering operations
5//! with type-safe parameter binding across different database drivers.
6//!
7//! ## Features
8//!
9//! - **Fluent API**: Chainable methods for building complex queries
10//! - **Type-Safe Binding**: Automatic parameter binding with support for multiple types
11//! - **Multi-Driver Support**: Works with PostgreSQL, MySQL, and SQLite
12//! - **UUID Support**: Full support for UUID versions 1-7
13//! - **Pagination**: Built-in LIMIT/OFFSET support with helper methods
14//! - **Custom Filters**: Support for manual SQL construction with closures
15//!
16//! ## Example Usage
17//!
18//! ```rust,ignore
19//! use bottle_orm::{Database, Model};
20//! use uuid::Uuid;
21//!
22//! // Simple query
23//! let users: Vec<User> = db.model::<User>()
24//! .filter("age", ">=", 18)
25//! .order("created_at DESC")
26//! .limit(10)
27//! .scan()
28//! .await?;
29//!
30//! // Query with UUID filter
31//! let user_id = Uuid::new_v4();
32//! let user: User = db.model::<User>()
33//! .filter("id", "=", user_id)
34//! .first()
35//! .await?;
36//!
37//! // Insert a new record
38//! let new_user = User {
39//! id: Uuid::new_v7(uuid::Timestamp::now(uuid::NoContext)),
40//! username: "john_doe".to_string(),
41//! age: 25,
42//! };
43//! db.model::<User>().insert(&new_user).await?;
44//! ```
45
46// ============================================================================
47// External Crate Imports
48// ============================================================================
49
50use futures::future::BoxFuture;
51use heck::ToSnakeCase;
52use sqlx::{any::AnyArguments, Any, Arguments, Decode, Encode, Row, Type};
53use std::marker::PhantomData;
54use uuid::Uuid;
55
56// ============================================================================
57// Internal Crate Imports
58// ============================================================================
59
60use crate::{
61 any_struct::FromAnyRow,
62 database::{Connection, Drivers},
63 model::{ColumnInfo, Model},
64 temporal::{self, is_temporal_type},
65 value_binding::ValueBinder,
66 AnyImpl, Error,
67};
68
69// ============================================================================
70// Type Aliases
71// ============================================================================
72
73/// A type alias for filter closures that support manual SQL construction and argument binding.
74///
75/// Filter functions receive the following parameters:
76/// 1. `&mut String` - The SQL query buffer being built
77/// 2. `&mut AnyArguments` - The argument container for binding values
78/// 3. `&Drivers` - The current database driver (determines placeholder syntax)
79/// 4. `&mut usize` - The argument counter (for PostgreSQL `$n` placeholders)
80///
81/// ## Example
82///
83/// ```rust,ignore
84/// let custom_filter: FilterFn = Box::new(|query, args, driver, counter| {
85/// query.push_str(" AND age > ");
86/// match driver {
87/// Drivers::Postgres => {
88/// query.push_str(&format!("${}", counter));
89/// *counter += 1;
90/// }
91/// _ => query.push('?'),
92/// }
93/// args.add(18);
94/// });
95/// });\n/// ```
96pub type FilterFn = Box<dyn Fn(&mut String, &mut AnyArguments<'_>, &Drivers, &mut usize) + Send + Sync>;
97
98// ============================================================================
99// Comparison Operators Enum
100// ============================================================================
101
102/// Type-safe comparison operators for filter conditions.
103///
104/// Use these instead of string operators for autocomplete support and type safety.
105///
106/// # Example
107///
108/// ```rust,ignore
109/// use bottle_orm::Op;
110///
111/// db.model::<User>()
112/// .filter(user_fields::AGE, Op::Gte, 18)
113/// .filter(user_fields::NAME, Op::Like, "%John%")
114/// .scan()
115/// .await?;
116/// ```
117#[derive(Debug, Clone, Copy, PartialEq, Eq)]
118pub enum Op {
119 /// Equal: `=`
120 Eq,
121 /// Not Equal: `!=` or `<>`
122 Ne,
123 /// Greater Than: `>`
124 Gt,
125 /// Greater Than or Equal: `>=`
126 Gte,
127 /// Less Than: `<`
128 Lt,
129 /// Less Than or Equal: `<=`
130 Lte,
131 /// SQL LIKE pattern matching
132 Like,
133 /// SQL NOT LIKE pattern matching
134 NotLike,
135 /// SQL IN (for arrays/lists)
136 In,
137 /// SQL NOT IN
138 NotIn,
139}
140
141impl Op {
142 /// Converts the operator to its SQL string representation.
143 pub fn as_sql(&self) -> &'static str {
144 match self {
145 Op::Eq => "=",
146 Op::Ne => "!=",
147 Op::Gt => ">",
148 Op::Gte => ">=",
149 Op::Lt => "<",
150 Op::Lte => "<=",
151 Op::Like => "LIKE",
152 Op::NotLike => "NOT LIKE",
153 Op::In => "IN",
154 Op::NotIn => "NOT IN",
155 }
156 }
157}
158
159// ============================================================================
160// QueryBuilder Struct
161// ============================================================================
162
163/// A fluent Query Builder for constructing SQL queries.
164///
165/// `QueryBuilder` provides a type-safe, ergonomic interface for building and executing
166/// SQL queries across different database backends. It supports filtering, ordering,
167/// pagination, and both SELECT and INSERT operations.
168///
169/// ## Type Parameter
170///
171/// * `'a` - Lifetime of the database reference (used for PhantomData)
172/// * `T` - The Model type this query operates on
173/// * `E` - The connection type (Database or Transaction)
174///
175/// ## Fields
176///
177/// * `db` - Reference to the database connection pool or transaction
178/// * `table_name` - Static string containing the table name
179/// * `columns_info` - Metadata about each column in the table
180/// * `columns` - List of column names in snake_case format
181/// * `select_columns` - Specific columns to select (empty = SELECT *)
182/// * `where_clauses` - List of filter functions to apply
183/// * `order_clauses` - List of ORDER BY clauses
184/// * `limit` - Maximum number of rows to return
185/// * `offset` - Number of rows to skip (for pagination)
186/// * `_marker` - PhantomData to bind the generic type T
187pub struct QueryBuilder<'a, T, E> {
188 /// Reference to the database connection pool
189 pub(crate) tx: E,
190
191 /// Database driver type
192 pub(crate) driver: Drivers,
193
194 /// Name of the database table (in original case)
195 pub(crate) table_name: &'static str,
196
197 /// Metadata information about each column
198 pub(crate) columns_info: Vec<ColumnInfo>,
199
200 /// List of column names (in snake_case)
201 pub(crate) columns: Vec<String>,
202
203 /// Specific columns to select (empty means SELECT *)
204 pub(crate) select_columns: Vec<String>,
205
206 /// Collection of WHERE clause filter functions
207 pub(crate) where_clauses: Vec<FilterFn>,
208
209 /// Collection of ORDER BY clauses
210 pub(crate) order_clauses: Vec<String>,
211
212 /// Collection of JOIN clause to filter entry tables
213 pub(crate) joins_clauses: Vec<String>,
214
215 /// Maximum number of rows to return (LIMIT)
216 pub(crate) limit: Option<usize>,
217
218 /// Number of rows to skip (OFFSET)
219 pub(crate) offset: Option<usize>,
220
221 /// Activate debug mode in query
222 pub(crate) debug_mode: bool,
223
224 /// Clauses for GROUP BY
225 pub(crate) group_by_clauses: Vec<String>,
226
227 /// Clauses for HAVING
228 pub(crate) having_clauses: Vec<FilterFn>,
229
230 /// Distinct flag
231 pub(crate) is_distinct: bool,
232
233 /// Columns to omit from the query results (inverse of select_columns)
234 pub(crate) omit_columns: Vec<String>,
235
236 /// Whether to include soft-deleted records in query results
237 pub(crate) with_deleted: bool,
238
239 /// PhantomData to bind the generic type T
240 pub(crate) _marker: PhantomData<&'a T>,
241}
242
243// ============================================================================
244// QueryBuilder Implementation
245// ============================================================================
246
247impl<'a, T, E> QueryBuilder<'a, T, E>
248where
249 T: Model + Send + Sync + Unpin,
250 E: Connection + Send,
251{
252 // ========================================================================
253 // Constructor
254 // ========================================================================
255
256 /// Creates a new QueryBuilder instance.
257 ///
258 /// This constructor is typically called internally via `db.model::<T>()`.
259 /// You rarely need to call this directly.
260 ///
261 /// # Arguments
262 ///
263 /// * `db` - Reference to the database connection
264 /// * `table_name` - Name of the table to query
265 /// * `columns_info` - Metadata about table columns
266 /// * `columns` - List of column names
267 ///
268 /// # Returns
269 ///
270 /// A new `QueryBuilder` instance ready for query construction
271 ///
272 /// # Example
273 ///
274 /// ```rust,ignore
275 /// // Usually called via db.model::<User>()
276 /// let query = db.model::<User>();
277 /// ```
278 pub fn new(
279 tx: E,
280 driver: Drivers,
281 table_name: &'static str,
282 columns_info: Vec<ColumnInfo>,
283 columns: Vec<String>,
284 ) -> Self {
285 // Pre-populate omit_columns with globally omitted columns (from #[orm(omit)] attribute)
286 let omit_columns: Vec<String> =
287 columns_info.iter().filter(|c| c.omit).map(|c| c.name.to_snake_case()).collect();
288
289 Self {
290 tx,
291 driver,
292 table_name,
293 columns_info,
294 columns,
295 debug_mode: false,
296 select_columns: Vec::new(),
297 where_clauses: Vec::new(),
298 order_clauses: Vec::new(),
299 joins_clauses: Vec::new(),
300 group_by_clauses: Vec::new(),
301 having_clauses: Vec::new(),
302 is_distinct: false,
303 omit_columns,
304 limit: None,
305 offset: None,
306 with_deleted: false,
307 _marker: PhantomData,
308 }
309 }
310
311 // ========================================================================
312 // Query Building Methods
313 // ========================================================================
314
315 /// Adds a WHERE clause to the query.
316 ///
317 /// This method adds a filter condition to the query. Multiple filters can be chained
318 /// and will be combined with AND operators. The value is bound as a parameter to
319 /// prevent SQL injection.
320 ///
321 /// # Type Parameters
322 ///
323 /// * `V` - The type of the value to filter by. Must be encodable for SQL queries.
324 ///
325 /// # Arguments
326 ///
327 /// * `col` - The column name to filter on
328 /// * `op` - The comparison operator (e.g., "=", ">", "LIKE", "IN")
329 /// * `value` - The value to compare against
330 ///
331 /// # Supported Types
332 ///
333 /// - Primitives: `i32`, `i64`, `f64`, `bool`, `String`
334 /// - UUID: `Uuid` (all versions 1-7)
335 /// - Date/Time: `DateTime<Utc>`, `NaiveDateTime`, `NaiveDate`, `NaiveTime`
336 /// - Options: `Option<T>` for any supported type T
337 ///
338 /// # Example
339 ///
340 /// ```rust,ignore
341 /// // Filter by integer
342 /// query.filter("age", ">=", 18)
343 ///
344 /// // Filter by string
345 /// query.filter("username", "=", "john_doe")
346 ///
347 /// // Filter by UUID
348 /// let user_id = Uuid::new_v4();
349 /// query.filter("id", "=", user_id)
350 ///
351 /// // Filter with LIKE operator
352 /// query.filter("email", "LIKE", "%@example.com")
353 ///
354 /// // Chain multiple filters
355 /// query
356 /// .filter("age", Op::Gte, 18)
357 /// .filter("active", Op::Eq, true)
358 /// .filter("role", Op::Eq, "admin")
359 /// ```
360 pub fn filter<V>(mut self, col: &'static str, op: Op, value: V) -> Self
361 where
362 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
363 {
364 let op_str = op.as_sql();
365 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
366 query.push_str(" AND ");
367 if let Some((table, column)) = col.split_once(".") {
368 query.push_str(&format!("\"{}\".\"{}\"", table, column));
369 } else {
370 query.push_str(&format!("\"{}\"", col));
371 }
372 query.push(' ');
373 query.push_str(op_str);
374 query.push(' ');
375
376 // Handle different placeholder syntaxes based on database driver
377 match driver {
378 // PostgreSQL uses numbered placeholders: $1, $2, $3, ...
379 Drivers::Postgres => {
380 query.push_str(&format!("${}", arg_counter));
381 *arg_counter += 1;
382 }
383 // MySQL and SQLite use question mark placeholders: ?
384 _ => query.push('?'),
385 }
386
387 // Bind the value to the query
388 let _ = args.add(value.clone());
389 });
390
391 self.where_clauses.push(clause);
392 self
393 }
394
395 /// Adds an equality filter to the query.
396 ///
397 /// This is a convenience wrapper around `filter()` for simple equality checks.
398 /// It is equivalent to calling `filter(col, "=", value)`.
399 ///
400 /// # Type Parameters
401 ///
402 /// * `V` - The type of the value to compare against.
403 ///
404 /// # Arguments
405 ///
406 /// * `col` - The column name to filter on.
407 /// * `value` - The value to match.
408 ///
409 /// # Example
410 ///
411 /// ```rust,ignore
412 /// // Equivalent to filter("age", Op::Eq, 18)
413 /// query.equals("age", 18)
414 /// ```
415 pub fn equals<V>(self, col: &'static str, value: V) -> Self
416 where
417 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
418 {
419 self.filter(col, Op::Eq, value)
420 }
421
422 /// Adds an ORDER BY clause to the query.
423 ///
424 /// Specifies the sort order for the query results. Multiple order clauses
425 /// can be added and will be applied in the order they were added.
426 ///
427 /// # Arguments
428 ///
429 /// * `order` - The ORDER BY expression (e.g., "created_at DESC", "age ASC, name DESC")
430 ///
431 /// # Example
432 ///
433 /// ```rust,ignore
434 /// // Single column ascending (ASC is default)
435 /// query.order("age")
436 ///
437 /// // Single column descending
438 /// query.order("created_at DESC")
439 ///
440 /// // Multiple columns
441 /// query.order("age DESC, username ASC")
442 ///
443 /// // Chain multiple order clauses
444 /// query
445 /// .order("priority DESC")
446 /// .order("created_at ASC")
447 /// ```
448 pub fn order(mut self, order: &str) -> Self {
449 self.order_clauses.push(order.to_string());
450 self
451 }
452
453 /// Placeholder for eager loading relationships (preload).
454 ///
455 /// This method is reserved for future implementation of relationship preloading.
456 /// Currently, it returns `self` unchanged to maintain the fluent interface.
457 ///
458 /// # Future Implementation
459 ///
460 /// Will support eager loading of related models to avoid N+1 query problems:
461 ///
462 /// ```rust,ignore
463 /// // Future usage example
464 /// query.preload("posts").preload("comments")
465 /// ```
466 // pub fn preload(self) -> Self {
467 // // TODO: Implement relationship preloading
468 // self
469 // }
470
471 /// Activates debug mode for this query.
472 ///
473 /// When enabled, the generated SQL query will be logged using the `log` crate
474 /// at the `DEBUG` level before execution.
475 ///
476 /// # Note
477 ///
478 /// To see the output, you must initialize a logger in your application (e.g., using `env_logger`)
479 /// and configure it to display `debug` logs for `bottle_orm`.
480 ///
481 /// # Example
482 ///
483 /// ```rust,ignore
484 /// db.model::<User>()
485 /// .filter("active", "=", true)
486 /// .debug() // Logs SQL: SELECT * FROM "user" WHERE "active" = $1
487 /// .scan()
488 /// .await?;
489 /// ```
490 pub fn debug(mut self) -> Self {
491 self.debug_mode = true;
492 self
493 }
494
495 /// Adds an IS NULL filter for the specified column.
496 ///
497 /// # Arguments
498 ///
499 /// * `col` - The column name to check for NULL
500 ///
501 /// # Example
502 ///
503 /// ```rust,ignore
504 /// db.model::<User>()
505 /// .is_null("deleted_at")
506 /// .scan()
507 /// .await?;
508 /// // SQL: SELECT * FROM "user" WHERE "deleted_at" IS NULL
509 /// ```
510 pub fn is_null(mut self, col: &str) -> Self {
511 let col_owned = col.to_string();
512 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
513 query.push_str(" AND ");
514 if let Some((table, column)) = col_owned.split_once(".") {
515 query.push_str(&format!("\"{}\".\"{}\"", table, column));
516 } else {
517 query.push_str(&format!("\"{}\"", col_owned));
518 }
519 query.push_str(" IS NULL");
520 });
521 self.where_clauses.push(clause);
522 self
523 }
524
525 /// Adds an IS NOT NULL filter for the specified column.
526 ///
527 /// # Arguments
528 ///
529 /// * `col` - The column name to check for NOT NULL
530 ///
531 /// # Example
532 ///
533 /// ```rust,ignore
534 /// db.model::<User>()
535 /// .is_not_null("email")
536 /// .scan()
537 /// .await?;
538 /// // SQL: SELECT * FROM "user" WHERE "email" IS NOT NULL
539 /// ```
540 pub fn is_not_null(mut self, col: &str) -> Self {
541 let col_owned = col.to_string();
542 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
543 query.push_str(" AND ");
544 if let Some((table, column)) = col_owned.split_once(".") {
545 query.push_str(&format!("\"{}\".\"{}\"", table, column));
546 } else {
547 query.push_str(&format!("\"{}\"", col_owned));
548 }
549 query.push_str(" IS NOT NULL");
550 });
551 self.where_clauses.push(clause);
552 self
553 }
554
555 /// Includes soft-deleted records in query results.
556 ///
557 /// By default, queries on models with a `#[orm(soft_delete)]` column exclude
558 /// records where that column is not NULL. This method disables that filter.
559 ///
560 /// # Example
561 ///
562 /// ```rust,ignore
563 /// // Get all users including deleted ones
564 /// db.model::<User>()
565 /// .with_deleted()
566 /// .scan()
567 /// .await?;
568 /// ```
569 pub fn with_deleted(mut self) -> Self {
570 self.with_deleted = true;
571 self
572 }
573
574 /// Placeholder for JOIN operations.
575 ///
576 /// This method is reserved for future implementation of SQL JOINs.
577 /// Currently, it returns `self` unchanged to maintain the fluent interface.
578 ///
579 /// # Future Implementation
580 ///
581 /// Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):
582 ///
583 /// ```rust,ignore
584 /// Adds a JOIN clause to the query.
585 ///
586 /// # Arguments
587 ///
588 /// * `table` - The name of the table to join.
589 /// * `s_query` - The ON clause condition (e.g., "users.id = posts.user_id").
590 ///
591 /// # Example
592 ///
593 /// ```rust,ignore
594 /// query.join("posts", "users.id = posts.user_id")
595 /// ```
596 pub fn join(mut self, table: &str, s_query: &str) -> Self {
597 let trimmed_value = s_query.replace(" ", "");
598 let values = trimmed_value.split_once("=");
599 let parsed_query: String;
600 if let Some((first, second)) = values {
601 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
602 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
603 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
604 } else {
605 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table.column = table2.column2\")")
606 }
607
608 self.joins_clauses.push(format!("JOIN \"{}\" ON {}", table, parsed_query));
609 self
610 }
611
612 /// Internal helper for specific join types
613 fn join_generic(mut self, join_type: &str, table: &str, s_query: &str) -> Self {
614 let trimmed_value = s_query.replace(" ", "");
615 let values = trimmed_value.split_once("=");
616 let parsed_query: String;
617 if let Some((first, second)) = values {
618 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
619 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
620 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
621 } else {
622 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table.column = table2.column2\")")
623 }
624
625 self.joins_clauses.push(format!("{} JOIN \"{}\" ON {}", join_type, table, parsed_query));
626 self
627 }
628
629 /// Adds a LEFT JOIN clause.
630 ///
631 /// Performs a LEFT JOIN with another table. Returns all records from the left table,
632 /// and the matched records from the right table (or NULL if no match).
633 ///
634 /// # Arguments
635 ///
636 /// * `table` - The name of the table to join with
637 /// * `on` - The join condition (e.g., "users.id = posts.user_id")
638 ///
639 /// # Example
640 ///
641 /// ```rust,ignore
642 /// // Get all users and their posts (if any)
643 /// let users_with_posts = db.model::<User>()
644 /// .left_join("posts", "users.id = posts.user_id")
645 /// .scan()
646 /// .await?;
647 /// ```
648 pub fn left_join(self, table: &str, on: &str) -> Self {
649 self.join_generic("LEFT", table, on)
650 }
651
652 /// Adds a RIGHT JOIN clause.
653 ///
654 /// Performs a RIGHT JOIN with another table. Returns all records from the right table,
655 /// and the matched records from the left table (or NULL if no match).
656 ///
657 /// # Arguments
658 ///
659 /// * `table` - The name of the table to join with
660 /// * `on` - The join condition
661 ///
662 /// # Example
663 ///
664 /// ```rust,ignore
665 /// let posts_with_users = db.model::<Post>()
666 /// .right_join("users", "posts.user_id = users.id")
667 /// .scan()
668 /// .await?;
669 /// ```
670 pub fn right_join(self, table: &str, on: &str) -> Self {
671 self.join_generic("RIGHT", table, on)
672 }
673
674 /// Adds an INNER JOIN clause.
675 ///
676 /// Performs an INNER JOIN with another table. Returns records that have matching
677 /// values in both tables.
678 ///
679 /// # Arguments
680 ///
681 /// * `table` - The name of the table to join with
682 /// * `on` - The join condition
683 ///
684 /// # Example
685 ///
686 /// ```rust,ignore
687 /// // Get only users who have posts
688 /// let active_users = db.model::<User>()
689 /// .inner_join("posts", "users.id = posts.user_id")
690 /// .scan()
691 /// .await?;
692 /// ```
693 pub fn inner_join(self, table: &str, on: &str) -> Self {
694 self.join_generic("INNER", table, on)
695 }
696
697 /// Adds a FULL JOIN clause.
698 ///
699 /// Performs a FULL OUTER JOIN. Returns all records when there is a match in
700 /// either left or right table.
701 ///
702 /// # Arguments
703 ///
704 /// * `table` - The name of the table to join with
705 /// * `on` - The join condition
706 ///
707 /// # Note
708 ///
709 /// Support for FULL JOIN depends on the underlying database engine (e.g., SQLite
710 /// does not support FULL JOIN directly).
711 pub fn full_join(self, table: &str, on: &str) -> Self {
712 self.join_generic("FULL", table, on)
713 }
714
715 /// Marks the query to return DISTINCT results.
716 ///
717 /// Adds the `DISTINCT` keyword to the SELECT statement, ensuring that unique
718 /// rows are returned.
719 ///
720 /// # Example
721 ///
722 /// ```rust,ignore
723 /// // Get unique ages of users
724 /// let unique_ages: Vec<i32> = db.model::<User>()
725 /// .select("age")
726 /// .distinct()
727 /// .scan()
728 /// .await?;
729 /// ```
730 pub fn distinct(mut self) -> Self {
731 self.is_distinct = true;
732 self
733 }
734
735 /// Adds a GROUP BY clause to the query.
736 ///
737 /// Groups rows that have the same values into summary rows. Often used with
738 /// aggregate functions (COUNT, MAX, MIN, SUM, AVG).
739 ///
740 /// # Arguments
741 ///
742 /// * `columns` - Comma-separated list of columns to group by
743 ///
744 /// # Example
745 ///
746 /// ```rust,ignore
747 /// // Count users by age group
748 /// let stats: Vec<(i32, i64)> = db.model::<User>()
749 /// .select("age, COUNT(*)")
750 /// .group_by("age")
751 /// .scan()
752 /// .await?;
753 /// ```
754 pub fn group_by(mut self, columns: &str) -> Self {
755 self.group_by_clauses.push(columns.to_string());
756 self
757 }
758
759 /// Adds a HAVING clause to the query.
760 ///
761 /// Used to filter groups created by `group_by`. Similar to `filter` (WHERE),
762 /// but operates on grouped records and aggregate functions.
763 ///
764 /// # Arguments
765 ///
766 /// * `col` - The column or aggregate function to filter on
767 /// * `op` - Comparison operator
768 /// * `value` - Value to compare against
769 ///
770 /// # Example
771 ///
772 /// ```rust,ignore
773 /// // Get ages with more than 5 users
774 /// let popular_ages = db.model::<User>()
775 /// .select("age, COUNT(*)")
776 /// .group_by("age")
777 /// .having("COUNT(*)", ">", 5)
778 /// .scan()
779 /// .await?;
780 /// ```
781 pub fn having<V>(mut self, col: &'static str, op: &'static str, value: V) -> Self
782 where
783 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
784 {
785 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
786 query.push_str(" AND ");
787 query.push_str(col);
788 query.push(' ');
789 query.push_str(op);
790 query.push(' ');
791
792 match driver {
793 Drivers::Postgres => {
794 query.push_str(&format!("${}", arg_counter));
795 *arg_counter += 1;
796 }
797 _ => query.push('?'),
798 }
799 let _ = args.add(value.clone());
800 });
801
802 self.having_clauses.push(clause);
803 self
804 }
805
806 /// Returns the COUNT of rows matching the query.
807 ///
808 /// A convenience method that automatically sets `SELECT COUNT(*)` and returns
809 /// the result as an `i64`.
810 ///
811 /// # Returns
812 ///
813 /// * `Ok(i64)` - The count of rows
814 /// * `Err(sqlx::Error)` - Database error
815 ///
816 /// # Example
817 ///
818 /// ```rust,ignore
819 /// let user_count = db.model::<User>().count().await?;
820 /// ```
821 pub async fn count(mut self) -> Result<i64, sqlx::Error> {
822 self.select_columns = vec!["COUNT(*)".to_string()];
823 self.scalar::<i64>().await
824 }
825
826 /// Returns the SUM of the specified column.
827 ///
828 /// Calculates the sum of a numeric column.
829 ///
830 /// # Arguments
831 ///
832 /// * `column` - The column to sum
833 ///
834 /// # Example
835 ///
836 /// ```rust,ignore
837 /// let total_age: i64 = db.model::<User>().sum("age").await?;
838 /// ```
839 pub async fn sum<N>(mut self, column: &str) -> Result<N, sqlx::Error>
840 where
841 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
842 {
843 self.select_columns = vec![format!("SUM({})", column)];
844 self.scalar::<N>().await
845 }
846
847 /// Returns the AVG of the specified column.
848 ///
849 /// Calculates the average value of a numeric column.
850 ///
851 /// # Arguments
852 ///
853 /// * `column` - The column to average
854 ///
855 /// # Example
856 ///
857 /// ```rust,ignore
858 /// let avg_age: f64 = db.model::<User>().avg("age").await?;
859 /// ```
860 pub async fn avg<N>(mut self, column: &str) -> Result<N, sqlx::Error>
861 where
862 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
863 {
864 self.select_columns = vec![format!("AVG({})", column)];
865 self.scalar::<N>().await
866 }
867
868 /// Returns the MIN of the specified column.
869 ///
870 /// Finds the minimum value in a column.
871 ///
872 /// # Arguments
873 ///
874 /// * `column` - The column to check
875 ///
876 /// # Example
877 ///
878 /// ```rust,ignore
879 /// let min_age: i32 = db.model::<User>().min("age").await?;
880 /// ```
881 pub async fn min<N>(mut self, column: &str) -> Result<N, sqlx::Error>
882 where
883 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
884 {
885 self.select_columns = vec![format!("MIN({})", column)];
886 self.scalar::<N>().await
887 }
888
889 /// Returns the MAX of the specified column.
890 ///
891 /// Finds the maximum value in a column.
892 ///
893 /// # Arguments
894 ///
895 /// * `column` - The column to check
896 ///
897 /// # Example
898 ///
899 /// ```rust,ignore
900 /// let max_age: i32 = db.model::<User>().max("age").await?;
901 /// ```
902 pub async fn max<N>(mut self, column: &str) -> Result<N, sqlx::Error>
903 where
904 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
905 {
906 self.select_columns = vec![format!("MAX({})", column)];
907 self.scalar::<N>().await
908 }
909
910 /// Applies pagination with validation and limits.
911 ///
912 /// This is a convenience method that combines `limit()` and `offset()` with
913 /// built-in validation and maximum value enforcement for safer pagination.
914 ///
915 /// # Arguments
916 ///
917 /// * `max_value` - Maximum allowed items per page
918 /// * `default` - Default value if `value` exceeds `max_value`
919 /// * `page` - Zero-based page number
920 /// * `value` - Requested items per page
921 ///
922 /// # Returns
923 ///
924 /// * `Ok(Self)` - The updated QueryBuilder with pagination applied
925 /// * `Err(Error)` - If `value` is negative
926 ///
927 /// # Pagination Logic
928 ///
929 /// 1. Validates that `value` is non-negative
930 /// 2. If `value` > `max_value`, uses `default` instead
931 /// 3. Calculates offset as: `value * page`
932 /// 4. Sets limit to `value`
933 ///
934 /// # Example
935 ///
936 /// ```rust,ignore
937 /// // Page 0 with 10 items (page 1 in 1-indexed systems)
938 /// query.pagination(100, 20, 0, 10)? // LIMIT 10 OFFSET 0
939 ///
940 /// // Page 2 with 25 items (page 3 in 1-indexed systems)
941 /// query.pagination(100, 20, 2, 25)? // LIMIT 25 OFFSET 50
942 ///
943 /// // Request too many items, falls back to default
944 /// query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)
945 ///
946 /// // Error: negative value
947 /// query.pagination(100, 20, 0, -10)? // Returns Error
948 /// ```
949 pub fn pagination(mut self, max_value: usize, default: usize, page: usize, value: isize) -> Result<Self, Error> {
950 // Validate that value is non-negative
951 if value < 0 {
952 return Err(Error::InvalidArgument("value cannot be negative".into()));
953 }
954
955 let mut f_value = value as usize;
956
957 // Enforce maximum value limit
958 if f_value > max_value {
959 f_value = default;
960 }
961
962 // Apply offset and limit
963 self = self.offset(f_value * page);
964 self = self.limit(f_value);
965
966 Ok(self)
967 }
968
969 /// Selects specific columns to return.
970 ///
971 /// By default, queries use `SELECT *` to return all columns. This method
972 /// allows you to specify exactly which columns should be returned, which can
973 /// improve performance for tables with many or large columns.
974 ///
975 /// # Arguments
976 ///
977 /// * `columns` - Comma-separated list of column names to select
978 ///
979 /// # Example
980 ///
981 /// ```rust,ignore
982 /// // Select single column
983 /// query.select("id")
984 ///
985 /// // Select multiple columns
986 /// query.select("id, username, email")
987 ///
988 /// // Select with SQL functions
989 /// query.select("COUNT(*) as total")
990 ///
991 /// // Chain multiple select calls (all will be included)
992 /// query
993 /// .select("id, username")
994 /// .select("created_at")
995 /// ```
996 pub fn select(mut self, columns: &str) -> Self {
997 self.select_columns.push(columns.to_string().to_snake_case());
998 self
999 }
1000
1001 /// Excludes specific columns from the query results.
1002 ///
1003 /// This is the inverse of `select()`. Instead of specifying which columns to include,
1004 /// you specify which columns to exclude. All other columns will be returned.
1005 ///
1006 /// # Arguments
1007 ///
1008 /// * `columns` - Comma-separated list of column names to exclude
1009 ///
1010 /// # Priority
1011 ///
1012 /// If both `select()` and `omit()` are used, `select()` takes priority.
1013 ///
1014 /// # Example
1015 ///
1016 /// ```rust,ignore
1017 /// // Exclude password from results
1018 /// let user = db.model::<User>()
1019 /// .omit("password")
1020 /// .first()
1021 /// .await?;
1022 ///
1023 /// // Exclude multiple fields
1024 /// let user = db.model::<User>()
1025 /// .omit("password, secret_token")
1026 /// .first()
1027 /// .await?;
1028 ///
1029 /// // Using with generated field constants (autocomplete support)
1030 /// let user = db.model::<User>()
1031 /// .omit(user_fields::PASSWORD)
1032 /// .first()
1033 /// .await?;
1034 /// ```
1035 pub fn omit(mut self, columns: &str) -> Self {
1036 for col in columns.split(',') {
1037 self.omit_columns.push(col.trim().to_snake_case());
1038 }
1039 self
1040 }
1041
1042 /// Sets the query offset (pagination).
1043 ///
1044 /// Specifies the number of rows to skip before starting to return rows.
1045 /// Commonly used in combination with `limit()` for pagination.
1046 ///
1047 /// # Arguments
1048 ///
1049 /// * `offset` - Number of rows to skip
1050 ///
1051 /// # Example
1052 ///
1053 /// ```rust,ignore
1054 /// // Skip first 20 rows
1055 /// query.offset(20)
1056 ///
1057 /// // Pagination: page 3 with 10 items per page
1058 /// query.limit(10).offset(20) // Skip 2 pages = 20 items
1059 /// ```
1060 pub fn offset(mut self, offset: usize) -> Self {
1061 self.offset = Some(offset);
1062 self
1063 }
1064
1065 /// Sets the maximum number of records to return.
1066 ///
1067 /// Limits the number of rows returned by the query. Essential for pagination
1068 /// and preventing accidentally fetching large result sets.
1069 ///
1070 /// # Arguments
1071 ///
1072 /// * `limit` - Maximum number of rows to return
1073 ///
1074 /// # Example
1075 ///
1076 /// ```rust,ignore
1077 /// // Return at most 10 rows
1078 /// query.limit(10)
1079 ///
1080 /// // Pagination: 50 items per page
1081 /// query.limit(50).offset(page * 50)
1082 /// ```
1083 pub fn limit(mut self, limit: usize) -> Self {
1084 self.limit = Some(limit);
1085 self
1086 }
1087
1088 // ========================================================================
1089 // Insert Operation
1090 // ========================================================================
1091
1092 /// Inserts a new record into the database based on the model instance.
1093 ///
1094 /// This method serializes the model into a SQL INSERT statement with proper
1095 /// type handling for primitives, dates, UUIDs, and other supported types.
1096 ///
1097 /// # Type Binding Strategy
1098 ///
1099 /// The method uses string parsing as a temporary solution for type binding.
1100 /// Values are converted to strings via the model's `to_map()` method, then
1101 /// parsed back to their original types for proper SQL binding.
1102 ///
1103 /// # Supported Types for Insert
1104 ///
1105 /// - **Integers**: `i32`, `i64` (INTEGER, BIGINT)
1106 /// - **Boolean**: `bool` (BOOLEAN)
1107 /// - **Float**: `f64` (DOUBLE PRECISION)
1108 /// - **Text**: `String` (TEXT, VARCHAR)
1109 /// - **UUID**: `Uuid` (UUID) - All versions 1-7 supported
1110 /// - **DateTime**: `DateTime<Utc>` (TIMESTAMPTZ)
1111 /// - **NaiveDateTime**: (TIMESTAMP)
1112 /// - **NaiveDate**: (DATE)
1113 /// - **NaiveTime**: (TIME)
1114 ///
1115 /// # Arguments
1116 ///
1117 /// * `model` - Reference to the model instance to insert
1118 ///
1119 /// # Returns
1120 ///
1121 /// * `Ok(&Self)` - Reference to self for method chaining
1122 /// * `Err(sqlx::Error)` - Database error during insertion
1123 ///
1124 /// # Example
1125 ///
1126 /// ```rust,ignore
1127 /// use uuid::Uuid;
1128 /// use chrono::Utc;
1129 ///
1130 /// let new_user = User {
1131 /// id: Uuid::new_v4(),
1132 /// username: "john_doe".to_string(),
1133 /// email: "john@example.com".to_string(),
1134 /// age: 25,
1135 /// active: true,
1136 /// created_at: Utc::now(),
1137 /// };
1138 ///
1139 /// db.model::<User>().insert(&new_user).await?;
1140 /// ```
1141 pub fn insert<'b>(&'b mut self, model: &'b T) -> BoxFuture<'b, Result<(), sqlx::Error>> {
1142 Box::pin(async move {
1143 // Serialize model to a HashMap of column_name -> string_value
1144 let data_map = model.to_map();
1145
1146 // Early return if no data to insert
1147 if data_map.is_empty() {
1148 return Ok(());
1149 }
1150
1151 let table_name = self.table_name.to_snake_case();
1152 let columns_info = T::columns();
1153
1154 let mut target_columns = Vec::new();
1155 let mut bindings: Vec<(String, &str)> = Vec::new();
1156
1157 // Build column list and collect values with their SQL types
1158 for (col_name, value) in data_map {
1159 // Strip the "r#" prefix if present (for Rust keywords used as field names)
1160 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1161 target_columns.push(format!("\"{}\"", col_name_clean));
1162
1163 // Find the SQL type for this column
1164 let sql_type = columns_info.iter().find(|c| c.name == col_name).map(|c| c.sql_type).unwrap_or("TEXT");
1165
1166 bindings.push((value, sql_type));
1167 }
1168
1169 // Generate placeholders with proper type casting for PostgreSQL
1170 let placeholders: Vec<String> = bindings
1171 .iter()
1172 .enumerate()
1173 .map(|(i, (_, sql_type))| match self.driver {
1174 Drivers::Postgres => {
1175 let idx = i + 1;
1176 // PostgreSQL requires explicit type casting for some types
1177 if temporal::is_temporal_type(sql_type) {
1178 // Use temporal module for type casting
1179 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1180 } else {
1181 match *sql_type {
1182 "UUID" => format!("${}::UUID", idx),
1183 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
1184 _ => format!("${}", idx),
1185 }
1186 }
1187 }
1188 // MySQL and SQLite use simple ? placeholders
1189 _ => "?".to_string(),
1190 })
1191 .collect();
1192
1193 // Construct the INSERT query
1194 let query_str = format!(
1195 "INSERT INTO \"{}\" ({}) VALUES ({})",
1196 table_name,
1197 target_columns.join(", "),
1198 placeholders.join(", ")
1199 );
1200
1201 // If debug mode is enabled, log the generated SQL query before execution
1202 if self.debug_mode {
1203 log::debug!("SQL: {}", query_str);
1204 }
1205
1206 let mut query = sqlx::query::<sqlx::Any>(&query_str);
1207
1208 // Bind values using the optimized value_binding module
1209 // This provides type-safe binding with driver-specific optimizations
1210 for (val_str, sql_type) in bindings {
1211 // Create temporary AnyArguments to collect the bound value
1212 let mut temp_args = AnyArguments::default();
1213
1214 // Use the ValueBinder trait for type-safe binding
1215 if temp_args.bind_value(&val_str, sql_type, &self.driver).is_ok() {
1216 // For now, we need to convert back to individual bindings
1217 // This is a workaround until we can better integrate AnyArguments
1218 match sql_type {
1219 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
1220 if let Ok(val) = val_str.parse::<i32>() {
1221 query = query.bind(val);
1222 } else {
1223 query = query.bind(val_str);
1224 }
1225 }
1226 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
1227 if let Ok(val) = val_str.parse::<i64>() {
1228 query = query.bind(val);
1229 } else {
1230 query = query.bind(val_str);
1231 }
1232 }
1233 "BOOLEAN" | "BOOL" | "bool" => {
1234 if let Ok(val) = val_str.parse::<bool>() {
1235 query = query.bind(val);
1236 } else {
1237 query = query.bind(val_str);
1238 }
1239 }
1240 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
1241 if let Ok(val) = val_str.parse::<f64>() {
1242 query = query.bind(val);
1243 } else {
1244 query = query.bind(val_str);
1245 }
1246 }
1247 "UUID" => {
1248 if let Ok(val) = val_str.parse::<Uuid>() {
1249 query = query.bind(val.hyphenated().to_string());
1250 } else {
1251 query = query.bind(val_str);
1252 }
1253 }
1254 "TIMESTAMPTZ" | "DateTime" => {
1255 if let Ok(val) = temporal::parse_datetime_utc(&val_str) {
1256 let formatted = temporal::format_datetime_for_driver(&val, &self.driver);
1257 query = query.bind(formatted);
1258 } else {
1259 query = query.bind(val_str);
1260 }
1261 }
1262 "TIMESTAMP" | "NaiveDateTime" => {
1263 if let Ok(val) = temporal::parse_naive_datetime(&val_str) {
1264 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.driver);
1265 query = query.bind(formatted);
1266 } else {
1267 query = query.bind(val_str);
1268 }
1269 }
1270 "DATE" | "NaiveDate" => {
1271 if let Ok(val) = temporal::parse_naive_date(&val_str) {
1272 let formatted = val.format("%Y-%m-%d").to_string();
1273 query = query.bind(formatted);
1274 } else {
1275 query = query.bind(val_str);
1276 }
1277 }
1278 "TIME" | "NaiveTime" => {
1279 if let Ok(val) = temporal::parse_naive_time(&val_str) {
1280 let formatted = val.format("%H:%M:%S%.6f").to_string();
1281 query = query.bind(formatted);
1282 } else {
1283 query = query.bind(val_str);
1284 }
1285 }
1286 _ => {
1287 query = query.bind(val_str);
1288 }
1289 }
1290 } else {
1291 // Fallback: bind as string if type conversion fails
1292 query = query.bind(val_str);
1293 }
1294 }
1295
1296 // Execute the INSERT query
1297 query.execute(self.tx.executor()).await?;
1298 Ok(())
1299 })
1300 }
1301
1302 // ========================================================================
1303 // Query Execution Methods
1304 // ========================================================================
1305
1306 /// Returns the generated SQL string for debugging purposes.
1307 ///
1308 /// This method constructs the SQL query string without executing it.
1309 /// Useful for debugging and logging query construction. Note that this
1310 /// shows placeholders (?, $1, etc.) rather than actual bound values.
1311 ///
1312 /// # Returns
1313 ///
1314 /// A `String` containing the SQL query that would be executed
1315 ///
1316 /// # Example
1317 ///
1318 /// ```rust,ignore
1319 /// let query = db.model::<User>()
1320 /// .filter("age", ">=", 18)
1321 /// .order("created_at DESC")
1322 /// .limit(10);
1323 ///
1324 /// println!("SQL: {}", query.to_sql());
1325 /// // Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC
1326 /// ```
1327 pub fn to_sql(&self) -> String {
1328 let mut query = String::from("SELECT ");
1329
1330 if self.is_distinct {
1331 query.push_str("DISTINCT ");
1332 }
1333
1334 // Handle column selection
1335 if self.select_columns.is_empty() {
1336 query.push('*');
1337 } else {
1338 query.push_str(&self.select_columns.join(", "));
1339 }
1340
1341 query.push_str(" FROM \"");
1342 query.push_str(&self.table_name.to_snake_case());
1343 query.push_str("\" ");
1344
1345 if !self.joins_clauses.is_empty() {
1346 query.push_str(&self.joins_clauses.join(" "));
1347 }
1348
1349 query.push_str(" WHERE 1=1");
1350
1351 // Apply WHERE clauses with dummy arguments
1352 let mut dummy_args = AnyArguments::default();
1353 let mut dummy_counter = 1;
1354
1355 for clause in &self.where_clauses {
1356 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1357 }
1358
1359 // Apply GROUP BY
1360 if !self.group_by_clauses.is_empty() {
1361 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1362 }
1363
1364 // Apply HAVING
1365 if !self.having_clauses.is_empty() {
1366 query.push_str(" HAVING 1=1");
1367 for clause in &self.having_clauses {
1368 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1369 }
1370 }
1371
1372 // Apply ORDER BY if present
1373 if !self.order_clauses.is_empty() {
1374 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1375 }
1376
1377 query
1378 }
1379
1380 /// Generates the list of column selection SQL arguments.
1381 ///
1382 /// This helper function constructs the column list for the SELECT statement.
1383 /// It handles:
1384 /// 1. Mapping specific columns if `select_columns` is set.
1385 /// 2. Defaulting to all columns from the struct `R` if no columns are specified.
1386 /// 3. applying `to_json(...)` casting for temporal types when using `AnyImpl` structs,
1387 /// ensuring compatibility with the `FromAnyRow` deserialization logic.
1388 fn select_args_sql<R: AnyImpl>(&self) -> Vec<String> {
1389 let struct_cols = R::columns();
1390
1391 if !struct_cols.is_empty() {
1392 if !self.select_columns.is_empty() {
1393 let mut args = Vec::new();
1394 for col_info in struct_cols {
1395 let col_snake = col_info.column.to_snake_case();
1396 let sql_type = col_info.sql_type;
1397 if self.select_columns.contains(&col_snake) {
1398 if is_temporal_type(sql_type) && matches!(self.driver, Drivers::Postgres) {
1399 if !self.joins_clauses.is_empty() {
1400 args.push(format!(
1401 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
1402 self.table_name.to_snake_case(),
1403 col_snake,
1404 col_snake
1405 ));
1406 } else {
1407 args.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake));
1408 }
1409 } else if !self.joins_clauses.is_empty() {
1410 args.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col_snake));
1411 } else {
1412 args.push(format!("\"{}\"", col_snake));
1413 }
1414 }
1415 }
1416 return args;
1417 } else {
1418 // For omitted columns, return 'omited' as placeholder value
1419 return struct_cols
1420 .iter()
1421 .map(|c| {
1422 let col_snake = c.column.to_snake_case();
1423 let is_omitted = self.omit_columns.contains(&col_snake);
1424 let table_name =
1425 if !c.table.is_empty() { c.table.to_snake_case() } else { self.table_name.to_snake_case() };
1426
1427 if is_omitted {
1428 // Return type-appropriate placeholder based on sql_type
1429 let placeholder = match c.sql_type {
1430 // String types
1431 "TEXT" | "VARCHAR" | "CHAR" | "STRING" => "'omited'",
1432 // Date/Time types - use epoch timestamp
1433 "TIMESTAMP" | "TIMESTAMPTZ" | "TIMESTAMP WITH TIME ZONE" => "'1970-01-01T00:00:00Z'",
1434 "DATE" => "'1970-01-01'",
1435 "TIME" => "'00:00:00'",
1436 // Numeric types
1437 "INTEGER" | "INT" | "SMALLINT" | "BIGINT" | "INT4" | "INT8" => "0",
1438 "REAL" | "FLOAT" | "DOUBLE" | "FLOAT4" | "FLOAT8" | "DECIMAL" | "NUMERIC" => "0.0",
1439 // Boolean
1440 "BOOLEAN" | "BOOL" => "false",
1441 // UUID - nil UUID
1442 "UUID" => "'00000000-0000-0000-0000-000000000000'",
1443 // JSON types
1444 "JSON" | "JSONB" => "'{}'",
1445 // Default fallback for unknown types
1446 _ => "'omited'",
1447 };
1448 format!("{} AS \"{}__{}\"", placeholder, table_name, col_snake)
1449 } else if is_temporal_type(c.sql_type) && matches!(self.driver, Drivers::Postgres) {
1450 format!(
1451 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}__{}\"",
1452 table_name, col_snake, table_name, col_snake
1453 )
1454 } else {
1455 format!("\"{}\".\"{}\" AS \"{}__{}\"", table_name, col_snake, table_name, col_snake)
1456 }
1457 })
1458 .collect();
1459 }
1460 }
1461
1462 if !self.select_columns.is_empty() {
1463 return self
1464 .select_columns
1465 .iter()
1466 .map(|c| if c.contains('(') { c.clone() } else { format!("\"{}\"", c) })
1467 .collect();
1468 }
1469
1470 vec!["*".to_string()]
1471 }
1472
1473 /// Executes the query and returns a list of results.
1474 ///
1475 /// This method builds and executes a SELECT query with all accumulated filters,
1476 /// ordering, and pagination settings. It returns all matching rows as a vector.
1477 ///
1478 /// # Type Parameters
1479 ///
1480 /// * `R` - The result type. Must implement `FromRow` for deserialization from database rows.
1481 ///
1482 /// # Returns
1483 ///
1484 /// * `Ok(Vec<R>)` - Vector of results (empty if no matches)
1485 /// * `Err(sqlx::Error)` - Database error during query execution
1486 ///
1487 /// # Example
1488 ///
1489 /// ```rust,ignore
1490 /// // Get all adult users, ordered by age, limited to 10
1491 /// let users: Vec<User> = db.model::<User>()
1492 /// .filter("age", ">=", 18)
1493 /// .order("age DESC")
1494 /// .limit(10)
1495 /// .scan()
1496 /// .await?;
1497 ///
1498 /// // Get users by UUID
1499 /// let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
1500 /// let users: Vec<User> = db.model::<User>()
1501 /// .filter("id", "=", user_id)
1502 /// .scan()
1503 /// .await?;
1504 ///
1505 /// // Empty result is Ok
1506 /// let results: Vec<User> = db.model::<User>()
1507 /// .filter("age", ">", 200)
1508 /// .scan()
1509 /// .await?; // Returns empty Vec, not an error
1510 /// ```
1511 pub async fn scan<R>(mut self) -> Result<Vec<R>, sqlx::Error>
1512 where
1513 R: FromAnyRow + AnyImpl + Send + Unpin,
1514 {
1515 // Apply default soft delete filter if not disabled
1516 if !self.with_deleted {
1517 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1518 self = self.is_null(soft_delete_col);
1519 }
1520 }
1521
1522 // Build SELECT clause
1523 let mut query = String::from("SELECT ");
1524
1525 if self.is_distinct {
1526 query.push_str("DISTINCT ");
1527 }
1528
1529 query.push_str(&self.select_args_sql::<R>().join(", "));
1530
1531 // Build FROM clause
1532 query.push_str(" FROM \"");
1533 query.push_str(&self.table_name.to_snake_case());
1534 query.push_str("\" ");
1535 if !self.joins_clauses.is_empty() {
1536 query.push_str(&self.joins_clauses.join(" "));
1537 }
1538
1539 query.push_str(" WHERE 1=1");
1540
1541 // Apply WHERE clauses
1542 let mut args = AnyArguments::default();
1543 let mut arg_counter = 1;
1544
1545 for clause in &self.where_clauses {
1546 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1547 }
1548
1549 // Apply GROUP BY
1550 if !self.group_by_clauses.is_empty() {
1551 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1552 }
1553
1554 // Apply HAVING
1555 if !self.having_clauses.is_empty() {
1556 query.push_str(" HAVING 1=1");
1557 for clause in &self.having_clauses {
1558 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1559 }
1560 }
1561
1562 // Apply ORDER BY clauses
1563 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1564 if !self.order_clauses.is_empty() {
1565 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1566 }
1567
1568 // Apply LIMIT clause
1569 if let Some(limit) = self.limit {
1570 query.push_str(" LIMIT ");
1571 match self.driver {
1572 Drivers::Postgres => {
1573 query.push_str(&format!("${}", arg_counter));
1574 arg_counter += 1;
1575 }
1576 _ => query.push('?'),
1577 }
1578 let _ = args.add(limit as i64);
1579 }
1580
1581 // Apply OFFSET clause
1582 if let Some(offset) = self.offset {
1583 query.push_str(" OFFSET ");
1584 match self.driver {
1585 Drivers::Postgres => {
1586 query.push_str(&format!("${}", arg_counter));
1587 // arg_counter += 1; // Not needed as this is the last clause
1588 }
1589 _ => query.push('?'),
1590 }
1591 let _ = args.add(offset as i64);
1592 }
1593
1594 // Print SQL query to logs if debug mode is active
1595 if self.debug_mode {
1596 log::debug!("SQL: {}", query);
1597 }
1598
1599 // Execute query and fetch all results
1600 let rows = sqlx::query_with(&query, args).fetch_all(self.tx.executor()).await?;
1601
1602 rows.iter().map(|row| R::from_any_row(row)).collect()
1603 }
1604
1605 /// Executes the query and returns only the first result.
1606 ///
1607 /// This method automatically adds `LIMIT 1` and orders by the Primary Key
1608 /// (if available) to ensure consistent results. It's optimized for fetching
1609 /// a single row and will return an error if no rows match.
1610 ///
1611 /// # Type Parameters
1612 ///
1613 /// * `R` - The result type. Must implement `FromRow` for deserialization.
1614 ///
1615 /// # Returns
1616 ///
1617 /// * `Ok(R)` - The first matching row
1618 /// * `Err(sqlx::Error)` - No rows found or database error
1619 ///
1620 /// # Error Handling
1621 ///
1622 /// Returns `sqlx::Error::RowNotFound` if no rows match the query.
1623 /// Use `scan()` instead if you want an empty Vec rather than an error.
1624 ///
1625 /// # Example
1626 ///
1627 /// ```rust,ignore
1628 /// // Get a specific user by ID
1629 /// let user: User = db.model::<User>()
1630 /// .filter("id", "=", 1)
1631 /// .first()
1632 /// .await?;
1633 ///
1634 /// // Get user by UUID
1635 /// let user_id = Uuid::new_v4();
1636 /// let user: User = db.model::<User>()
1637 /// .filter("id", "=", user_id)
1638 /// .first()
1639 /// .await?;
1640 ///
1641 /// // Get the oldest user
1642 /// let oldest: User = db.model::<User>()
1643 /// .order("age DESC")
1644 /// .first()
1645 /// .await?;
1646 ///
1647 /// // Error handling
1648 /// match db.model::<User>().filter("id", "=", 999).first().await {
1649 /// Ok(user) => println!("Found: {:?}", user),
1650 /// Err(sqlx::Error::RowNotFound) => println!("User not found"),
1651 /// Err(e) => println!("Database error: {}", e),
1652 /// }
1653 /// ```
1654 pub async fn first<R>(mut self) -> Result<R, sqlx::Error>
1655 where
1656 R: FromAnyRow + AnyImpl + Send + Unpin,
1657 {
1658 // Apply default soft delete filter if not disabled
1659 if !self.with_deleted {
1660 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1661 self = self.is_null(soft_delete_col);
1662 }
1663 }
1664
1665 // Build SELECT clause
1666 let mut query = String::from("SELECT ");
1667
1668 if self.is_distinct {
1669 query.push_str("DISTINCT ");
1670 }
1671
1672 query.push_str(&self.select_args_sql::<R>().join(", "));
1673
1674 // Build FROM clause
1675 query.push_str(" FROM \"");
1676 query.push_str(&self.table_name.to_snake_case());
1677 query.push_str("\" ");
1678 if !self.joins_clauses.is_empty() {
1679 query.push_str(&self.joins_clauses.join(" "));
1680 }
1681
1682 query.push_str(" WHERE 1=1");
1683
1684 // Apply WHERE clauses
1685 let mut args = AnyArguments::default();
1686 let mut arg_counter = 1;
1687
1688 for clause in &self.where_clauses {
1689 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1690 }
1691
1692 // Apply GROUP BY
1693 if !self.group_by_clauses.is_empty() {
1694 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1695 }
1696
1697 // Apply HAVING
1698 if !self.having_clauses.is_empty() {
1699 query.push_str(" HAVING 1=1");
1700 for clause in &self.having_clauses {
1701 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1702 }
1703 }
1704
1705 // Find primary key column for consistent ordering
1706 let pk_column = T::columns()
1707 .iter()
1708 .find(|c| c.is_primary_key)
1709 .map(|c| c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case());
1710
1711 // Apply ORDER BY clauses
1712 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1713 if !self.order_clauses.is_empty() {
1714 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1715 } else if let Some(pk) = pk_column {
1716 // Fallback to PK ordering if no custom order is specified (ensures deterministic results)
1717 query.push_str(" ORDER BY ");
1718 query.push_str(&format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), pk));
1719 query.push_str(" ASC");
1720 }
1721
1722 // Always add LIMIT 1 for first() queries
1723 query.push_str(" LIMIT 1");
1724
1725 // Print SQL query to logs if debug mode is active
1726 log::debug!("SQL: {}", query);
1727
1728 // Execute query and fetch exactly one result
1729 let row = sqlx::query_with(&query, args).fetch_one(self.tx.executor()).await?;
1730 R::from_any_row(&row)
1731 }
1732
1733 /// Executes the query and returns a single scalar value.
1734 ///
1735 /// This method is useful for fetching single values like counts, max/min values,
1736 /// or specific columns without mapping to a struct or tuple.
1737 ///
1738 /// # Type Parameters
1739 ///
1740 /// * `O` - The output type. Must implement `Decode` and `Type`.
1741 ///
1742 /// # Example
1743 ///
1744 /// ```rust,ignore
1745 /// // Get count of users
1746 /// let count: i64 = db.model::<User>()
1747 /// .select("count(*)")
1748 /// .scalar()
1749 /// .await?;
1750 ///
1751 /// // Get specific field
1752 /// let username: String = db.model::<User>()
1753 /// .filter("id", "=", 1)
1754 /// .select("username")
1755 /// .scalar()
1756 /// .await?;
1757 /// ```
1758 pub async fn scalar<O>(mut self) -> Result<O, sqlx::Error>
1759 where
1760 O: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1761 {
1762 // Apply default soft delete filter if not disabled
1763 if !self.with_deleted {
1764 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1765 self = self.is_null(soft_delete_col);
1766 }
1767 }
1768
1769 // Build SELECT clause
1770 let mut query = String::from("SELECT ");
1771
1772 if self.is_distinct {
1773 query.push_str("DISTINCT ");
1774 }
1775
1776 if self.select_columns.is_empty() {
1777 return Err(sqlx::Error::ColumnNotFound("is not possible get data without column".to_string()));
1778 }
1779
1780 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
1781 for col in self.select_columns {
1782 if !self.joins_clauses.is_empty() {
1783 if let Some((table, column)) = col.split_once(".") {
1784 select_cols.push(format!("\"{}\".\"{}\"", table, column));
1785 } else {
1786 select_cols.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col));
1787 }
1788 continue;
1789 }
1790 select_cols.push(col);
1791 }
1792
1793 query.push_str(&select_cols.join(", "));
1794
1795 // Build FROM clause
1796 query.push_str(" FROM \"");
1797 query.push_str(&self.table_name.to_snake_case());
1798 query.push_str("\" ");
1799
1800 if !self.joins_clauses.is_empty() {
1801 query.push_str(&self.joins_clauses.join(" "));
1802 }
1803
1804 query.push_str(" WHERE 1=1");
1805
1806 // Apply WHERE clauses
1807 let mut args = AnyArguments::default();
1808 let mut arg_counter = 1;
1809
1810 for clause in &self.where_clauses {
1811 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1812 }
1813
1814 // Apply GROUP BY
1815 if !self.group_by_clauses.is_empty() {
1816 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1817 }
1818
1819 // Apply HAVING
1820 if !self.having_clauses.is_empty() {
1821 query.push_str(" HAVING 1=1");
1822 for clause in &self.having_clauses {
1823 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1824 }
1825 }
1826
1827 // Apply ORDER BY
1828 if !self.order_clauses.is_empty() {
1829 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1830 }
1831
1832 // Always add LIMIT 1 for scalar queries
1833 query.push_str(" LIMIT 1");
1834
1835 // Print SQL query to logs if debug mode is active
1836 if self.debug_mode {
1837 log::debug!("SQL: {}", query);
1838 }
1839
1840 // Execute query and fetch one row
1841 let row = sqlx::query_with::<_, _>(&query, args).fetch_one(self.tx.executor()).await?;
1842
1843 // Get the first column
1844 row.try_get::<O, _>(0)
1845 }
1846
1847 /// Updates a single column in the database.
1848 ///
1849 /// # Arguments
1850 ///
1851 /// * `col` - The column name to update
1852 /// * `value` - The new value
1853 ///
1854 /// # Returns
1855 ///
1856 /// * `Ok(u64)` - The number of rows affected
1857 pub fn update<'b, V>(&'b mut self, col: &str, value: V) -> BoxFuture<'b, Result<u64, sqlx::Error>>
1858 where
1859 V: ToString + Send + Sync,
1860 {
1861 let mut map = std::collections::HashMap::new();
1862 map.insert(col.to_string(), value.to_string());
1863 self.execute_update(map)
1864 }
1865
1866 /// Updates all columns based on the model instance.
1867 ///
1868 /// This method updates all active columns of the table with values from the provided model.
1869 ///
1870 /// # Arguments
1871 ///
1872 /// * `model` - The model instance containing new values
1873 ///
1874 /// # Returns
1875 ///
1876 /// * `Ok(u64)` - The number of rows affected
1877 pub fn updates<'b>(&'b mut self, model: &T) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
1878 self.execute_update(model.to_map())
1879 }
1880
1881 /// Updates columns based on a partial model (struct implementing AnyImpl).
1882 ///
1883 /// This allows updating a subset of columns using a custom struct.
1884 /// The struct must implement `AnyImpl` (usually via `#[derive(FromAnyRow)]`).
1885 ///
1886 /// # Arguments
1887 ///
1888 /// * `partial` - The partial model containing new values
1889 ///
1890 /// # Returns
1891 ///
1892 /// * `Ok(u64)` - The number of rows affected
1893 pub fn update_partial<'b, P: AnyImpl>(&'b mut self, partial: &P) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
1894 self.execute_update(partial.to_map())
1895 }
1896
1897 /// Internal helper to execute an UPDATE query from a map of values.
1898 fn execute_update<'b>(
1899 &'b mut self,
1900 data_map: std::collections::HashMap<String, String>,
1901 ) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
1902 // Apply default soft delete filter if not disabled
1903 if !self.with_deleted {
1904 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1905 let col_owned = soft_delete_col.to_string();
1906 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
1907 query.push_str(" AND ");
1908 query.push_str(&format!("\"{}\"", col_owned));
1909 query.push_str(" IS NULL");
1910 });
1911 self.where_clauses.push(clause);
1912 }
1913 }
1914
1915 Box::pin(async move {
1916 let table_name = self.table_name.to_snake_case();
1917 let mut query = format!("UPDATE \"{}\" SET ", table_name);
1918
1919 let mut bindings: Vec<(String, &str)> = Vec::new();
1920 let mut set_clauses = Vec::new();
1921
1922 // Maintain argument counter for PostgreSQL ($1, $2, ...)
1923 let mut arg_counter = 1;
1924
1925 // Build SET clause
1926 for (col_name, value) in data_map {
1927 // Strip the "r#" prefix if present
1928 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1929
1930 // Find the SQL type for this column from the Model metadata
1931 let sql_type = self
1932 .columns_info
1933 .iter()
1934 .find(|c| c.name == col_name || c.name == col_name_clean)
1935 .map(|c| c.sql_type)
1936 .unwrap_or("TEXT");
1937
1938 // Generate placeholder
1939 let placeholder = match self.driver {
1940 Drivers::Postgres => {
1941 let idx = arg_counter;
1942 arg_counter += 1;
1943
1944 if temporal::is_temporal_type(sql_type) {
1945 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1946 } else {
1947 match sql_type {
1948 "UUID" => format!("${}::UUID", idx),
1949 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
1950 _ => format!("${}", idx),
1951 }
1952 }
1953 }
1954 _ => "?".to_string(),
1955 };
1956
1957 set_clauses.push(format!("\"{}\" = {}", col_name_clean, placeholder));
1958 bindings.push((value, sql_type));
1959 }
1960
1961 // If no fields to update, return 0
1962 if set_clauses.is_empty() {
1963 return Ok(0);
1964 }
1965
1966 query.push_str(&set_clauses.join(", "));
1967
1968 // Build WHERE clause
1969 query.push_str(" WHERE 1=1");
1970
1971 let mut args = AnyArguments::default();
1972
1973 // Bind SET values
1974 for (val_str, sql_type) in bindings {
1975 if args.bind_value(&val_str, sql_type, &self.driver).is_err() {
1976 let _ = args.add(val_str);
1977 }
1978 }
1979
1980 // Apply WHERE clauses (appending to args and query)
1981 for clause in &self.where_clauses {
1982 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1983 }
1984
1985 // Print SQL query to logs if debug mode is active
1986 if self.debug_mode {
1987 log::debug!("SQL: {}", query);
1988 }
1989
1990 // Execute the UPDATE query
1991 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
1992
1993 Ok(result.rows_affected())
1994 })
1995 }
1996
1997 /// Executes a DELETE query based on the current filters.
1998 ///
1999 /// If the model has a `#[orm(soft_delete)]` column, this method performs
2000 /// an UPDATE setting the soft delete column to the current timestamp instead
2001 /// of physically deleting the record.
2002 ///
2003 /// For permanent deletion, use `hard_delete()`.
2004 ///
2005 /// # Returns
2006 ///
2007 /// * `Ok(u64)` - The number of rows deleted (or soft-deleted)
2008 /// * `Err(sqlx::Error)` - Database error
2009 pub async fn delete(mut self) -> Result<u64, sqlx::Error> {
2010 // Check for soft delete column
2011 let soft_delete_col = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name);
2012
2013 if let Some(col) = soft_delete_col {
2014 // Soft Delete: Update the column to current timestamp
2015 let table_name = self.table_name.to_snake_case();
2016 let mut query = format!("UPDATE \"{}\" SET \"{}\" = ", table_name, col);
2017
2018 match self.driver {
2019 Drivers::Postgres => query.push_str("NOW()"),
2020 Drivers::SQLite => query.push_str("strftime('%Y-%m-%dT%H:%M:%SZ', 'now')"),
2021 Drivers::MySQL => query.push_str("NOW()"),
2022 }
2023
2024 query.push_str(" WHERE 1=1");
2025
2026 let mut args = AnyArguments::default();
2027 let mut arg_counter = 1;
2028
2029 // Apply filters
2030 for clause in &self.where_clauses {
2031 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2032 }
2033
2034 // Print SQL query to logs if debug mode is active
2035 if self.debug_mode {
2036 log::debug!("SQL: {}", query);
2037 }
2038
2039 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2040 Ok(result.rows_affected())
2041 } else {
2042 // Standard Delete (no soft delete column)
2043 let mut query = String::from("DELETE FROM \"");
2044 query.push_str(&self.table_name.to_snake_case());
2045 query.push_str("\" WHERE 1=1");
2046
2047 let mut args = AnyArguments::default();
2048 let mut arg_counter = 1;
2049
2050 for clause in &self.where_clauses {
2051 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2052 }
2053
2054 // Print SQL query to logs if debug mode is active
2055 if self.debug_mode {
2056 log::debug!("SQL: {}", query);
2057 }
2058
2059 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2060 Ok(result.rows_affected())
2061 }
2062 }
2063
2064 /// Permanently removes records from the database.
2065 ///
2066 /// This method performs a physical DELETE, bypassing any soft delete logic.
2067 /// Use this when you need to permanently remove records.
2068 ///
2069 /// # Returns
2070 ///
2071 /// * `Ok(u64)` - The number of rows deleted
2072 /// * `Err(sqlx::Error)` - Database error
2073 ///
2074 /// # Example
2075 ///
2076 /// ```rust,ignore
2077 /// // Permanently delete soft-deleted records older than 30 days
2078 /// db.model::<User>()
2079 /// .with_deleted()
2080 /// .filter("deleted_at", "<", thirty_days_ago)
2081 /// .hard_delete()
2082 /// .await?;
2083 /// ```
2084 pub async fn hard_delete(mut self) -> Result<u64, sqlx::Error> {
2085 let mut query = String::from("DELETE FROM \"");
2086 query.push_str(&self.table_name.to_snake_case());
2087 query.push_str("\" WHERE 1=1");
2088
2089 let mut args = AnyArguments::default();
2090 let mut arg_counter = 1;
2091
2092 for clause in &self.where_clauses {
2093 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2094 }
2095
2096 // Print SQL query to logs if debug mode is active
2097 if self.debug_mode {
2098 log::debug!("SQL: {}", query);
2099 }
2100
2101 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2102 Ok(result.rows_affected())
2103 }
2104}