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

libsql_orm/
query.rs
