kayrozen.com
Heavily customized version of smokesignal - https://whtwnd.com/kayrozen.com/3lpwe4ymowg2t
1// SQL query builder for dynamic event filtering
2//
3// Constructs and executes SQL queries based on filter criteria,
4// supporting PostGIS for location-based filtering.
5
6use sqlx::{PgPool, QueryBuilder};
7use tracing::{debug, instrument, trace};
8
9use super::{EventFilterCriteria, EventSortField, FilterError, LocationFilter, SortOrder};
10use crate::storage::event::model::Event;
11use crate::atproto::lexicon::community::lexicon::calendar::event::{Mode, Status};
12
13/// SQL query builder for event filtering
14#[derive(Debug, Clone)]
15pub struct EventQueryBuilder {
16 pool: PgPool,
17}
18
19impl EventQueryBuilder {
20 /// Create a new query builder
21 pub fn new(pool: PgPool) -> Self {
22 Self { pool }
23 }
24
25 /// Build and execute a query to get filtered events
26 #[instrument(skip(self, criteria), fields(search_term = ?criteria.search_term, page = criteria.page))]
27 pub async fn build_and_execute(
28 &self,
29 criteria: &EventFilterCriteria,
30 ) -> Result<Vec<Event>, FilterError> {
31 let mut query = QueryBuilder::new(
32 "SELECT aturi, cid, did, lexicon, record, name, updated_at FROM events"
33 );
34
35 // Apply filters
36 self.apply_filters(&mut query, criteria);
37
38 // Apply sorting
39 self.apply_sorting(&mut query, criteria);
40
41 // Apply pagination
42 self.apply_pagination(&mut query, criteria);
43
44 trace!("Executing query: {}", query.sql());
45
46 let events = query
47 .build_query_as::<Event>()
48 .fetch_all(&self.pool)
49 .await?;
50
51 Ok(events)
52 }
53
54 /// Build and execute a query to count total results
55 #[instrument(skip(self, criteria))]
56 pub async fn count_results(&self, criteria: &EventFilterCriteria) -> Result<i64, FilterError> {
57 let mut query = QueryBuilder::new("SELECT COUNT(*) FROM events");
58
59 // Apply the same filters but without sorting/pagination
60 self.apply_where_clause(&mut query, criteria);
61
62 let count: (i64,) = query
63 .build_query_as()
64 .fetch_one(&self.pool)
65 .await?;
66
67 Ok(count.0)
68 }
69
70 /// Apply WHERE clause filters to the query
71 fn apply_filters<'a>(
72 &self,
73 query: &mut QueryBuilder<'a, sqlx::Postgres>,
74 criteria: &'a EventFilterCriteria,
75 ) {
76 self.apply_where_clause(query, criteria);
77 }
78
79 /// Apply WHERE clause conditions
80 fn apply_where_clause<'a>(
81 &self,
82 query: &mut QueryBuilder<'a, sqlx::Postgres>,
83 criteria: &'a EventFilterCriteria,
84 ) {
85 let mut has_where = false;
86
87 // Text search in name and description
88 if let Some(ref term) = criteria.search_term {
89 if !term.trim().is_empty() {
90 query.push(if has_where { " AND " } else { " WHERE " });
91 query.push("(name ILIKE ");
92 query.push_bind(format!("%{}%", term));
93 query.push(" OR record->>'description' ILIKE ");
94 query.push_bind(format!("%{}%", term));
95 query.push(")");
96 has_where = true;
97 }
98 }
99
100 // Date filtering - using overlap logic for events within date range
101 // An event overlaps with the filter range if:
102 // event_start <= filter_end AND event_end >= filter_start
103 if criteria.start_date.is_some() || criteria.end_date.is_some() {
104 debug!("Applying date filters: start_date={:?}, end_date={:?}", criteria.start_date, criteria.end_date);
105
106 query.push(if has_where { " AND " } else { " WHERE " });
107 query.push("(");
108
109 let mut condition_added = false;
110
111 if let Some(filter_start) = criteria.start_date {
112 // Event must end on or after filter start date (for overlap)
113 // If no endsAt, use end of start day (startsAt date + 23:59:59)
114 query.push("COALESCE((record->>'endsAt')::timestamptz, \
115 DATE_TRUNC('day', (record->>'startsAt')::timestamptz) + INTERVAL '1 day' - INTERVAL '1 second') >= ");
116 query.push_bind(filter_start);
117 condition_added = true;
118 }
119
120 if let Some(filter_end) = criteria.end_date {
121 // Event must start on or before filter end date (for overlap)
122 if condition_added {
123 query.push(" AND ");
124 }
125 query.push("(record->>'startsAt')::timestamptz <= ");
126 query.push_bind(filter_end);
127 }
128
129 query.push(")");
130 has_where = true;
131 }
132
133 // Creator filtering
134 if let Some(ref creator_did) = criteria.creator_did {
135 query.push(if has_where { " AND " } else { " WHERE " });
136 query.push("did = ");
137 query.push_bind(creator_did);
138 has_where = true;
139 }
140
141 // Mode filtering
142 if !criteria.modes.is_empty() {
143 query.push(if has_where { " AND " } else { " WHERE " });
144 query.push("(");
145 for (i, mode) in criteria.modes.iter().enumerate() {
146 if i > 0 {
147 query.push(" OR ");
148 }
149 // Convert enum to its serde representation for database comparison
150 let mode_str = match mode {
151 Mode::InPerson =>
152 "community.lexicon.calendar.event#inperson",
153 Mode::Virtual =>
154 "community.lexicon.calendar.event#virtual",
155 Mode::Hybrid =>
156 "community.lexicon.calendar.event#hybrid",
157 };
158 query.push("record->>'mode' = ");
159 query.push_bind(mode_str);
160 }
161 query.push(")");
162 has_where = true;
163 }
164
165 // Status filtering
166 if !criteria.statuses.is_empty() {
167 query.push(if has_where { " AND " } else { " WHERE " });
168 query.push("(");
169 for (i, status) in criteria.statuses.iter().enumerate() {
170 if i > 0 {
171 query.push(" OR ");
172 }
173 // Convert enum to its serde representation for database comparison
174 let status_str = match status {
175 Status::Scheduled =>
176 "community.lexicon.calendar.event#scheduled",
177 Status::Rescheduled =>
178 "community.lexicon.calendar.event#rescheduled",
179 Status::Cancelled =>
180 "community.lexicon.calendar.event#cancelled",
181 Status::Postponed =>
182 "community.lexicon.calendar.event#postponed",
183 Status::Planned =>
184 "community.lexicon.calendar.event#planned",
185 };
186 query.push("record->>'status' = ");
187 query.push_bind(status_str);
188 }
189 query.push(")");
190 has_where = true;
191 }
192
193 // Location filtering using PostGIS (if PostGIS extension is available)
194 if let Some(ref location) = criteria.location {
195 self.apply_location_filter(query, location, has_where);
196 }
197 }
198
199 /// Apply location-based filtering using PostGIS
200 /// Uses the cached location_point column for efficient spatial queries.
201 /// Events must have valid coordinates stored in the location_point column to be included.
202 fn apply_location_filter<'a>(
203 &self,
204 query: &mut QueryBuilder<'a, sqlx::Postgres>,
205 location: &'a LocationFilter,
206 has_where: bool,
207 ) {
208 query.push(if has_where { " AND " } else { " WHERE " });
209
210 // Use the cached location_point column for efficient PostGIS spatial queries
211 // This column is populated during event creation/editing when coordinates are available
212 query.push("location_point IS NOT NULL AND ST_DWithin(");
213 query.push(" location_point::geography, ");
214 query.push(" ST_MakePoint(");
215 query.push_bind(location.longitude);
216 query.push(", ");
217 query.push_bind(location.latitude);
218 query.push(")::geography, ");
219 query.push_bind(location.radius_km * 1000.0); // Convert km to meters
220 query.push(")");
221 }
222
223 /// Apply sorting to the query
224 fn apply_sorting<'a>(
225 &self,
226 query: &mut QueryBuilder<'a, sqlx::Postgres>,
227 criteria: &'a EventFilterCriteria,
228 ) {
229 query.push(" ORDER BY ");
230
231 match criteria.sort_by {
232 EventSortField::StartTime => {
233 query.push("(record->>'startsAt')::timestamptz");
234 }
235 EventSortField::UpdatedAt => {
236 query.push("updated_at");
237 }
238 EventSortField::Name => {
239 query.push("name");
240 }
241 EventSortField::PopularityRsvp => {
242 // This would require a more complex query with a subquery or join
243 // For now, fall back to start time
244 query.push("(record->>'startsAt')::timestamptz");
245 }
246 }
247
248 match criteria.sort_order {
249 SortOrder::Ascending => query.push(" ASC"),
250 SortOrder::Descending => query.push(" DESC"),
251 };
252 }
253
254 /// Apply pagination to the query
255 fn apply_pagination<'a>(
256 &self,
257 query: &mut QueryBuilder<'a, sqlx::Postgres>,
258 criteria: &'a EventFilterCriteria,
259 ) {
260 query.push(" LIMIT ");
261 query.push_bind(criteria.page_size as i64);
262 query.push(" OFFSET ");
263
264 // Ensure page is at least 1 to avoid underflow
265 let page = criteria.page.max(1);
266 let offset = (page - 1) * criteria.page_size;
267 query.push_bind(offset as i64);
268 }
269}
270
271#[cfg(test)]
272mod tests {
273 use super::*;
274
275 #[test]
276 fn test_criteria_building() {
277 let criteria = EventFilterCriteria::new()
278 .with_search_term("conference")
279 .with_pagination(0, 10);
280
281 assert_eq!(criteria.search_term, Some("conference".to_string()));
282 assert_eq!(criteria.page, 0);
283 assert_eq!(criteria.page_size, 10);
284 }
285
286 #[test]
287 fn test_has_filters() {
288 let empty_criteria = EventFilterCriteria::new();
289 assert!(!empty_criteria.has_filters());
290
291 let filtered_criteria = EventFilterCriteria::new()
292 .with_search_term("test");
293 assert!(filtered_criteria.has_filters());
294 }
295}