kayrozen.com
forked from
smokesignal.events/smokesignal
i18n+filtering fork - fluent-templates v2
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 /// This method handles all EventLocation types from the ATproto lexicon:
201 /// - Address: Physical addresses (must have coordinates or be geocoded)
202 /// - Geo: Direct latitude/longitude coordinates
203 /// - Fsq: Foursquare place IDs (require coordinates)
204 /// - Hthree: H3 geospatial cell identifiers (require coordinates)
205 /// Virtual/hybrid events without location data are filtered out.
206 fn apply_location_filter<'a>(
207 &self,
208 query: &mut QueryBuilder<'a, sqlx::Postgres>,
209 location: &'a LocationFilter,
210 has_where: bool,
211 ) {
212 query.push(if has_where { " AND " } else { " WHERE " });
213
214 // First, ensure we only get events with actual location data
215 // This filters out virtual/hybrid events without physical locations
216 query.push("(");
217
218 // Check if event has location data at all (not null and not empty array)
219 query.push("record->'location' IS NOT NULL AND ");
220 query.push("jsonb_array_length(CAST(record AS jsonb)->'location') > 0 AND ");
221
222 // Build a comprehensive location check that handles all location types
223 query.push("(");
224
225 // Case 1: Direct Geo coordinates (community.lexicon.location.geo)
226 query.push("EXISTS (");
227 query.push(" SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
228 query.push(" WHERE loc->>'$type' = 'community.lexicon.location.geo' ");
229 query.push(" AND loc->>'latitude' IS NOT NULL ");
230 query.push(" AND loc->>'longitude' IS NOT NULL ");
231 query.push(" AND ST_DWithin(");
232 query.push(" ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
233 query.push(" ST_MakePoint(");
234 query.push_bind(location.longitude);
235 query.push(", ");
236 query.push_bind(location.latitude);
237 query.push(")::geography, ");
238 query.push_bind(location.radius_km * 1000.0); // Convert km to meters
239 query.push(" )");
240 query.push(") OR ");
241
242 // Case 2: Address with coordinates (community.lexicon.location.address)
243 // Note: Addresses without coordinates would need geocoding (future enhancement)
244 query.push("EXISTS (");
245 query.push(" SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
246 query.push(" WHERE loc->>'$type' = 'community.lexicon.location.address' ");
247 query.push(" AND loc->>'latitude' IS NOT NULL ");
248 query.push(" AND loc->>'longitude' IS NOT NULL ");
249 query.push(" AND ST_DWithin(");
250 query.push(" ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
251 query.push(" ST_MakePoint(");
252 query.push_bind(location.longitude);
253 query.push(", ");
254 query.push_bind(location.latitude);
255 query.push(")::geography, ");
256 query.push_bind(location.radius_km * 1000.0);
257 query.push(" )");
258 query.push(") OR ");
259
260 // Case 3: Foursquare places with coordinates (community.lexicon.location.fsq)
261 query.push("EXISTS (");
262 query.push(" SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
263 query.push(" WHERE loc->>'$type' = 'community.lexicon.location.fsq' ");
264 query.push(" AND loc->>'latitude' IS NOT NULL ");
265 query.push(" AND loc->>'longitude' IS NOT NULL ");
266 query.push(" AND ST_DWithin(");
267 query.push(" ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
268 query.push(" ST_MakePoint(");
269 query.push_bind(location.longitude);
270 query.push(", ");
271 query.push_bind(location.latitude);
272 query.push(")::geography, ");
273 query.push_bind(location.radius_km * 1000.0);
274 query.push(" )");
275 query.push(") OR ");
276
277 // Case 4: H3 cells with coordinates (community.lexicon.location.hthree)
278 query.push("EXISTS (");
279 query.push(" SELECT 1 FROM jsonb_array_elements(CAST(record AS jsonb)->'location') AS loc ");
280 query.push(" WHERE loc->>'$type' = 'community.lexicon.location.hthree' ");
281 query.push(" AND loc->>'latitude' IS NOT NULL ");
282 query.push(" AND loc->>'longitude' IS NOT NULL ");
283 query.push(" AND ST_DWithin(");
284 query.push(" ST_MakePoint((loc->>'longitude')::float8, (loc->>'latitude')::float8)::geography, ");
285 query.push(" ST_MakePoint(");
286 query.push_bind(location.longitude);
287 query.push(", ");
288 query.push_bind(location.latitude);
289 query.push(")::geography, ");
290 query.push_bind(location.radius_km * 1000.0);
291 query.push(" )");
292 query.push(")");
293
294 // End the comprehensive location check
295 query.push(")");
296
297 // End the main location filter condition
298 query.push(")");
299 }
300
301 /// Apply sorting to the query
302 fn apply_sorting<'a>(
303 &self,
304 query: &mut QueryBuilder<'a, sqlx::Postgres>,
305 criteria: &'a EventFilterCriteria,
306 ) {
307 query.push(" ORDER BY ");
308
309 match criteria.sort_by {
310 EventSortField::StartTime => {
311 query.push("(record->>'startsAt')::timestamptz");
312 }
313 EventSortField::UpdatedAt => {
314 query.push("updated_at");
315 }
316 EventSortField::Name => {
317 query.push("name");
318 }
319 EventSortField::PopularityRsvp => {
320 // This would require a more complex query with a subquery or join
321 // For now, fall back to start time
322 query.push("(record->>'startsAt')::timestamptz");
323 }
324 }
325
326 match criteria.sort_order {
327 SortOrder::Ascending => query.push(" ASC"),
328 SortOrder::Descending => query.push(" DESC"),
329 };
330 }
331
332 /// Apply pagination to the query
333 fn apply_pagination<'a>(
334 &self,
335 query: &mut QueryBuilder<'a, sqlx::Postgres>,
336 criteria: &'a EventFilterCriteria,
337 ) {
338 query.push(" LIMIT ");
339 query.push_bind(criteria.page_size as i64);
340 query.push(" OFFSET ");
341
342 // Ensure page is at least 1 to avoid underflow
343 let page = criteria.page.max(1);
344 let offset = (page - 1) * criteria.page_size;
345 query.push_bind(offset as i64);
346 }
347}
348
349#[cfg(test)]
350mod tests {
351 use super::*;
352
353 #[test]
354 fn test_criteria_building() {
355 let criteria = EventFilterCriteria::new()
356 .with_search_term("conference")
357 .with_pagination(0, 10);
358
359 assert_eq!(criteria.search_term, Some("conference".to_string()));
360 assert_eq!(criteria.page, 0);
361 assert_eq!(criteria.page_size, 10);
362 }
363
364 #[test]
365 fn test_has_filters() {
366 let empty_criteria = EventFilterCriteria::new();
367 assert!(!empty_criteria.has_filters());
368
369 let filtered_criteria = EventFilterCriteria::new()
370 .with_search_term("test");
371 assert!(filtered_criteria.has_filters());
372 }
373}