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 heck::ToSnakeCase;
51use sqlx::{
52 any::{AnyArguments, AnyRow},
53 Any, Arguments, Decode, Encode, FromRow, Row, Type,
54};
55use std::marker::PhantomData;
56use uuid::Uuid;
57
58// ============================================================================
59// Internal Crate Imports
60// ============================================================================
61
62use crate::{
63 database::{Connection, Drivers},
64 model::{ColumnInfo, Model},
65 temporal::{self, is_temporal_type},
66 value_binding::ValueBinder,
67 AnyImpl, Error,
68};
69
70// ============================================================================
71// Type Aliases
72// ============================================================================
73
74/// A type alias for filter closures that support manual SQL construction and argument binding.
75///
76/// Filter functions receive the following parameters:
77/// 1. `&mut String` - The SQL query buffer being built
78/// 2. `&mut AnyArguments` - The argument container for binding values
79/// 3. `&Drivers` - The current database driver (determines placeholder syntax)
80/// 4. `&mut usize` - The argument counter (for PostgreSQL `$n` placeholders)
81///
82/// ## Example
83///
84/// ```rust,ignore
85/// let custom_filter: FilterFn = Box::new(|query, args, driver, counter| {
86/// query.push_str(" AND age > ");
87/// match driver {
88/// Drivers::Postgres => {
89/// query.push_str(&format!("${}", counter));
90/// *counter += 1;
91/// }
92/// _ => query.push('?'),
93/// }
94/// args.add(18);
95/// });
96/// ```
97pub type FilterFn = Box<dyn Fn(&mut String, &mut AnyArguments<'_>, &Drivers, &mut usize) + Send + Sync>;
98
99// ============================================================================
100// QueryBuilder Struct
101// ============================================================================
102
103/// A fluent Query Builder for constructing SQL queries.
104///
105/// `QueryBuilder` provides a type-safe, ergonomic interface for building and executing
106/// SQL queries across different database backends. It supports filtering, ordering,
107/// pagination, and both SELECT and INSERT operations.
108///
109/// ## Type Parameter
110///
111/// * `'a` - Lifetime of the database reference (used for PhantomData)
112/// * `T` - The Model type this query operates on
113/// * `E` - The connection type (Database or Transaction)
114///
115/// ## Fields
116///
117/// * `db` - Reference to the database connection pool or transaction
118/// * `table_name` - Static string containing the table name
119/// * `columns_info` - Metadata about each column in the table
120/// * `columns` - List of column names in snake_case format
121/// * `select_columns` - Specific columns to select (empty = SELECT *)
122/// * `where_clauses` - List of filter functions to apply
123/// * `order_clauses` - List of ORDER BY clauses
124/// * `limit` - Maximum number of rows to return
125/// * `offset` - Number of rows to skip (for pagination)
126/// * `_marker` - PhantomData to bind the generic type T
127pub struct QueryBuilder<'a, T, E>
128where
129 E: Connection,
130{
131 /// Reference to the database connection pool
132 pub(crate) tx: E,
133
134 /// Name of the database table (in original case)
135 pub(crate) table_name: &'static str,
136
137 /// Metadata information about each column
138 pub(crate) columns_info: Vec<ColumnInfo>,
139
140 /// List of column names (in snake_case)
141 pub(crate) columns: Vec<String>,
142
143 /// Specific columns to select (empty means SELECT *)
144 pub(crate) select_columns: Vec<String>,
145
146 /// Collection of WHERE clause filter functions
147 pub(crate) where_clauses: Vec<FilterFn>,
148
149 /// Collection of ORDER BY clauses
150 pub(crate) order_clauses: Vec<String>,
151
152 /// Collection of JOIN clause to filter entry tables
153 pub(crate) joins_clauses: Vec<String>,
154
155 /// Maximum number of rows to return (LIMIT)
156 pub(crate) limit: Option<usize>,
157
158 /// Number of rows to skip (OFFSET)
159 pub(crate) offset: Option<usize>,
160
161 /// PhantomData to bind the generic type T
162 pub(crate) _marker: PhantomData<&'a T>,
163}
164
165// ============================================================================
166// QueryBuilder Implementation
167// ============================================================================
168
169impl<'a, T, E> QueryBuilder<'a, T, E>
170where
171 T: Model + Send + Sync + Unpin,
172 E: Connection,
173{
174 // ========================================================================
175 // Constructor
176 // ========================================================================
177
178 /// Creates a new QueryBuilder instance.
179 ///
180 /// This constructor is typically called internally via `db.model::<T>()`.
181 /// You rarely need to call this directly.
182 ///
183 /// # Arguments
184 ///
185 /// * `db` - Reference to the database connection
186 /// * `table_name` - Name of the table to query
187 /// * `columns_info` - Metadata about table columns
188 /// * `columns` - List of column names
189 ///
190 /// # Returns
191 ///
192 /// A new `QueryBuilder` instance ready for query construction
193 ///
194 /// # Example
195 ///
196 /// ```rust,ignore
197 /// // Usually called via db.model::<User>()
198 /// let query = db.model::<User>();
199 /// ```
200 pub fn new(tx: E, table_name: &'static str, columns_info: Vec<ColumnInfo>, columns: Vec<String>) -> Self {
201 Self {
202 tx,
203 table_name,
204 columns_info,
205 columns,
206 select_columns: Vec::new(),
207 where_clauses: Vec::new(),
208 order_clauses: Vec::new(),
209 joins_clauses: Vec::new(),
210 limit: None,
211 offset: None,
212 _marker: PhantomData,
213 }
214 }
215
216 // ========================================================================
217 // Query Building Methods
218 // ========================================================================
219
220 /// Adds a WHERE clause to the query.
221 ///
222 /// This method adds a filter condition to the query. Multiple filters can be chained
223 /// and will be combined with AND operators. The value is bound as a parameter to
224 /// prevent SQL injection.
225 ///
226 /// # Type Parameters
227 ///
228 /// * `V` - The type of the value to filter by. Must be encodable for SQL queries.
229 ///
230 /// # Arguments
231 ///
232 /// * `col` - The column name to filter on
233 /// * `op` - The comparison operator (e.g., "=", ">", "LIKE", "IN")
234 /// * `value` - The value to compare against
235 ///
236 /// # Supported Types
237 ///
238 /// - Primitives: `i32`, `i64`, `f64`, `bool`, `String`
239 /// - UUID: `Uuid` (all versions 1-7)
240 /// - Date/Time: `DateTime<Utc>`, `NaiveDateTime`, `NaiveDate`, `NaiveTime`
241 /// - Options: `Option<T>` for any supported type T
242 ///
243 /// # Example
244 ///
245 /// ```rust,ignore
246 /// // Filter by integer
247 /// query.filter("age", ">=", 18)
248 ///
249 /// // Filter by string
250 /// query.filter("username", "=", "john_doe")
251 ///
252 /// // Filter by UUID
253 /// let user_id = Uuid::new_v4();
254 /// query.filter("id", "=", user_id)
255 ///
256 /// // Filter with LIKE operator
257 /// query.filter("email", "LIKE", "%@example.com")
258 ///
259 /// // Chain multiple filters
260 /// query
261 /// .filter("age", ">=", 18)
262 /// .filter("active", "=", true)
263 /// .filter("role", "=", "admin")
264 /// ```
265 pub fn filter<V>(mut self, col: &'static str, op: &'static str, value: V) -> Self
266 where
267 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
268 {
269 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
270 query.push_str(" AND \"");
271 query.push_str(col);
272 query.push_str("\" ");
273 query.push_str(op);
274 query.push(' ');
275
276 // Handle different placeholder syntaxes based on database driver
277 match driver {
278 // PostgreSQL uses numbered placeholders: $1, $2, $3, ...
279 Drivers::Postgres => {
280 query.push_str(&format!("${}", arg_counter));
281 *arg_counter += 1;
282 }
283 // MySQL and SQLite use question mark placeholders: ?
284 _ => query.push('?'),
285 }
286
287 // Bind the value to the query
288 let _ = args.add(value.clone());
289 });
290
291 self.where_clauses.push(clause);
292 self
293 }
294
295 /// Adds an equality filter to the query.
296 ///
297 /// This is a convenience wrapper around `filter()` for simple equality checks.
298 /// It is equivalent to calling `filter(col, "=", value)`.
299 ///
300 /// # Type Parameters
301 ///
302 /// * `V` - The type of the value to compare against.
303 ///
304 /// # Arguments
305 ///
306 /// * `col` - The column name to filter on.
307 /// * `value` - The value to match.
308 ///
309 /// # Example
310 ///
311 /// ```rust,ignore
312 /// // Equivalent to filter("age", "=", 18)
313 /// query.equals("age", 18)
314 /// ```
315 pub fn equals<V>(self, col: &'static str, value: V) -> Self
316 where
317 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
318 {
319 self.filter(col, "=", value)
320 }
321
322 /// Adds an ORDER BY clause to the query.
323 ///
324 /// Specifies the sort order for the query results. Multiple order clauses
325 /// can be added and will be applied in the order they were added.
326 ///
327 /// # Arguments
328 ///
329 /// * `order` - The ORDER BY expression (e.g., "created_at DESC", "age ASC, name DESC")
330 ///
331 /// # Example
332 ///
333 /// ```rust,ignore
334 /// // Single column ascending (ASC is default)
335 /// query.order("age")
336 ///
337 /// // Single column descending
338 /// query.order("created_at DESC")
339 ///
340 /// // Multiple columns
341 /// query.order("age DESC, username ASC")
342 ///
343 /// // Chain multiple order clauses
344 /// query
345 /// .order("priority DESC")
346 /// .order("created_at ASC")
347 /// ```
348 pub fn order(mut self, order: &str) -> Self {
349 self.order_clauses.push(order.to_string());
350 self
351 }
352
353 /// Placeholder for eager loading relationships (preload).
354 ///
355 /// This method is reserved for future implementation of relationship preloading.
356 /// Currently, it returns `self` unchanged to maintain the fluent interface.
357 ///
358 /// # Future Implementation
359 ///
360 /// Will support eager loading of related models to avoid N+1 query problems:
361 ///
362 /// ```rust,ignore
363 /// // Future usage example
364 /// query.preload("posts").preload("comments")
365 /// ```
366 pub fn preload(self) -> Self {
367 // TODO: Implement relationship preloading
368 self
369 }
370
371 /// Placeholder for JOIN operations.
372 ///
373 /// This method is reserved for future implementation of SQL JOINs.
374 /// Currently, it returns `self` unchanged to maintain the fluent interface.
375 ///
376 /// # Future Implementation
377 ///
378 /// Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):
379 ///
380 /// ```rust,ignore
381 /// // Future usage example
382 /// query.join("posts", "users.id = posts.user_id")
383 /// ```
384 pub fn join(self) -> Self {
385 // TODO: Implement JOIN operations
386 self
387 }
388
389 /// Applies pagination with validation and limits.
390 ///
391 /// This is a convenience method that combines `limit()` and `offset()` with
392 /// built-in validation and maximum value enforcement for safer pagination.
393 ///
394 /// # Arguments
395 ///
396 /// * `max_value` - Maximum allowed items per page
397 /// * `default` - Default value if `value` exceeds `max_value`
398 /// * `page` - Zero-based page number
399 /// * `value` - Requested items per page
400 ///
401 /// # Returns
402 ///
403 /// * `Ok(Self)` - The updated QueryBuilder with pagination applied
404 /// * `Err(Error)` - If `value` is negative
405 ///
406 /// # Pagination Logic
407 ///
408 /// 1. Validates that `value` is non-negative
409 /// 2. If `value` > `max_value`, uses `default` instead
410 /// 3. Calculates offset as: `value * page`
411 /// 4. Sets limit to `value`
412 ///
413 /// # Example
414 ///
415 /// ```rust,ignore
416 /// // Page 0 with 10 items (page 1 in 1-indexed systems)
417 /// query.pagination(100, 20, 0, 10)? // LIMIT 10 OFFSET 0
418 ///
419 /// // Page 2 with 25 items (page 3 in 1-indexed systems)
420 /// query.pagination(100, 20, 2, 25)? // LIMIT 25 OFFSET 50
421 ///
422 /// // Request too many items, falls back to default
423 /// query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)
424 ///
425 /// // Error: negative value
426 /// query.pagination(100, 20, 0, -10)? // Returns Error
427 /// ```
428 pub fn pagination(mut self, max_value: usize, default: usize, page: usize, value: isize) -> Result<Self, Error> {
429 // Validate that value is non-negative
430 if value < 0 {
431 return Err(Error::InvalidArgument("value cannot be negative".into()));
432 }
433
434 let mut f_value = value as usize;
435
436 // Enforce maximum value limit
437 if f_value > max_value {
438 f_value = default;
439 }
440
441 // Apply offset and limit
442 self = self.offset(f_value * page);
443 self = self.limit(f_value);
444
445 Ok(self)
446 }
447
448 /// Selects specific columns to return.
449 ///
450 /// By default, queries use `SELECT *` to return all columns. This method
451 /// allows you to specify exactly which columns should be returned, which can
452 /// improve performance for tables with many or large columns.
453 ///
454 /// # Arguments
455 ///
456 /// * `columns` - Comma-separated list of column names to select
457 ///
458 /// # Example
459 ///
460 /// ```rust,ignore
461 /// // Select single column
462 /// query.select("id")
463 ///
464 /// // Select multiple columns
465 /// query.select("id, username, email")
466 ///
467 /// // Select with SQL functions
468 /// query.select("COUNT(*) as total")
469 ///
470 /// // Chain multiple select calls (all will be included)
471 /// query
472 /// .select("id, username")
473 /// .select("created_at")
474 /// ```
475 pub fn select(mut self, columns: &str) -> Self {
476 self.select_columns.push(columns.to_string().to_snake_case());
477 self
478 }
479
480 /// Sets the query offset (pagination).
481 ///
482 /// Specifies the number of rows to skip before starting to return rows.
483 /// Commonly used in combination with `limit()` for pagination.
484 ///
485 /// # Arguments
486 ///
487 /// * `offset` - Number of rows to skip
488 ///
489 /// # Example
490 ///
491 /// ```rust,ignore
492 /// // Skip first 20 rows
493 /// query.offset(20)
494 ///
495 /// // Pagination: page 3 with 10 items per page
496 /// query.limit(10).offset(20) // Skip 2 pages = 20 items
497 /// ```
498 pub fn offset(mut self, offset: usize) -> Self {
499 self.offset = Some(offset);
500 self
501 }
502
503 /// Sets the maximum number of records to return.
504 ///
505 /// Limits the number of rows returned by the query. Essential for pagination
506 /// and preventing accidentally fetching large result sets.
507 ///
508 /// # Arguments
509 ///
510 /// * `limit` - Maximum number of rows to return
511 ///
512 /// # Example
513 ///
514 /// ```rust,ignore
515 /// // Return at most 10 rows
516 /// query.limit(10)
517 ///
518 /// // Pagination: 50 items per page
519 /// query.limit(50).offset(page * 50)
520 /// ```
521 pub fn limit(mut self, limit: usize) -> Self {
522 self.limit = Some(limit);
523 self
524 }
525
526 // ========================================================================
527 // Insert Operation
528 // ========================================================================
529
530 /// Inserts a new record into the database based on the model instance.
531 ///
532 /// This method serializes the model into a SQL INSERT statement with proper
533 /// type handling for primitives, dates, UUIDs, and other supported types.
534 ///
535 /// # Type Binding Strategy
536 ///
537 /// The method uses string parsing as a temporary solution for type binding.
538 /// Values are converted to strings via the model's `to_map()` method, then
539 /// parsed back to their original types for proper SQL binding.
540 ///
541 /// # Supported Types for Insert
542 ///
543 /// - **Integers**: `i32`, `i64` (INTEGER, BIGINT)
544 /// - **Boolean**: `bool` (BOOLEAN)
545 /// - **Float**: `f64` (DOUBLE PRECISION)
546 /// - **Text**: `String` (TEXT, VARCHAR)
547 /// - **UUID**: `Uuid` (UUID) - All versions 1-7 supported
548 /// - **DateTime**: `DateTime<Utc>` (TIMESTAMPTZ)
549 /// - **NaiveDateTime**: (TIMESTAMP)
550 /// - **NaiveDate**: (DATE)
551 /// - **NaiveTime**: (TIME)
552 ///
553 /// # Arguments
554 ///
555 /// * `model` - Reference to the model instance to insert
556 ///
557 /// # Returns
558 ///
559 /// * `Ok(&Self)` - Reference to self for method chaining
560 /// * `Err(sqlx::Error)` - Database error during insertion
561 ///
562 /// # Example
563 ///
564 /// ```rust,ignore
565 /// use uuid::Uuid;
566 /// use chrono::Utc;
567 ///
568 /// let new_user = User {
569 /// id: Uuid::new_v4(),
570 /// username: "john_doe".to_string(),
571 /// email: "john@example.com".to_string(),
572 /// age: 25,
573 /// active: true,
574 /// created_at: Utc::now(),
575 /// };
576 ///
577 /// db.model::<User>().insert(&new_user).await?;
578 /// ```
579 pub async fn insert(&mut self, model: &T) -> Result<&Self, sqlx::Error> {
580 // Serialize model to a HashMap of column_name -> string_value
581 let data_map = model.to_map();
582
583 // Early return if no data to insert
584 if data_map.is_empty() {
585 return Ok(self);
586 }
587
588 let table_name = self.table_name.to_snake_case();
589 let columns_info = T::columns();
590
591 let mut target_columns = Vec::new();
592 let mut bindings: Vec<(String, &str)> = Vec::new();
593
594 // Build column list and collect values with their SQL types
595 for (col_name, value) in data_map {
596 // Strip the "r#" prefix if present (for Rust keywords used as field names)
597 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
598 target_columns.push(format!("\"{}\"", col_name_clean));
599
600 // Find the SQL type for this column
601 let sql_type = columns_info.iter().find(|c| c.name == col_name).map(|c| c.sql_type).unwrap_or("TEXT");
602
603 bindings.push((value, sql_type));
604 }
605
606 // Generate placeholders with proper type casting for PostgreSQL
607 let placeholders: Vec<String> = bindings
608 .iter()
609 .enumerate()
610 .map(|(i, (_, sql_type))| match self.tx.driver() {
611 Drivers::Postgres => {
612 let idx = i + 1;
613 // PostgreSQL requires explicit type casting for some types
614 if temporal::is_temporal_type(sql_type) {
615 // Use temporal module for type casting
616 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
617 } else {
618 match *sql_type {
619 "UUID" => format!("${}::UUID", idx),
620 _ => format!("${}", idx),
621 }
622 }
623 }
624 // MySQL and SQLite use simple ? placeholders
625 _ => "?".to_string(),
626 })
627 .collect();
628
629 // Construct the INSERT query
630 let query_str = format!(
631 "INSERT INTO \"{}\" ({}) VALUES ({})",
632 table_name,
633 target_columns.join(", "),
634 placeholders.join(", ")
635 );
636
637 // Debug: Uncomment to see generated SQL
638 // println!("{}", query_str);
639
640 let mut query = sqlx::query::<sqlx::Any>(&query_str);
641
642 // Bind values using the optimized value_binding module
643 // This provides type-safe binding with driver-specific optimizations
644 for (val_str, sql_type) in bindings {
645 // Create temporary AnyArguments to collect the bound value
646 let mut temp_args = AnyArguments::default();
647
648 // Use the ValueBinder trait for type-safe binding
649 if temp_args.bind_value(&val_str, sql_type, &self.tx.driver()).is_ok() {
650 // For now, we need to convert back to individual bindings
651 // This is a workaround until we can better integrate AnyArguments
652 match sql_type {
653 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
654 if let Ok(val) = val_str.parse::<i32>() {
655 query = query.bind(val);
656 } else {
657 query = query.bind(val_str);
658 }
659 }
660 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
661 if let Ok(val) = val_str.parse::<i64>() {
662 query = query.bind(val);
663 } else {
664 query = query.bind(val_str);
665 }
666 }
667 "BOOLEAN" | "BOOL" | "bool" => {
668 if let Ok(val) = val_str.parse::<bool>() {
669 query = query.bind(val);
670 } else {
671 query = query.bind(val_str);
672 }
673 }
674 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
675 if let Ok(val) = val_str.parse::<f64>() {
676 query = query.bind(val);
677 } else {
678 query = query.bind(val_str);
679 }
680 }
681 "UUID" => {
682 if let Ok(val) = val_str.parse::<Uuid>() {
683 query = query.bind(val.hyphenated().to_string());
684 } else {
685 query = query.bind(val_str);
686 }
687 }
688 "TIMESTAMPTZ" | "DateTime" => {
689 if let Ok(val) = temporal::parse_datetime_utc(&val_str) {
690 let formatted = temporal::format_datetime_for_driver(&val, &self.tx.driver());
691 query = query.bind(formatted);
692 } else {
693 query = query.bind(val_str);
694 }
695 }
696 "TIMESTAMP" | "NaiveDateTime" => {
697 if let Ok(val) = temporal::parse_naive_datetime(&val_str) {
698 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.tx.driver());
699 query = query.bind(formatted);
700 } else {
701 query = query.bind(val_str);
702 }
703 }
704 "DATE" | "NaiveDate" => {
705 if let Ok(val) = temporal::parse_naive_date(&val_str) {
706 let formatted = val.format("%Y-%m-%d").to_string();
707 query = query.bind(formatted);
708 } else {
709 query = query.bind(val_str);
710 }
711 }
712 "TIME" | "NaiveTime" => {
713 if let Ok(val) = temporal::parse_naive_time(&val_str) {
714 let formatted = val.format("%H:%M:%S%.6f").to_string();
715 query = query.bind(formatted);
716 } else {
717 query = query.bind(val_str);
718 }
719 }
720 _ => {
721 query = query.bind(val_str);
722 }
723 }
724 } else {
725 // Fallback: bind as string if type conversion fails
726 query = query.bind(val_str);
727 }
728 }
729
730 // Execute the INSERT query
731 query.execute(self.tx.executor()).await?;
732 Ok(self)
733 }
734
735 // ========================================================================
736 // Query Execution Methods
737 // ========================================================================
738
739 /// Returns the generated SQL string for debugging purposes.
740 ///
741 /// This method constructs the SQL query string without executing it.
742 /// Useful for debugging and logging query construction. Note that this
743 /// shows placeholders (?, $1, etc.) rather than actual bound values.
744 ///
745 /// # Returns
746 ///
747 /// A `String` containing the SQL query that would be executed
748 ///
749 /// # Example
750 ///
751 /// ```rust,ignore
752 /// let query = db.model::<User>()
753 /// .filter("age", ">=", 18)
754 /// .order("created_at DESC")
755 /// .limit(10);
756 ///
757 /// println!("SQL: {}", query.to_sql());
758 /// // Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC
759 /// ```
760 pub fn to_sql(&self) -> String {
761 let mut query = String::from("SELECT ");
762
763 // Handle column selection
764 if self.select_columns.is_empty() {
765 query.push('*');
766 } else {
767 query.push_str(&self.select_columns.join(", "));
768 }
769
770 query.push_str(" FROM \"");
771 query.push_str(&self.table_name.to_snake_case());
772 query.push_str("\" WHERE 1=1");
773
774 // Apply WHERE clauses with dummy arguments
775 let mut dummy_args = AnyArguments::default();
776 let mut dummy_counter = 1;
777
778 for clause in &self.where_clauses {
779 clause(&mut query, &mut dummy_args, &self.tx.driver(), &mut dummy_counter);
780 }
781
782 // Apply ORDER BY if present
783 if !self.order_clauses.is_empty() {
784 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
785 }
786
787 query
788 }
789
790 /// Generates the list of column selection SQL arguments.
791 ///
792 /// This helper function constructs the column list for the SELECT statement.
793 /// It handles:
794 /// 1. Mapping specific columns if `select_columns` is set.
795 /// 2. Defaulting to all columns from the struct `R` if no columns are specified.
796 /// 3. applying `to_json(...)` casting for temporal types when using `AnyImpl` structs,
797 /// ensuring compatibility with the `FromAnyRow` deserialization logic.
798 fn select_args_sql<R: AnyImpl>(&self) -> Vec<String> {
799 let struct_cols = R::columns();
800
801 if !struct_cols.is_empty() {
802 if !self.select_columns.is_empty() {
803 let mut args = Vec::new();
804 for col_info in struct_cols {
805 let col_snake = col_info.column.to_snake_case();
806 let sql_type = col_info.sql_type;
807 if self.select_columns.contains(&col_snake) {
808 if is_temporal_type(sql_type) {
809 args.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake));
810 } else {
811 args.push(format!("\"{}\"", col_snake));
812 }
813 }
814 }
815 return args;
816 } else {
817 return struct_cols
818 .iter()
819 .map(|c| {
820 let col_snake = c.column.to_snake_case();
821 if is_temporal_type(c.sql_type) {
822 format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake)
823 } else {
824 format!("\"{}\"", col_snake)
825 }
826 })
827 .collect();
828 }
829 }
830
831 if !self.select_columns.is_empty() {
832 return self
833 .select_columns
834 .iter()
835 .map(|c| if c.contains('(') { c.clone() } else { format!("\"{}\"", c) })
836 .collect();
837 }
838
839 vec!["*".to_string()]
840 }
841
842 /// Executes the query and returns a list of results.
843 ///
844 /// This method builds and executes a SELECT query with all accumulated filters,
845 /// ordering, and pagination settings. It returns all matching rows as a vector.
846 ///
847 /// # Type Parameters
848 ///
849 /// * `R` - The result type. Must implement `FromRow` for deserialization from database rows.
850 ///
851 /// # Returns
852 ///
853 /// * `Ok(Vec<R>)` - Vector of results (empty if no matches)
854 /// * `Err(sqlx::Error)` - Database error during query execution
855 ///
856 /// # Example
857 ///
858 /// ```rust,ignore
859 /// // Get all adult users, ordered by age, limited to 10
860 /// let users: Vec<User> = db.model::<User>()
861 /// .filter("age", ">=", 18)
862 /// .order("age DESC")
863 /// .limit(10)
864 /// .scan()
865 /// .await?;
866 ///
867 /// // Get users by UUID
868 /// let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
869 /// let users: Vec<User> = db.model::<User>()
870 /// .filter("id", "=", user_id)
871 /// .scan()
872 /// .await?;
873 ///
874 /// // Empty result is Ok
875 /// let results: Vec<User> = db.model::<User>()
876 /// .filter("age", ">", 200)
877 /// .scan()
878 /// .await?; // Returns empty Vec, not an error
879 /// ```
880 pub async fn scan<R>(mut self) -> Result<Vec<R>, sqlx::Error>
881 where
882 R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,
883 {
884 // Build SELECT clause
885 let mut query = String::from("SELECT ");
886 query.push_str(&self.select_args_sql::<R>().join(", "));
887
888 // Build FROM clause
889 query.push_str(" FROM \"");
890 query.push_str(&self.table_name.to_snake_case());
891 query.push_str("\" WHERE 1=1");
892
893 // Apply WHERE clauses
894 let mut args = AnyArguments::default();
895 let mut arg_counter = 1;
896
897 for clause in &self.where_clauses {
898 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
899 }
900
901 // Apply ORDER BY clauses
902 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
903 if !self.order_clauses.is_empty() {
904 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
905 }
906
907 // Apply LIMIT clause
908 if let Some(limit) = self.limit {
909 query.push_str(" LIMIT ");
910 match self.tx.driver() {
911 Drivers::Postgres => {
912 query.push_str(&format!("${}", arg_counter));
913 arg_counter += 1;
914 }
915 _ => query.push('?'),
916 }
917 let _ = args.add(limit as i64);
918 }
919
920 // Apply OFFSET clause
921 if let Some(offset) = self.offset {
922 query.push_str(" OFFSET ");
923 match self.tx.driver() {
924 Drivers::Postgres => {
925 query.push_str(&format!("${}", arg_counter));
926 // arg_counter += 1; // Not needed as this is the last clause
927 }
928 _ => query.push('?'),
929 }
930 let _ = args.add(offset as i64);
931 }
932
933 // Execute query and fetch all results
934 sqlx::query_as_with::<_, R, _>(&query, args).fetch_all(self.tx.executor()).await
935 }
936
937 /// Executes the query and returns only the first result.
938 ///
939 /// This method automatically adds `LIMIT 1` and orders by the Primary Key
940 /// (if available) to ensure consistent results. It's optimized for fetching
941 /// a single row and will return an error if no rows match.
942 ///
943 /// # Type Parameters
944 ///
945 /// * `R` - The result type. Must implement `FromRow` for deserialization.
946 ///
947 /// # Returns
948 ///
949 /// * `Ok(R)` - The first matching row
950 /// * `Err(sqlx::Error)` - No rows found or database error
951 ///
952 /// # Error Handling
953 ///
954 /// Returns `sqlx::Error::RowNotFound` if no rows match the query.
955 /// Use `scan()` instead if you want an empty Vec rather than an error.
956 ///
957 /// # Example
958 ///
959 /// ```rust,ignore
960 /// // Get a specific user by ID
961 /// let user: User = db.model::<User>()
962 /// .filter("id", "=", 1)
963 /// .first()
964 /// .await?;
965 ///
966 /// // Get user by UUID
967 /// let user_id = Uuid::new_v4();
968 /// let user: User = db.model::<User>()
969 /// .filter("id", "=", user_id)
970 /// .first()
971 /// .await?;
972 ///
973 /// // Get the oldest user
974 /// let oldest: User = db.model::<User>()
975 /// .order("age DESC")
976 /// .first()
977 /// .await?;
978 ///
979 /// // Error handling
980 /// match db.model::<User>().filter("id", "=", 999).first().await {
981 /// Ok(user) => println!("Found: {:?}", user),
982 /// Err(sqlx::Error::RowNotFound) => println!("User not found"),
983 /// Err(e) => println!("Database error: {}", e),
984 /// }
985 /// ```
986 pub async fn first<R>(mut self) -> Result<R, sqlx::Error>
987 where
988 R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,
989 {
990 // Build SELECT clause
991 let mut query = String::from("SELECT ");
992 query.push_str(&self.select_args_sql::<R>().join(", "));
993
994 // Build FROM clause
995 query.push_str(" FROM \"");
996 query.push_str(&self.table_name.to_snake_case());
997 query.push_str("\" WHERE 1=1");
998
999 // Apply WHERE clauses
1000 let mut args = AnyArguments::default();
1001 let mut arg_counter = 1;
1002
1003 for clause in &self.where_clauses {
1004 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1005 }
1006
1007 // Find primary key column for consistent ordering
1008 let pk_column = T::columns()
1009 .iter()
1010 .find(|c| c.is_primary_key)
1011 .map(|c| c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case());
1012
1013 // Apply ORDER BY clauses
1014 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1015 if !self.order_clauses.is_empty() {
1016 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1017 } else if let Some(pk) = pk_column {
1018 // Fallback to PK ordering if no custom order is specified (ensures deterministic results)
1019 query.push_str(" ORDER BY \"");
1020 query.push_str(&pk);
1021 query.push_str("\" ASC");
1022 }
1023
1024 // Always add LIMIT 1 for first() queries
1025 query.push_str(" LIMIT 1");
1026
1027 // Execute query and fetch exactly one result
1028 sqlx::query_as_with::<_, R, _>(&query, args).fetch_one(self.tx.executor()).await
1029 }
1030
1031 /// Executes the query and returns a single scalar value.
1032 ///
1033 /// This method is useful for fetching single values like counts, max/min values,
1034 /// or specific columns without mapping to a struct or tuple.
1035 ///
1036 /// # Type Parameters
1037 ///
1038 /// * `O` - The output type. Must implement `Decode` and `Type`.
1039 ///
1040 /// # Example
1041 ///
1042 /// ```rust,ignore
1043 /// // Get count of users
1044 /// let count: i64 = db.model::<User>()
1045 /// .select("count(*)")
1046 /// .scalar()
1047 /// .await?;
1048 ///
1049 /// // Get specific field
1050 /// let username: String = db.model::<User>()
1051 /// .filter("id", "=", 1)
1052 /// .select("username")
1053 /// .scalar()
1054 /// .await?;
1055 /// ```
1056 pub async fn scalar<O>(mut self) -> Result<O, sqlx::Error>
1057 where
1058 O: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1059 {
1060 // Build SELECT clause
1061 let mut query = String::from("SELECT ");
1062
1063 if self.select_columns.is_empty() {
1064 return Err(sqlx::Error::ColumnNotFound("is not possible get data without column".to_string()));
1065 }
1066
1067 query.push_str(&self.select_columns.join(", "));
1068
1069 // Build FROM clause
1070 query.push_str(" FROM \"");
1071 query.push_str(&self.table_name.to_snake_case());
1072 query.push_str("\" WHERE 1=1");
1073
1074 // Apply WHERE clauses
1075 let mut args = AnyArguments::default();
1076 let mut arg_counter = 1;
1077
1078 for clause in &self.where_clauses {
1079 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1080 }
1081
1082 // Apply ORDER BY
1083 if !self.order_clauses.is_empty() {
1084 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1085 }
1086
1087 // Always add LIMIT 1 for scalar queries
1088 query.push_str(" LIMIT 1");
1089
1090 // Execute query and fetch one row
1091 let row = sqlx::query_with::<_, _>(&query, args).fetch_one(self.tx.executor()).await?;
1092
1093 // Get the first column
1094 row.try_get::<O, _>(0)
1095 }
1096
1097 /// Updates a single column in the database.
1098 ///
1099 /// # Arguments
1100 ///
1101 /// * `col` - The column name to update
1102 /// * `value` - The new value
1103 ///
1104 /// # Returns
1105 ///
1106 /// * `Ok(u64)` - The number of rows affected
1107 pub async fn update<V>(&mut self, col: &str, value: V) -> Result<u64, sqlx::Error>
1108 where
1109 V: ToString + Send + Sync,
1110 {
1111 let mut map = std::collections::HashMap::new();
1112 map.insert(col.to_string(), value.to_string());
1113 self.execute_update(map).await
1114 }
1115
1116 /// Updates all columns based on the model instance.
1117 ///
1118 /// This method updates all active columns of the table with values from the provided model.
1119 ///
1120 /// # Arguments
1121 ///
1122 /// * `model` - The model instance containing new values
1123 ///
1124 /// # Returns
1125 ///
1126 /// * `Ok(u64)` - The number of rows affected
1127 pub async fn updates(&mut self, model: &T) -> Result<u64, sqlx::Error> {
1128 self.execute_update(model.to_map()).await
1129 }
1130
1131 /// Updates columns based on a partial model (struct implementing AnyImpl).
1132 ///
1133 /// This allows updating a subset of columns using a custom struct.
1134 /// The struct must implement `AnyImpl` (usually via `#[derive(FromAnyRow)]`).
1135 ///
1136 /// # Arguments
1137 ///
1138 /// * `partial` - The partial model containing new values
1139 ///
1140 /// # Returns
1141 ///
1142 /// * `Ok(u64)` - The number of rows affected
1143 pub async fn update_partial<P: AnyImpl>(&mut self, partial: &P) -> Result<u64, sqlx::Error> {
1144 self.execute_update(partial.to_map()).await
1145 }
1146
1147 /// Internal helper to execute an UPDATE query from a map of values.
1148 async fn execute_update(
1149 &mut self,
1150 data_map: std::collections::HashMap<String, String>,
1151 ) -> Result<u64, sqlx::Error> {
1152 let table_name = self.table_name.to_snake_case();
1153 let mut query = format!("UPDATE \"{}\" SET ", table_name);
1154
1155 let mut bindings: Vec<(String, &str)> = Vec::new();
1156 let mut set_clauses = Vec::new();
1157
1158 // Maintain argument counter for PostgreSQL ($1, $2, ...)
1159 let mut arg_counter = 1;
1160
1161 // Build SET clause
1162 for (col_name, value) in data_map {
1163 // Strip the "r#" prefix if present
1164 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1165
1166 // Find the SQL type for this column from the Model metadata
1167 let sql_type = self
1168 .columns_info
1169 .iter()
1170 .find(|c| c.name == col_name || c.name == col_name_clean)
1171 .map(|c| c.sql_type)
1172 .unwrap_or("TEXT");
1173
1174 // Generate placeholder
1175 let placeholder = match self.tx.driver() {
1176 Drivers::Postgres => {
1177 let idx = arg_counter;
1178 arg_counter += 1;
1179
1180 if temporal::is_temporal_type(sql_type) {
1181 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1182 } else {
1183 match sql_type {
1184 "UUID" => format!("${}::UUID", idx),
1185 _ => format!("${}", idx),
1186 }
1187 }
1188 }
1189 _ => "?".to_string(),
1190 };
1191
1192 set_clauses.push(format!("\"{}\" = {}", col_name_clean, placeholder));
1193 bindings.push((value, sql_type));
1194 }
1195
1196 // If no fields to update, return 0
1197 if set_clauses.is_empty() {
1198 return Ok(0);
1199 }
1200
1201 query.push_str(&set_clauses.join(", "));
1202
1203 // Build WHERE clause
1204 query.push_str(" WHERE 1=1");
1205
1206 let mut args = AnyArguments::default();
1207
1208 // Bind SET values
1209 for (val_str, sql_type) in bindings {
1210 if args
1211 .bind_value(&val_str, sql_type, &self.tx.driver())
1212 .is_err()
1213 {
1214 let _ = args.add(val_str);
1215 }
1216 }
1217
1218 // Apply WHERE clauses (appending to args and query)
1219 for clause in &self.where_clauses {
1220 clause(
1221 &mut query,
1222 &mut args,
1223 &self.tx.driver(),
1224 &mut arg_counter,
1225 );
1226 }
1227
1228 // Execute the UPDATE query
1229 let result = sqlx::query_with(&query, args)
1230 .execute(self.tx.executor())
1231 .await?;
1232
1233 Ok(result.rows_affected())
1234 }
1235
1236 /// Executes a DELETE query based on the current filters.
1237 ///
1238 /// # Returns
1239 ///
1240 /// * `Ok(u64)` - The number of rows deleted
1241 /// * `Err(sqlx::Error)` - Database error
1242 pub async fn delete(mut self) -> Result<u64, sqlx::Error> {
1243 let mut query = String::from("DELETE FROM \"");
1244 query.push_str(&self.table_name.to_snake_case());
1245 query.push_str("\" WHERE 1=1");
1246
1247 let mut args = AnyArguments::default();
1248 let mut arg_counter = 1;
1249
1250 for clause in &self.where_clauses {
1251 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1252 }
1253
1254 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
1255 Ok(result.rows_affected())
1256 }
1257}