libsql_orm/
query.rs

1//! Query building and execution for libsql-orm
2//!
3//! This module provides a fluent query builder for constructing complex SQL queries
4//! with type safety and parameter binding. It supports SELECT, INSERT, UPDATE, DELETE
5//! operations with joins, filtering, sorting, grouping, and aggregation.
6//!
7//! # Basic Usage
8//!
9//! ```rust
10//! use libsql_orm::{QueryBuilder, FilterOperator, Sort, SortOrder};
11//!
12//! let query = QueryBuilder::new("users")
13//!     .select(vec!["id", "name", "email"])
14//!     .r#where(FilterOperator::Eq("is_active".to_string(), Value::Boolean(true)))
15//!     .order_by(Sort::new("name", SortOrder::Asc))
16//!     .limit(10);
17//!
18//! let (sql, params) = query.build()?;
19//! ```
20//!
21//! # Complex Queries
22//!
23//! ```rust
24//! use libsql_orm::{QueryBuilder, JoinType, FilterOperator, Aggregate};
25//!
26//! let complex_query = QueryBuilder::new("orders")
27//!     .select(vec!["orders.id", "users.name", "products.title"])
28//!     .join(JoinType::Inner, "users", "users.id = orders.user_id")
29//!     .join(JoinType::Inner, "products", "products.id = orders.product_id")
30//!     .r#where(FilterOperator::Gte("orders.created_at".to_string(), Value::Text("2024-01-01".to_string())))
31//!     .group_by(vec!["users.id"])
32//!     .aggregate(Aggregate::Count, "orders.id", Some("order_count"))
33//!     .order_by(Sort::desc("order_count"));
34//!
35//! let results = complex_query.execute::<OrderWithUser>(&db).await?;
36//! ```
37
38use crate::filters::FilterValue;
39use crate::{
40    Aggregate, Database, FilterOperator, Operator, PaginatedResult, Pagination, Result, Sort, Value,
41};
42use std::collections::HashMap;
43
44/// Query result wrapper
45///
46/// Contains query results with optional total count for pagination support.
47///
48/// # Examples
49///
50/// ```rust
51/// use libsql_orm::QueryResult;
52///
53/// let result = QueryResult::new(vec!["item1", "item2"]);
54/// let result_with_total = QueryResult::with_total(vec!["item1", "item2"], 100);
55/// ```
56pub struct QueryResult<T> {
57    pub data: Vec<T>,
58    pub total: Option<u64>,
59}
60
61impl<T> QueryResult<T> {
62    pub fn new(data: Vec<T>) -> Self {
63        Self { data, total: None }
64    }
65
66    pub fn with_total(data: Vec<T>, total: u64) -> Self {
67        Self {
68            data,
69            total: Some(total),
70        }
71    }
72}
73
74/// SQL query builder for complex queries
75///
76/// Provides a fluent interface for building SQL queries with support for:
77/// - Column selection and table joins
78/// - WHERE clauses with complex filtering
79/// - GROUP BY and HAVING clauses
80/// - ORDER BY with multiple sort criteria
81/// - LIMIT and OFFSET for pagination
82/// - Aggregate functions (COUNT, SUM, AVG, etc.)
83/// - DISTINCT queries
84///
85/// # Examples
86///
87/// ```rust
88/// use libsql_orm::{QueryBuilder, FilterOperator, Sort, SortOrder, JoinType};
89///
90/// // Basic query
91/// let query = QueryBuilder::new("users")
92///     .select(vec!["id", "name", "email"])
93///     .r#where(FilterOperator::Eq("is_active".to_string(), Value::Boolean(true)))
94///     .order_by(Sort::new("name", SortOrder::Asc))
95///     .limit(10);
96///
97/// // Query with joins
98/// let joined_query = QueryBuilder::new("posts")
99///     .select(vec!["posts.title", "users.name"])
100///     .join(JoinType::Inner, "users", "users.id = posts.user_id")
101///     .r#where(FilterOperator::Eq("posts.published".to_string(), Value::Boolean(true)));
102///
103/// // Aggregate query
104/// let agg_query = QueryBuilder::new("orders")
105///     .aggregate(Aggregate::Sum, "amount", Some("total_amount"))
106///     .group_by(vec!["user_id"])
107///     .having(FilterOperator::Gt("total_amount".to_string(), Value::Real(1000.0)));
108/// ```
109pub struct QueryBuilder {
110    table: String,
111    select_columns: Vec<String>,
112    joins: Vec<JoinClause>,
113    where_clauses: Vec<FilterOperator>,
114    group_by: Vec<String>,
115    having: Vec<FilterOperator>,
116    order_by: Vec<Sort>,
117    limit: Option<u32>,
118    offset: Option<u32>,
119    distinct: bool,
120    aggregate: Option<AggregateClause>,
121}
122
123/// Join clause for complex queries
124struct JoinClause {
125    join_type: crate::JoinType,
126    table: String,
127    alias: Option<String>,
128    condition: String,
129}
130
131/// Aggregate clause for aggregation queries
132struct AggregateClause {
133    function: Aggregate,
134    column: String,
135    alias: Option<String>,
136}
137
138impl QueryBuilder {
139    /// Create a new query builder
140    pub fn new(table: impl Into<String>) -> Self {
141        Self {
142            table: table.into(),
143            select_columns: vec!["*".to_string()],
144            joins: Vec::new(),
145            where_clauses: Vec::new(),
146            group_by: Vec::new(),
147            having: Vec::new(),
148            order_by: Vec::new(),
149            limit: None,
150            offset: None,
151            distinct: false,
152            aggregate: None,
153        }
154    }
155
156    /// Select specific columns
157    pub fn select(mut self, columns: Vec<impl Into<String>>) -> Self {
158        self.select_columns = columns.into_iter().map(|c| c.into()).collect();
159        self
160    }
161
162    /// Add a join clause
163    pub fn join(
164        mut self,
165        join_type: crate::JoinType,
166        table: impl Into<String>,
167        condition: impl Into<String>,
168    ) -> Self {
169        self.joins.push(JoinClause {
170            join_type,
171            table: table.into(),
172            alias: None,
173            condition: condition.into(),
174        });
175        self
176    }
177
178    /// Add a join clause with alias
179    pub fn join_as(
180        mut self,
181        join_type: crate::JoinType,
182        table: impl Into<String>,
183        alias: impl Into<String>,
184        condition: impl Into<String>,
185    ) -> Self {
186        self.joins.push(JoinClause {
187            join_type,
188            table: table.into(),
189            alias: Some(alias.into()),
190            condition: condition.into(),
191        });
192        self
193    }
194
195    /// Add a where clause
196    pub fn r#where(mut self, filter: FilterOperator) -> Self {
197        self.where_clauses.push(filter);
198        self
199    }
200
201    /// Add a group by clause
202    pub fn group_by(mut self, columns: Vec<impl Into<String>>) -> Self {
203        self.group_by = columns.into_iter().map(|c| c.into()).collect();
204        self
205    }
206
207    /// Add a having clause
208    pub fn having(mut self, filter: FilterOperator) -> Self {
209        self.having.push(filter);
210        self
211    }
212
213    /// Add an order by clause
214    pub fn order_by(mut self, sort: Sort) -> Self {
215        self.order_by.push(sort);
216        self
217    }
218
219    /// Add multiple order by clauses
220    pub fn order_by_multiple(mut self, sorts: Vec<Sort>) -> Self {
221        self.order_by.extend(sorts);
222        self
223    }
224
225    /// Set limit
226    pub fn limit(mut self, limit: u32) -> Self {
227        self.limit = Some(limit);
228        self
229    }
230
231    /// Set offset
232    pub fn offset(mut self, offset: u32) -> Self {
233        self.offset = Some(offset);
234        self
235    }
236
237    /// Set distinct
238    pub fn distinct(mut self, distinct: bool) -> Self {
239        self.distinct = distinct;
240        self
241    }
242
243    /// Set aggregate function
244    pub fn aggregate(
245        mut self,
246        function: Aggregate,
247        column: impl Into<String>,
248        alias: Option<impl Into<String>>,
249    ) -> Self {
250        self.aggregate = Some(AggregateClause {
251            function,
252            column: column.into(),
253            alias: alias.map(|a| a.into()),
254        });
255        self
256    }
257
258    /// Select all columns
259    pub fn select_all(mut self) -> Self {
260        self.select_columns = vec!["*".to_string()];
261        self
262    }
263
264    /// Select specific columns
265    pub fn select_columns(mut self, columns: &[&str]) -> Self {
266        self.select_columns = columns.iter().map(|&c| c.to_string()).collect();
267        self
268    }
269
270    /// Select a single column
271    pub fn select_column(mut self, column: &str) -> Self {
272        self.select_columns = vec![column.to_string()];
273        self
274    }
275
276    /// Select count
277    pub fn select_count(mut self) -> Self {
278        self.select_columns = vec!["COUNT(*)".to_string()];
279        self
280    }
281
282    /// Select aggregate
283    pub fn select_aggregate(mut self, aggregate: &str) -> Self {
284        self.select_columns = vec![aggregate.to_string()];
285        self
286    }
287
288    /// Select distinct
289    pub fn select_distinct(mut self, column: &str) -> Self {
290        self.select_columns = vec![column.to_string()];
291        self.distinct = true;
292        self
293    }
294
295    /// Add where condition
296    pub fn where_condition(
297        mut self,
298        condition: &str,
299        _params: impl Into<Vec<libsql::Value>>,
300    ) -> Self {
301        // This is a simplified implementation - in a real implementation you'd parse the condition
302        self.where_clauses
303            .push(FilterOperator::Custom(condition.to_string()));
304        self
305    }
306
307    /// Add search
308    pub fn search(mut self, field: &str, query: &str) -> Self {
309        let condition = format!("{field} LIKE '%{query}%'");
310        self.where_clauses.push(FilterOperator::Custom(condition));
311        self
312    }
313
314    /// Add filter
315    pub fn with_filter(mut self, filter: crate::Filter) -> Self {
316        // Convert Filter to FilterOperator::Single
317        self.where_clauses.push(FilterOperator::Single(filter));
318        self
319    }
320
321    /// Add filters
322    pub fn with_filters(mut self, filters: Vec<crate::Filter>) -> Self {
323        for filter in filters {
324            self = self.with_filter(filter);
325        }
326        self
327    }
328
329    /// Add sorts
330    pub fn with_sorts(mut self, sorts: Vec<crate::Sort>) -> Self {
331        for sort in sorts {
332            self = self.order_by(sort);
333        }
334        self
335    }
336
337    /// Add having condition
338    pub fn having_condition(
339        mut self,
340        condition: &str,
341        _params: impl Into<Vec<libsql::Value>>,
342    ) -> Self {
343        // This is a simplified implementation
344        self.having
345            .push(FilterOperator::Custom(condition.to_string()));
346        self
347    }
348
349    /// Add where in clause
350    pub fn where_in(mut self, field: &str, subquery: QueryBuilder) -> Self {
351        let (subquery_sql, _) = subquery.build().unwrap_or_default();
352        let condition = format!("{field} IN ({subquery_sql})");
353        self.where_clauses.push(FilterOperator::Custom(condition));
354        self
355    }
356
357    /// Execute count query
358    pub async fn execute_count(&self, db: &Database) -> Result<u64> {
359        let (sql, params) = self.build_count()?;
360        let mut rows = db.query(&sql, params).await?;
361
362        if let Some(row) = rows.next().await? {
363            row.get_value(0)
364                .ok()
365                .and_then(|v| match v {
366                    libsql::Value::Integer(i) => Some(i as u64),
367                    _ => None,
368                })
369                .ok_or_else(|| crate::Error::Query("Failed to get count".to_string()))
370        } else {
371            Err(crate::Error::Query("No count result".to_string()))
372        }
373    }
374
375    /// Execute aggregate query
376    pub async fn execute_aggregate(&self, db: &Database) -> Result<Vec<libsql::Row>> {
377        let (sql, params) = self.build()?;
378        let mut rows = db.query(&sql, params).await?;
379        let mut results = Vec::new();
380        while let Some(row) = rows.next().await? {
381            results.push(row);
382        }
383        Ok(results)
384    }
385
386    /// Build the SQL query
387    pub fn build(&self) -> Result<(String, Vec<libsql::Value>)> {
388        let mut sql = String::new();
389        let mut params = Vec::new();
390
391        // SELECT clause
392        sql.push_str("SELECT ");
393        if self.distinct {
394            sql.push_str("DISTINCT ");
395        }
396
397        if let Some(agg) = &self.aggregate {
398            sql.push_str(&format!("{}({})", agg.function, agg.column));
399            if let Some(alias) = &agg.alias {
400                sql.push_str(&format!(" AS {alias}"));
401            }
402        } else {
403            sql.push_str(&self.select_columns.join(", "));
404        }
405
406        // FROM clause
407        sql.push_str(&format!(" FROM {}", self.table));
408
409        // JOIN clauses
410        for join in &self.joins {
411            sql.push_str(&format!(" {} {}", join.join_type, join.table));
412            if let Some(alias) = &join.alias {
413                sql.push_str(&format!(" AS {alias}"));
414            }
415            sql.push_str(&format!(" ON {}", join.condition));
416        }
417
418        // WHERE clause
419        if !self.where_clauses.is_empty() {
420            sql.push_str(" WHERE ");
421            let (where_sql, where_params) = self.build_where_clause(&self.where_clauses)?;
422            sql.push_str(&where_sql);
423            params.extend(where_params);
424        }
425
426        // GROUP BY clause
427        if !self.group_by.is_empty() {
428            sql.push_str(&format!(" GROUP BY {}", self.group_by.join(", ")));
429        }
430
431        // HAVING clause
432        if !self.having.is_empty() {
433            sql.push_str(" HAVING ");
434            let (having_sql, having_params) = self.build_where_clause(&self.having)?;
435            sql.push_str(&having_sql);
436            params.extend(having_params);
437        }
438
439        // ORDER BY clause
440        if !self.order_by.is_empty() {
441            sql.push_str(" ORDER BY ");
442            let order_clauses: Vec<String> = self
443                .order_by
444                .iter()
445                .map(|sort| format!("{} {}", sort.column, sort.order))
446                .collect();
447            sql.push_str(&order_clauses.join(", "));
448        }
449
450        // LIMIT and OFFSET
451        if let Some(limit) = self.limit {
452            sql.push_str(&format!(" LIMIT {limit}"));
453        }
454        if let Some(offset) = self.offset {
455            sql.push_str(&format!(" OFFSET {offset}"));
456        }
457
458        Ok((sql, params))
459    }
460
461    /// Build a count query
462    pub fn build_count(&self) -> Result<(String, Vec<libsql::Value>)> {
463        let mut sql = String::new();
464        let mut params = Vec::new();
465
466        sql.push_str("SELECT COUNT(*)");
467
468        // FROM clause
469        sql.push_str(&format!(" FROM {}", self.table));
470
471        // JOIN clauses
472        for join in &self.joins {
473            sql.push_str(&format!(" {} {}", join.join_type, join.table));
474            if let Some(alias) = &join.alias {
475                sql.push_str(&format!(" AS {alias}"));
476            }
477            sql.push_str(&format!(" ON {}", join.condition));
478        }
479
480        // WHERE clause
481        if !self.where_clauses.is_empty() {
482            sql.push_str(" WHERE ");
483            let (where_sql, where_params) = self.build_where_clause(&self.where_clauses)?;
484            sql.push_str(&where_sql);
485            params.extend(where_params);
486        }
487
488        // GROUP BY clause
489        if !self.group_by.is_empty() {
490            sql.push_str(&format!(" GROUP BY {}", self.group_by.join(", ")));
491        }
492
493        // HAVING clause
494        if !self.having.is_empty() {
495            sql.push_str(" HAVING ");
496            let (having_sql, having_params) = self.build_where_clause(&self.having)?;
497            sql.push_str(&having_sql);
498            params.extend(having_params);
499        }
500
501        Ok((sql, params))
502    }
503
504    /// Build where clause from filter operators
505    fn build_where_clause(
506        &self,
507        filters: &[FilterOperator],
508    ) -> Result<(String, Vec<libsql::Value>)> {
509        let mut sql = String::new();
510        let mut params = Vec::new();
511
512        for (i, filter) in filters.iter().enumerate() {
513            if i > 0 {
514                sql.push_str(" AND ");
515            }
516            let (filter_sql, filter_params) = self.build_filter_operator(filter)?;
517            sql.push_str(&filter_sql);
518            params.extend(filter_params);
519        }
520
521        Ok((sql, params))
522    }
523
524    /// Build filter operator
525    fn build_filter_operator(
526        &self,
527        filter: &FilterOperator,
528    ) -> Result<(String, Vec<libsql::Value>)> {
529        match filter {
530            FilterOperator::Single(filter) => self.build_filter(filter),
531            FilterOperator::And(filters) => {
532                let mut sql = String::new();
533                let mut params = Vec::new();
534                sql.push('(');
535                for (i, filter) in filters.iter().enumerate() {
536                    if i > 0 {
537                        sql.push_str(" AND ");
538                    }
539                    let (filter_sql, filter_params) = self.build_filter_operator(filter)?;
540                    sql.push_str(&filter_sql);
541                    params.extend(filter_params);
542                }
543                sql.push(')');
544                Ok((sql, params))
545            }
546            FilterOperator::Or(filters) => {
547                let mut sql = String::new();
548                let mut params = Vec::new();
549                sql.push('(');
550                for (i, filter) in filters.iter().enumerate() {
551                    if i > 0 {
552                        sql.push_str(" OR ");
553                    }
554                    let (filter_sql, filter_params) = self.build_filter_operator(filter)?;
555                    sql.push_str(&filter_sql);
556                    params.extend(filter_params);
557                }
558                sql.push(')');
559                Ok((sql, params))
560            }
561            FilterOperator::Not(filter) => {
562                let (filter_sql, filter_params) = self.build_filter_operator(filter)?;
563                Ok((format!("NOT ({filter_sql})"), filter_params))
564            }
565            FilterOperator::Custom(condition) => Ok((condition.clone(), vec![])),
566        }
567    }
568
569    /// Build individual filter
570    fn build_filter(&self, filter: &crate::Filter) -> Result<(String, Vec<libsql::Value>)> {
571        let mut sql = String::new();
572        let mut params = Vec::new();
573
574        match &filter.operator {
575            Operator::IsNull => {
576                sql.push_str(&format!("{} IS NULL", filter.column));
577            }
578            Operator::IsNotNull => {
579                sql.push_str(&format!("{} IS NOT NULL", filter.column));
580            }
581            _ => {
582                sql.push_str(&format!("{} {} ", filter.column, filter.operator));
583                match &filter.value {
584                    FilterValue::Single(value) => {
585                        sql.push('?');
586                        params.push(self.value_to_libsql_value(value));
587                    }
588                    FilterValue::Multiple(values) => {
589                        sql.push('(');
590                        for (i, value) in values.iter().enumerate() {
591                            if i > 0 {
592                                sql.push_str(", ");
593                            }
594                            sql.push('?');
595                            params.push(self.value_to_libsql_value(value));
596                        }
597                        sql.push(')');
598                    }
599                    FilterValue::Range(min, max) => {
600                        sql.push_str("? AND ?");
601                        params.push(self.value_to_libsql_value(min));
602                        params.push(self.value_to_libsql_value(max));
603                    }
604                }
605            }
606        }
607
608        Ok((sql, params))
609    }
610
611    /// Convert our Value type to libsql::Value
612    fn value_to_libsql_value(&self, value: &Value) -> libsql::Value {
613        match value {
614            Value::Null => libsql::Value::Null,
615            Value::Integer(i) => libsql::Value::Integer(*i),
616            Value::Real(f) => libsql::Value::Real(*f),
617            Value::Text(s) => libsql::Value::Text(s.clone()),
618            Value::Blob(b) => libsql::Value::Blob(b.clone()),
619            Value::Boolean(b) => libsql::Value::Integer(if *b { 1 } else { 0 }),
620        }
621    }
622
623    /// Execute the query
624    pub async fn execute<T>(&self, db: &Database) -> Result<Vec<T>>
625    where
626        T: serde::de::DeserializeOwned,
627    {
628        let (sql, params) = self.build()?;
629        let mut rows = db.query(&sql, params).await?;
630
631        let mut results = Vec::new();
632        while let Some(row) = rows.next().await? {
633            let mut map = HashMap::new();
634            for i in 0..row.column_count() {
635                if let Some(column_name) = row.column_name(i) {
636                    let value = row.get_value(i).unwrap_or(libsql::Value::Null);
637                    map.insert(
638                        column_name.to_string(),
639                        self.libsql_value_to_json_value(&value),
640                    );
641                }
642            }
643            let json_value = serde_json::to_value(map)?;
644            let result: T = serde_json::from_value(json_value)?;
645            results.push(result);
646        }
647
648        Ok(results)
649    }
650
651    /// Execute the query with pagination
652    pub async fn execute_paginated<T>(
653        &self,
654        db: &Database,
655        pagination: &Pagination,
656    ) -> Result<PaginatedResult<T>>
657    where
658        T: serde::de::DeserializeOwned,
659    {
660        // Get total count
661        let count_builder = QueryBuilder::new(&self.table).select(vec!["COUNT(*) as count"]);
662
663        let (count_sql, count_params) = count_builder.build_count()?;
664        let mut count_rows = db.query(&count_sql, count_params).await?;
665        let total: u64 = if let Some(row) = count_rows.next().await? {
666            row.get_value(0)
667                .ok()
668                .and_then(|v| match v {
669                    libsql::Value::Integer(i) => Some(i as u64),
670                    _ => None,
671                })
672                .unwrap_or(0)
673        } else {
674            0
675        };
676
677        // Get paginated data
678        let data_builder = self
679            .clone()
680            .limit(pagination.limit())
681            .offset(pagination.offset());
682
683        let data = data_builder.execute::<T>(db).await?;
684
685        Ok(PaginatedResult::with_total(data, pagination.clone(), total))
686    }
687
688    /// Convert libsql::Value to serde_json::Value
689    fn libsql_value_to_json_value(&self, value: &libsql::Value) -> serde_json::Value {
690        match value {
691            libsql::Value::Null => serde_json::Value::Null,
692            libsql::Value::Integer(i) => serde_json::Value::Number(serde_json::Number::from(*i)),
693            libsql::Value::Real(f) => {
694                if let Some(n) = serde_json::Number::from_f64(*f) {
695                    serde_json::Value::Number(n)
696                } else {
697                    serde_json::Value::Null
698                }
699            }
700            libsql::Value::Text(s) => serde_json::Value::String(s.clone()),
701            libsql::Value::Blob(b) => serde_json::Value::Array(
702                b.iter()
703                    .map(|&byte| serde_json::Value::Number(serde_json::Number::from(byte)))
704                    .collect(),
705            ),
706        }
707    }
708}
709
710impl Clone for QueryBuilder {
711    fn clone(&self) -> Self {
712        Self {
713            table: self.table.clone(),
714            select_columns: self.select_columns.clone(),
715            joins: self.joins.clone(),
716            where_clauses: self.where_clauses.clone(),
717            group_by: self.group_by.clone(),
718            having: self.having.clone(),
719            order_by: self.order_by.clone(),
720            limit: self.limit,
721            offset: self.offset,
722            distinct: self.distinct,
723            aggregate: self.aggregate.clone(),
724        }
725    }
726}
727
728impl Clone for JoinClause {
729    fn clone(&self) -> Self {
730        Self {
731            join_type: self.join_type,
732            table: self.table.clone(),
733            alias: self.alias.clone(),
734            condition: self.condition.clone(),
735        }
736    }
737}
738
739impl Clone for AggregateClause {
740    fn clone(&self) -> Self {
741        Self {
742            function: self.function,
743            column: self.column.clone(),
744            alias: self.alias.clone(),
745        }
746    }
747}
libsql_orm/query.rs

libsql_orm/
query.rs
