Something went wrong. Try again.
Highly ambitious ATProtocol AppView service and sdks forked from slices.network/slices
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708//! SQL query building utilities for dynamic WHERE and ORDER BY clauses.//!//! This module provides helpers for constructing SQL queries dynamically//! based on user input while preventing SQL injection attacks.
use super::cursor::build_field_reference;use super::types::{SortField, WhereClause, WhereCondition};use crate::models::Record;
/// Builds an ORDER BY clause with optional datetime field information.////// # Arguments/// * `sort_by` - Optional array of fields to sort by/// * `field_is_datetime` - Optional boolean indicating if primary field is datetime////// # Returns/// SQL ORDER BY clause string (without "ORDER BY" prefix)pub fn build_order_by_clause_with_field_info( sort_by: Option<&Vec<SortField>>, field_is_datetime: Option<bool>,) -> String { match sort_by { Some(sort_fields) if !sort_fields.is_empty() => { let mut order_clauses = Vec::new(); for (index, sort_field) in sort_fields.iter().enumerate() { let field = &sort_field.field; let direction = match sort_field.direction.to_lowercase().as_str() { "desc" => "DESC", _ => "ASC", };
let is_primary = index == 0; let is_datetime = is_primary && field_is_datetime == Some(true);
if field .chars() .all(|c| c.is_alphanumeric() || c == '_' || c == '.') { let field_ref = build_field_reference(field, is_datetime);
if matches!( field.as_str(), "indexed_at" | "uri" | "cid" | "did" | "collection" ) { order_clauses.push(format!("{field_ref} {direction}")); } else { order_clauses.push(format!("{field_ref} {direction} NULLS LAST")); } } } if !order_clauses.is_empty() { let has_indexed_at = order_clauses .iter() .any(|clause| clause.contains("indexed_at")); if !has_indexed_at { order_clauses.push("indexed_at DESC".to_string()); } order_clauses.join(", ") } else { "indexed_at DESC".to_string() } } _ => "indexed_at DESC".to_string(), }}
/// Builds WHERE conditions from a WhereClause structure.////// Returns separate arrays for AND conditions and OR conditions/// to be combined in the final query.////// Supports both legacy flat conditions and nested and/or arrays.////// # Arguments/// * `where_clause` - Optional where clause with AND/OR conditions/// * `param_count` - Mutable counter for parameter numbering ($1, $2, etc)////// # Returns/// Tuple of (and_conditions, or_conditions) as SQL stringspub fn build_where_conditions( where_clause: Option<&WhereClause>, param_count: &mut usize,) -> (Vec<String>, Vec<String>) { let mut where_clauses = Vec::new(); let mut or_clauses = Vec::new();
if let Some(clause) = where_clause { // Handle legacy flat conditions for (field, condition) in &clause.conditions { let field_clause = build_single_condition(field, condition, param_count); if !field_clause.is_empty() { where_clauses.push(field_clause); } }
// Handle legacy or_conditions if let Some(or_conditions) = &clause.or_conditions { for (field, condition) in or_conditions { let field_clause = build_single_condition(field, condition, param_count); if !field_clause.is_empty() { or_clauses.push(field_clause); } } }
// Handle nested AND array if let Some(and_clauses) = &clause.and { for nested_clause in and_clauses { let nested_sql = build_nested_clause(nested_clause, param_count); if !nested_sql.is_empty() { where_clauses.push(nested_sql); } } }
// Handle nested OR array if let Some(or_clauses_nested) = &clause.or { let mut or_parts = Vec::new(); for nested_clause in or_clauses_nested { let nested_sql = build_nested_clause(nested_clause, param_count); if !nested_sql.is_empty() { or_parts.push(nested_sql); } } if !or_parts.is_empty() { let or_sql = format!("({})", or_parts.join(" OR ")); where_clauses.push(or_sql); } } }
(where_clauses, or_clauses)}
/// Builds a nested WHERE clause recursively.////// Handles arbitrarily nested and/or structures.////// # Arguments/// * `clause` - The WhereClause to process/// * `param_count` - Mutable counter for parameter numbering////// # Returns/// SQL condition string (may be wrapped in parentheses)fn build_nested_clause(clause: &WhereClause, param_count: &mut usize) -> String { let mut parts = Vec::new();
// Add flat conditions for (field, condition) in &clause.conditions { let field_clause = build_single_condition(field, condition, param_count); if !field_clause.is_empty() { parts.push(field_clause); } }
// Add legacy or_conditions as an OR group if let Some(or_conditions) = &clause.or_conditions { let mut or_parts = Vec::new(); for (field, condition) in or_conditions { let field_clause = build_single_condition(field, condition, param_count); if !field_clause.is_empty() { or_parts.push(field_clause); } } if !or_parts.is_empty() { parts.push(format!("({})", or_parts.join(" OR "))); } }
// Add nested AND array if let Some(and_clauses) = &clause.and { for nested_clause in and_clauses { let nested_sql = build_nested_clause(nested_clause, param_count); if !nested_sql.is_empty() { parts.push(nested_sql); } } }
// Add nested OR array if let Some(or_clauses) = &clause.or { let mut or_parts = Vec::new(); for nested_clause in or_clauses { let nested_sql = build_nested_clause(nested_clause, param_count); if !nested_sql.is_empty() { or_parts.push(nested_sql); } } if !or_parts.is_empty() { parts.push(format!("({})", or_parts.join(" OR "))); } }
if parts.is_empty() { String::new() } else if parts.len() == 1 { parts[0].clone() } else { format!("({})", parts.join(" AND ")) }}
/// Builds a single SQL condition clause for a field.////// Supports equality (eq), array membership (in_values), pattern matching (contains),/// fuzzy matching (fuzzy), and comparison operators (gt, gte, lt, lte)/// for both table columns and JSON fields with nested paths.////// # Arguments/// * `field` - Field name (table column or JSON path)/// * `condition` - The condition to apply/// * `param_count` - Mutable counter for parameter numbering////// # Returns/// SQL condition string with parameter placeholderpub fn build_single_condition( field: &str, condition: &WhereCondition, param_count: &mut usize,) -> String { if let Some(_eq_value) = &condition.eq { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} = ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} = ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_in_values) = &condition.in_values { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} = ANY(${})", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} = ANY(${})", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_contains_value) = &condition.contains { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} ILIKE '%' || ${} || '%'", field, param_count) } "json" => { format!("json::text ILIKE '%' || ${} || '%'", param_count) } _ => { let json_path = build_json_path(field); format!("{} ILIKE '%' || ${} || '%'", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_fuzzy_value) = &condition.fuzzy { let clause = match field { "did" | "collection" | "uri" | "cid" => { format!("{} % ${}", field, param_count) } "json" => { format!("json::text % ${}", param_count) } _ => { let json_path = build_json_path(field); format!("({}) % ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_gt_value) = &condition.gt { let clause = match field { "indexed_at" => { format!("{} > ${}::timestamptz", field, param_count) } "did" | "collection" | "uri" | "cid" => { format!("{} > ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} > ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_gte_value) = &condition.gte { let clause = match field { "indexed_at" => { format!("{} >= ${}::timestamptz", field, param_count) } "did" | "collection" | "uri" | "cid" => { format!("{} >= ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} >= ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_lt_value) = &condition.lt { let clause = match field { "indexed_at" => { format!("{} < ${}::timestamptz", field, param_count) } "did" | "collection" | "uri" | "cid" => { format!("{} < ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} < ${}", json_path, param_count) } }; *param_count += 1; clause } else if let Some(_lte_value) = &condition.lte { let clause = match field { "indexed_at" => { format!("{} <= ${}::timestamptz", field, param_count) } "did" | "collection" | "uri" | "cid" => { format!("{} <= ${}", field, param_count) } _ => { let json_path = build_json_path(field); format!("{} <= ${}", json_path, param_count) } }; *param_count += 1; clause } else { String::new() }}
/// Builds a PostgreSQL JSON path accessor string.////// Converts dot notation (e.g., "user.name") into PostgreSQL JSON operators/// (e.g., "json->'user'->>'name'").fn build_json_path(field: &str) -> String { if field.contains('.') { let parts: Vec<&str> = field.split('.').collect(); let mut path = String::from("json"); for (i, part) in parts.iter().enumerate() { if i == parts.len() - 1 { path.push_str(&format!("->>'{}'", part)); } else { path.push_str(&format!("->'{}'", part)); } } path } else { format!("json->>'{}'", field) }}
/// Binds WHERE clause parameters to a sqlx query.////// Iterates through all conditions and binds their values in the correct order./// Supports both legacy flat conditions and nested and/or arrays.////// # Arguments/// * `query_builder` - The sqlx query to bind parameters to/// * `where_clause` - Optional where clause with parameter values////// # Returns/// Query builder with all parameters boundpub fn bind_where_parameters<'q>( mut query_builder: sqlx::query::QueryAs< 'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments, >, where_clause: Option<&'q WhereClause>,) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> { if let Some(clause) = where_clause { query_builder = bind_clause_recursive(query_builder, clause); } query_builder}
/// Recursively binds parameters from a WhereClause (including nested clauses).fn bind_clause_recursive<'q>( mut query_builder: sqlx::query::QueryAs< 'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments, >, clause: &'q WhereClause,) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> { // Bind legacy flat conditions for condition in clause.conditions.values() { query_builder = bind_single_condition(query_builder, condition); }
// Bind legacy or_conditions if let Some(or_conditions) = &clause.or_conditions { for condition in or_conditions.values() { query_builder = bind_single_condition(query_builder, condition); } }
// Bind nested AND array if let Some(and_clauses) = &clause.and { for nested_clause in and_clauses { query_builder = bind_clause_recursive(query_builder, nested_clause); } }
// Bind nested OR array if let Some(or_clauses) = &clause.or { for nested_clause in or_clauses { query_builder = bind_clause_recursive(query_builder, nested_clause); } }
query_builder}
/// Binds parameters for a single condition to a sqlx query.////// Handles eq (single value), in_values (array), contains (pattern), fuzzy (similarity), and comparison conditions.fn bind_single_condition<'q>( mut query_builder: sqlx::query::QueryAs< 'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments, >, condition: &'q WhereCondition,) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> { if let Some(eq_value) = &condition.eq { if let Some(str_val) = eq_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(eq_value); } }
if let Some(in_values) = &condition.in_values { let str_values: Vec<String> = in_values .iter() .filter_map(|v| v.as_str().map(|s| s.to_string())) .collect(); query_builder = query_builder.bind(str_values); }
if let Some(contains_value) = &condition.contains { query_builder = query_builder.bind(contains_value); }
if let Some(fuzzy_value) = &condition.fuzzy { query_builder = query_builder.bind(fuzzy_value); }
if let Some(gt_value) = &condition.gt { if let Some(str_val) = gt_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(gt_value); } }
if let Some(gte_value) = &condition.gte { if let Some(str_val) = gte_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(gte_value); } }
if let Some(lt_value) = &condition.lt { if let Some(str_val) = lt_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(lt_value); } }
if let Some(lte_value) = &condition.lte { if let Some(str_val) = lte_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(lte_value); } }
query_builder}
/// Binds WHERE clause parameters to a sqlx scalar query (for COUNT queries).////// Iterates through all conditions and binds their values in the correct order./// Supports both legacy flat conditions and nested and/or arrays.////// # Arguments/// * `query_builder` - The sqlx scalar query to bind parameters to/// * `where_clause` - Optional where clause with parameter values////// # Returns/// Query builder with all parameters boundpub fn bind_where_parameters_scalar<'q, T>( mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>, where_clause: Option<&'q WhereClause>,) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>where T: Send + Unpin,{ if let Some(clause) = where_clause { query_builder = bind_clause_recursive_scalar(query_builder, clause); } query_builder}
/// Recursively binds parameters from a WhereClause to a scalar query.fn bind_clause_recursive_scalar<'q, T>( mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>, clause: &'q WhereClause,) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>where T: Send + Unpin,{ // Bind legacy flat conditions for condition in clause.conditions.values() { query_builder = bind_single_condition_scalar(query_builder, condition); }
// Bind legacy or_conditions if let Some(or_conditions) = &clause.or_conditions { for condition in or_conditions.values() { query_builder = bind_single_condition_scalar(query_builder, condition); } }
// Bind nested AND array if let Some(and_clauses) = &clause.and { for nested_clause in and_clauses { query_builder = bind_clause_recursive_scalar(query_builder, nested_clause); } }
// Bind nested OR array if let Some(or_clauses) = &clause.or { for nested_clause in or_clauses { query_builder = bind_clause_recursive_scalar(query_builder, nested_clause); } }
query_builder}
/// Binds parameters for a single condition to a sqlx scalar query.fn bind_single_condition_scalar<'q, T>( mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>, condition: &'q WhereCondition,) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>where T: Send + Unpin,{ if let Some(eq_value) = &condition.eq { if let Some(str_val) = eq_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(eq_value); } }
if let Some(in_values) = &condition.in_values { let str_values: Vec<String> = in_values .iter() .filter_map(|v| v.as_str().map(|s| s.to_string())) .collect(); query_builder = query_builder.bind(str_values); }
if let Some(contains_value) = &condition.contains { query_builder = query_builder.bind(contains_value); }
if let Some(fuzzy_value) = &condition.fuzzy { query_builder = query_builder.bind(fuzzy_value); }
if let Some(gt_value) = &condition.gt { if let Some(str_val) = gt_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(gt_value); } }
if let Some(gte_value) = &condition.gte { if let Some(str_val) = gte_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(gte_value); } }
if let Some(lt_value) = &condition.lt { if let Some(str_val) = lt_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(lt_value); } }
if let Some(lte_value) = &condition.lte { if let Some(str_val) = lte_value.as_str() { query_builder = query_builder.bind(str_val); } else { query_builder = query_builder.bind(lte_value); } }
query_builder}
#[cfg(test)]mod tests { use super::*;
#[test] fn test_build_order_by_clause_default() { let result = build_order_by_clause_with_field_info(None, None); assert_eq!(result, "indexed_at DESC"); }
#[test] fn test_build_order_by_clause_single_field() { let sort_by = vec![SortField { field: "createdAt".to_string(), direction: "desc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!( result, "json->>'createdAt' DESC NULLS LAST, indexed_at DESC" ); }
#[test] fn test_build_order_by_clause_datetime_field() { let sort_by = vec![SortField { field: "createdAt".to_string(), direction: "desc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), Some(true)); assert_eq!( result, "(json->>'createdAt')::timestamp DESC NULLS LAST, indexed_at DESC" ); }
#[test] fn test_build_order_by_clause_table_column() { let sort_by = vec![SortField { field: "indexed_at".to_string(), direction: "asc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!(result, "indexed_at ASC"); }
#[test] fn test_build_order_by_clause_nested_json() { let sort_by = vec![SortField { field: "author.name".to_string(), direction: "asc".to_string(), }];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!( result, "json->'author'->>'name' ASC NULLS LAST, indexed_at DESC" ); }
#[test] fn test_build_order_by_clause_multiple_fields() { let sort_by = vec![ SortField { field: "text".to_string(), direction: "desc".to_string(), }, SortField { field: "createdAt".to_string(), direction: "asc".to_string(), }, ];
let result = build_order_by_clause_with_field_info(Some(&sort_by), None); assert_eq!( result, "json->>'text' DESC NULLS LAST, json->>'createdAt' ASC NULLS LAST, indexed_at DESC" ); }}