Highly ambitious ATProtocol AppView service and sdks
slices.network
1//! SQL query building utilities for dynamic WHERE and ORDER BY clauses.
2//!
3//! This module provides helpers for constructing SQL queries dynamically
4//! based on user input while preventing SQL injection attacks.
5
6use super::cursor::build_field_reference;
7use super::types::{SortField, WhereClause, WhereCondition};
8use crate::models::Record;
9
10/// Builds an ORDER BY clause with optional datetime field information.
11///
12/// # Arguments
13/// * `sort_by` - Optional array of fields to sort by
14/// * `field_is_datetime` - Optional boolean indicating if primary field is datetime
15///
16/// # Returns
17/// SQL ORDER BY clause string (without "ORDER BY" prefix)
18pub fn build_order_by_clause_with_field_info(
19 sort_by: Option<&Vec<SortField>>,
20 field_is_datetime: Option<bool>,
21) -> String {
22 match sort_by {
23 Some(sort_fields) if !sort_fields.is_empty() => {
24 let mut order_clauses = Vec::new();
25 for (index, sort_field) in sort_fields.iter().enumerate() {
26 let field = &sort_field.field;
27 let direction = match sort_field.direction.to_lowercase().as_str() {
28 "desc" => "DESC",
29 _ => "ASC",
30 };
31
32 let is_primary = index == 0;
33 let is_datetime = is_primary && field_is_datetime == Some(true);
34
35 if field
36 .chars()
37 .all(|c| c.is_alphanumeric() || c == '_' || c == '.')
38 {
39 let field_ref = build_field_reference(field, is_datetime);
40
41 if matches!(
42 field.as_str(),
43 "indexed_at" | "uri" | "cid" | "did" | "collection"
44 ) {
45 order_clauses.push(format!("{field_ref} {direction}"));
46 } else {
47 order_clauses.push(format!("{field_ref} {direction} NULLS LAST"));
48 }
49 }
50 }
51 if !order_clauses.is_empty() {
52 let has_indexed_at = order_clauses
53 .iter()
54 .any(|clause| clause.contains("indexed_at"));
55 if !has_indexed_at {
56 order_clauses.push("indexed_at DESC".to_string());
57 }
58 order_clauses.join(", ")
59 } else {
60 "indexed_at DESC".to_string()
61 }
62 }
63 _ => "indexed_at DESC".to_string(),
64 }
65}
66
67/// Builds WHERE conditions from a WhereClause structure.
68///
69/// Returns separate arrays for AND conditions and OR conditions
70/// to be combined in the final query.
71///
72/// Supports both legacy flat conditions and nested and/or arrays.
73///
74/// # Arguments
75/// * `where_clause` - Optional where clause with AND/OR conditions
76/// * `param_count` - Mutable counter for parameter numbering ($1, $2, etc)
77///
78/// # Returns
79/// Tuple of (and_conditions, or_conditions) as SQL strings
80pub fn build_where_conditions(
81 where_clause: Option<&WhereClause>,
82 param_count: &mut usize,
83) -> (Vec<String>, Vec<String>) {
84 let mut where_clauses = Vec::new();
85 let mut or_clauses = Vec::new();
86
87 if let Some(clause) = where_clause {
88 // Handle legacy flat conditions
89 for (field, condition) in &clause.conditions {
90 let field_clause = build_single_condition(field, condition, param_count);
91 if !field_clause.is_empty() {
92 where_clauses.push(field_clause);
93 }
94 }
95
96 // Handle legacy or_conditions
97 if let Some(or_conditions) = &clause.or_conditions {
98 for (field, condition) in or_conditions {
99 let field_clause = build_single_condition(field, condition, param_count);
100 if !field_clause.is_empty() {
101 or_clauses.push(field_clause);
102 }
103 }
104 }
105
106 // Handle nested AND array
107 if let Some(and_clauses) = &clause.and {
108 for nested_clause in and_clauses {
109 let nested_sql = build_nested_clause(nested_clause, param_count);
110 if !nested_sql.is_empty() {
111 where_clauses.push(nested_sql);
112 }
113 }
114 }
115
116 // Handle nested OR array
117 if let Some(or_clauses_nested) = &clause.or {
118 let mut or_parts = Vec::new();
119 for nested_clause in or_clauses_nested {
120 let nested_sql = build_nested_clause(nested_clause, param_count);
121 if !nested_sql.is_empty() {
122 or_parts.push(nested_sql);
123 }
124 }
125 if !or_parts.is_empty() {
126 let or_sql = format!("({})", or_parts.join(" OR "));
127 where_clauses.push(or_sql);
128 }
129 }
130 }
131
132 (where_clauses, or_clauses)
133}
134
135/// Builds a nested WHERE clause recursively.
136///
137/// Handles arbitrarily nested and/or structures.
138///
139/// # Arguments
140/// * `clause` - The WhereClause to process
141/// * `param_count` - Mutable counter for parameter numbering
142///
143/// # Returns
144/// SQL condition string (may be wrapped in parentheses)
145fn build_nested_clause(clause: &WhereClause, param_count: &mut usize) -> String {
146 let mut parts = Vec::new();
147
148 // Add flat conditions
149 for (field, condition) in &clause.conditions {
150 let field_clause = build_single_condition(field, condition, param_count);
151 if !field_clause.is_empty() {
152 parts.push(field_clause);
153 }
154 }
155
156 // Add legacy or_conditions as an OR group
157 if let Some(or_conditions) = &clause.or_conditions {
158 let mut or_parts = Vec::new();
159 for (field, condition) in or_conditions {
160 let field_clause = build_single_condition(field, condition, param_count);
161 if !field_clause.is_empty() {
162 or_parts.push(field_clause);
163 }
164 }
165 if !or_parts.is_empty() {
166 parts.push(format!("({})", or_parts.join(" OR ")));
167 }
168 }
169
170 // Add nested AND array
171 if let Some(and_clauses) = &clause.and {
172 for nested_clause in and_clauses {
173 let nested_sql = build_nested_clause(nested_clause, param_count);
174 if !nested_sql.is_empty() {
175 parts.push(nested_sql);
176 }
177 }
178 }
179
180 // Add nested OR array
181 if let Some(or_clauses) = &clause.or {
182 let mut or_parts = Vec::new();
183 for nested_clause in or_clauses {
184 let nested_sql = build_nested_clause(nested_clause, param_count);
185 if !nested_sql.is_empty() {
186 or_parts.push(nested_sql);
187 }
188 }
189 if !or_parts.is_empty() {
190 parts.push(format!("({})", or_parts.join(" OR ")));
191 }
192 }
193
194 if parts.is_empty() {
195 String::new()
196 } else if parts.len() == 1 {
197 parts[0].clone()
198 } else {
199 format!("({})", parts.join(" AND "))
200 }
201}
202
203/// Builds a single SQL condition clause for a field.
204///
205/// Supports equality (eq), array membership (in_values), pattern matching (contains),
206/// fuzzy matching (fuzzy), and comparison operators (gt, gte, lt, lte)
207/// for both table columns and JSON fields with nested paths.
208///
209/// # Arguments
210/// * `field` - Field name (table column or JSON path)
211/// * `condition` - The condition to apply
212/// * `param_count` - Mutable counter for parameter numbering
213///
214/// # Returns
215/// SQL condition string with parameter placeholder
216pub fn build_single_condition(
217 field: &str,
218 condition: &WhereCondition,
219 param_count: &mut usize,
220) -> String {
221 if let Some(_eq_value) = &condition.eq {
222 let clause = match field {
223 "did" | "collection" | "uri" | "cid" => {
224 format!("{} = ${}", field, param_count)
225 }
226 _ => {
227 let json_path = build_json_path(field);
228 format!("{} = ${}", json_path, param_count)
229 }
230 };
231 *param_count += 1;
232 clause
233 } else if let Some(_in_values) = &condition.in_values {
234 let clause = match field {
235 "did" | "collection" | "uri" | "cid" => {
236 format!("{} = ANY(${})", field, param_count)
237 }
238 _ => {
239 let json_path = build_json_path(field);
240 format!("{} = ANY(${})", json_path, param_count)
241 }
242 };
243 *param_count += 1;
244 clause
245 } else if let Some(_contains_value) = &condition.contains {
246 let clause = match field {
247 "did" | "collection" | "uri" | "cid" => {
248 format!("{} ILIKE '%' || ${} || '%'", field, param_count)
249 }
250 "json" => {
251 format!("json::text ILIKE '%' || ${} || '%'", param_count)
252 }
253 _ => {
254 let json_path = build_json_path(field);
255 format!("{} ILIKE '%' || ${} || '%'", json_path, param_count)
256 }
257 };
258 *param_count += 1;
259 clause
260 } else if let Some(_fuzzy_value) = &condition.fuzzy {
261 let clause = match field {
262 "did" | "collection" | "uri" | "cid" => {
263 format!("{} % ${}", field, param_count)
264 }
265 "json" => {
266 format!("json::text % ${}", param_count)
267 }
268 _ => {
269 let json_path = build_json_path(field);
270 format!("({}) % ${}", json_path, param_count)
271 }
272 };
273 *param_count += 1;
274 clause
275 } else if let Some(_gt_value) = &condition.gt {
276 let clause = match field {
277 "indexed_at" => {
278 format!("{} > ${}::timestamptz", field, param_count)
279 }
280 "did" | "collection" | "uri" | "cid" => {
281 format!("{} > ${}", field, param_count)
282 }
283 _ => {
284 let json_path = build_json_path(field);
285 format!("{} > ${}", json_path, param_count)
286 }
287 };
288 *param_count += 1;
289 clause
290 } else if let Some(_gte_value) = &condition.gte {
291 let clause = match field {
292 "indexed_at" => {
293 format!("{} >= ${}::timestamptz", field, param_count)
294 }
295 "did" | "collection" | "uri" | "cid" => {
296 format!("{} >= ${}", field, param_count)
297 }
298 _ => {
299 let json_path = build_json_path(field);
300 format!("{} >= ${}", json_path, param_count)
301 }
302 };
303 *param_count += 1;
304 clause
305 } else if let Some(_lt_value) = &condition.lt {
306 let clause = match field {
307 "indexed_at" => {
308 format!("{} < ${}::timestamptz", field, param_count)
309 }
310 "did" | "collection" | "uri" | "cid" => {
311 format!("{} < ${}", field, param_count)
312 }
313 _ => {
314 let json_path = build_json_path(field);
315 format!("{} < ${}", json_path, param_count)
316 }
317 };
318 *param_count += 1;
319 clause
320 } else if let Some(_lte_value) = &condition.lte {
321 let clause = match field {
322 "indexed_at" => {
323 format!("{} <= ${}::timestamptz", field, param_count)
324 }
325 "did" | "collection" | "uri" | "cid" => {
326 format!("{} <= ${}", field, param_count)
327 }
328 _ => {
329 let json_path = build_json_path(field);
330 format!("{} <= ${}", json_path, param_count)
331 }
332 };
333 *param_count += 1;
334 clause
335 } else {
336 String::new()
337 }
338}
339
340/// Builds a PostgreSQL JSON path accessor string.
341///
342/// Converts dot notation (e.g., "user.name") into PostgreSQL JSON operators
343/// (e.g., "json->'user'->>'name'").
344fn build_json_path(field: &str) -> String {
345 if field.contains('.') {
346 let parts: Vec<&str> = field.split('.').collect();
347 let mut path = String::from("json");
348 for (i, part) in parts.iter().enumerate() {
349 if i == parts.len() - 1 {
350 path.push_str(&format!("->>'{}'", part));
351 } else {
352 path.push_str(&format!("->'{}'", part));
353 }
354 }
355 path
356 } else {
357 format!("json->>'{}'", field)
358 }
359}
360
361/// Binds WHERE clause parameters to a sqlx query.
362///
363/// Iterates through all conditions and binds their values in the correct order.
364/// Supports both legacy flat conditions and nested and/or arrays.
365///
366/// # Arguments
367/// * `query_builder` - The sqlx query to bind parameters to
368/// * `where_clause` - Optional where clause with parameter values
369///
370/// # Returns
371/// Query builder with all parameters bound
372pub fn bind_where_parameters<'q>(
373 mut query_builder: sqlx::query::QueryAs<
374 'q,
375 sqlx::Postgres,
376 Record,
377 sqlx::postgres::PgArguments,
378 >,
379 where_clause: Option<&'q WhereClause>,
380) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
381 if let Some(clause) = where_clause {
382 query_builder = bind_clause_recursive(query_builder, clause);
383 }
384 query_builder
385}
386
387/// Recursively binds parameters from a WhereClause (including nested clauses).
388fn bind_clause_recursive<'q>(
389 mut query_builder: sqlx::query::QueryAs<
390 'q,
391 sqlx::Postgres,
392 Record,
393 sqlx::postgres::PgArguments,
394 >,
395 clause: &'q WhereClause,
396) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
397 // Bind legacy flat conditions
398 for condition in clause.conditions.values() {
399 query_builder = bind_single_condition(query_builder, condition);
400 }
401
402 // Bind legacy or_conditions
403 if let Some(or_conditions) = &clause.or_conditions {
404 for condition in or_conditions.values() {
405 query_builder = bind_single_condition(query_builder, condition);
406 }
407 }
408
409 // Bind nested AND array
410 if let Some(and_clauses) = &clause.and {
411 for nested_clause in and_clauses {
412 query_builder = bind_clause_recursive(query_builder, nested_clause);
413 }
414 }
415
416 // Bind nested OR array
417 if let Some(or_clauses) = &clause.or {
418 for nested_clause in or_clauses {
419 query_builder = bind_clause_recursive(query_builder, nested_clause);
420 }
421 }
422
423 query_builder
424}
425
426/// Binds parameters for a single condition to a sqlx query.
427///
428/// Handles eq (single value), in_values (array), contains (pattern), fuzzy (similarity), and comparison conditions.
429fn bind_single_condition<'q>(
430 mut query_builder: sqlx::query::QueryAs<
431 'q,
432 sqlx::Postgres,
433 Record,
434 sqlx::postgres::PgArguments,
435 >,
436 condition: &'q WhereCondition,
437) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
438 if let Some(eq_value) = &condition.eq {
439 if let Some(str_val) = eq_value.as_str() {
440 query_builder = query_builder.bind(str_val);
441 } else {
442 query_builder = query_builder.bind(eq_value);
443 }
444 }
445
446 if let Some(in_values) = &condition.in_values {
447 let str_values: Vec<String> = in_values
448 .iter()
449 .filter_map(|v| v.as_str().map(|s| s.to_string()))
450 .collect();
451 query_builder = query_builder.bind(str_values);
452 }
453
454 if let Some(contains_value) = &condition.contains {
455 query_builder = query_builder.bind(contains_value);
456 }
457
458 if let Some(fuzzy_value) = &condition.fuzzy {
459 query_builder = query_builder.bind(fuzzy_value);
460 }
461
462 if let Some(gt_value) = &condition.gt {
463 if let Some(str_val) = gt_value.as_str() {
464 query_builder = query_builder.bind(str_val);
465 } else {
466 query_builder = query_builder.bind(gt_value);
467 }
468 }
469
470 if let Some(gte_value) = &condition.gte {
471 if let Some(str_val) = gte_value.as_str() {
472 query_builder = query_builder.bind(str_val);
473 } else {
474 query_builder = query_builder.bind(gte_value);
475 }
476 }
477
478 if let Some(lt_value) = &condition.lt {
479 if let Some(str_val) = lt_value.as_str() {
480 query_builder = query_builder.bind(str_val);
481 } else {
482 query_builder = query_builder.bind(lt_value);
483 }
484 }
485
486 if let Some(lte_value) = &condition.lte {
487 if let Some(str_val) = lte_value.as_str() {
488 query_builder = query_builder.bind(str_val);
489 } else {
490 query_builder = query_builder.bind(lte_value);
491 }
492 }
493
494 query_builder
495}
496
497/// Binds WHERE clause parameters to a sqlx scalar query (for COUNT queries).
498///
499/// Iterates through all conditions and binds their values in the correct order.
500/// Supports both legacy flat conditions and nested and/or arrays.
501///
502/// # Arguments
503/// * `query_builder` - The sqlx scalar query to bind parameters to
504/// * `where_clause` - Optional where clause with parameter values
505///
506/// # Returns
507/// Query builder with all parameters bound
508pub fn bind_where_parameters_scalar<'q, T>(
509 mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
510 where_clause: Option<&'q WhereClause>,
511) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
512where
513 T: Send + Unpin,
514{
515 if let Some(clause) = where_clause {
516 query_builder = bind_clause_recursive_scalar(query_builder, clause);
517 }
518 query_builder
519}
520
521/// Recursively binds parameters from a WhereClause to a scalar query.
522fn bind_clause_recursive_scalar<'q, T>(
523 mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
524 clause: &'q WhereClause,
525) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
526where
527 T: Send + Unpin,
528{
529 // Bind legacy flat conditions
530 for condition in clause.conditions.values() {
531 query_builder = bind_single_condition_scalar(query_builder, condition);
532 }
533
534 // Bind legacy or_conditions
535 if let Some(or_conditions) = &clause.or_conditions {
536 for condition in or_conditions.values() {
537 query_builder = bind_single_condition_scalar(query_builder, condition);
538 }
539 }
540
541 // Bind nested AND array
542 if let Some(and_clauses) = &clause.and {
543 for nested_clause in and_clauses {
544 query_builder = bind_clause_recursive_scalar(query_builder, nested_clause);
545 }
546 }
547
548 // Bind nested OR array
549 if let Some(or_clauses) = &clause.or {
550 for nested_clause in or_clauses {
551 query_builder = bind_clause_recursive_scalar(query_builder, nested_clause);
552 }
553 }
554
555 query_builder
556}
557
558/// Binds parameters for a single condition to a sqlx scalar query.
559fn bind_single_condition_scalar<'q, T>(
560 mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
561 condition: &'q WhereCondition,
562) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
563where
564 T: Send + Unpin,
565{
566 if let Some(eq_value) = &condition.eq {
567 if let Some(str_val) = eq_value.as_str() {
568 query_builder = query_builder.bind(str_val);
569 } else {
570 query_builder = query_builder.bind(eq_value);
571 }
572 }
573
574 if let Some(in_values) = &condition.in_values {
575 let str_values: Vec<String> = in_values
576 .iter()
577 .filter_map(|v| v.as_str().map(|s| s.to_string()))
578 .collect();
579 query_builder = query_builder.bind(str_values);
580 }
581
582 if let Some(contains_value) = &condition.contains {
583 query_builder = query_builder.bind(contains_value);
584 }
585
586 if let Some(fuzzy_value) = &condition.fuzzy {
587 query_builder = query_builder.bind(fuzzy_value);
588 }
589
590 if let Some(gt_value) = &condition.gt {
591 if let Some(str_val) = gt_value.as_str() {
592 query_builder = query_builder.bind(str_val);
593 } else {
594 query_builder = query_builder.bind(gt_value);
595 }
596 }
597
598 if let Some(gte_value) = &condition.gte {
599 if let Some(str_val) = gte_value.as_str() {
600 query_builder = query_builder.bind(str_val);
601 } else {
602 query_builder = query_builder.bind(gte_value);
603 }
604 }
605
606 if let Some(lt_value) = &condition.lt {
607 if let Some(str_val) = lt_value.as_str() {
608 query_builder = query_builder.bind(str_val);
609 } else {
610 query_builder = query_builder.bind(lt_value);
611 }
612 }
613
614 if let Some(lte_value) = &condition.lte {
615 if let Some(str_val) = lte_value.as_str() {
616 query_builder = query_builder.bind(str_val);
617 } else {
618 query_builder = query_builder.bind(lte_value);
619 }
620 }
621
622 query_builder
623}
624
625#[cfg(test)]
626mod tests {
627 use super::*;
628
629 #[test]
630 fn test_build_order_by_clause_default() {
631 let result = build_order_by_clause_with_field_info(None, None);
632 assert_eq!(result, "indexed_at DESC");
633 }
634
635 #[test]
636 fn test_build_order_by_clause_single_field() {
637 let sort_by = vec![SortField {
638 field: "createdAt".to_string(),
639 direction: "desc".to_string(),
640 }];
641
642 let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
643 assert_eq!(
644 result,
645 "json->>'createdAt' DESC NULLS LAST, indexed_at DESC"
646 );
647 }
648
649 #[test]
650 fn test_build_order_by_clause_datetime_field() {
651 let sort_by = vec![SortField {
652 field: "createdAt".to_string(),
653 direction: "desc".to_string(),
654 }];
655
656 let result = build_order_by_clause_with_field_info(Some(&sort_by), Some(true));
657 assert_eq!(
658 result,
659 "(json->>'createdAt')::timestamp DESC NULLS LAST, indexed_at DESC"
660 );
661 }
662
663 #[test]
664 fn test_build_order_by_clause_table_column() {
665 let sort_by = vec![SortField {
666 field: "indexed_at".to_string(),
667 direction: "asc".to_string(),
668 }];
669
670 let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
671 assert_eq!(result, "indexed_at ASC");
672 }
673
674 #[test]
675 fn test_build_order_by_clause_nested_json() {
676 let sort_by = vec![SortField {
677 field: "author.name".to_string(),
678 direction: "asc".to_string(),
679 }];
680
681 let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
682 assert_eq!(
683 result,
684 "json->'author'->>'name' ASC NULLS LAST, indexed_at DESC"
685 );
686 }
687
688 #[test]
689 fn test_build_order_by_clause_multiple_fields() {
690 let sort_by = vec![
691 SortField {
692 field: "text".to_string(),
693 direction: "desc".to_string(),
694 },
695 SortField {
696 field: "createdAt".to_string(),
697 direction: "asc".to_string(),
698 },
699 ];
700
701 let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
702 assert_eq!(
703 result,
704 "json->>'text' DESC NULLS LAST, json->>'createdAt' ASC NULLS LAST, indexed_at DESC"
705 );
706 }
707}