things3_core/database/queries/
tasks.rs

1#![allow(deprecated)]
2
3#[cfg(feature = "advanced-queries")]
4use crate::database::conversions::naive_date_to_things_timestamp;
5#[cfg(feature = "advanced-queries")]
6use crate::models::TaskFilters;
7use crate::{
8    database::{mappers::map_task_row, ThingsDatabase},
9    error::{Result as ThingsResult, ThingsError},
10    models::{Task, TaskStatus, TaskType, ThingsId},
11};
12use chrono::{DateTime, NaiveDate, Utc};
13use sqlx::Row;
14use tracing::{debug, instrument};
15#[cfg(any(feature = "advanced-queries", feature = "batch-operations"))]
16use uuid::Uuid;
17
18impl ThingsDatabase {
19    /// Get all tasks from the database
20    ///
21    /// # Examples
22    ///
23    /// ```no_run
24    /// use things3_core::{ThingsDatabase, ThingsError};
25    /// use std::path::Path;
26    ///
27    /// # async fn example() -> Result<(), ThingsError> {
28    /// let db = ThingsDatabase::new(Path::new("/path/to/things.db")).await?;
29    ///
30    /// // Get all tasks
31    /// let tasks = db.get_all_tasks().await?;
32    /// println!("Found {} total tasks", tasks.len());
33    ///
34    /// // Filter tasks by status
35    /// let incomplete: Vec<_> = tasks.iter()
36    ///     .filter(|t| t.status == things3_core::TaskStatus::Incomplete)
37    ///     .collect();
38    /// println!("Found {} incomplete tasks", incomplete.len());
39    /// # Ok(())
40    /// # }
41    /// ```
42    ///
43    /// # Errors
44    ///
45    /// Returns an error if the database query fails or if task data is invalid
46    #[instrument]
47    pub async fn get_all_tasks(&self) -> ThingsResult<Vec<Task>> {
48        let rows = sqlx::query(
49            r"
50            SELECT 
51                uuid, title, status, type, 
52                start_date, due_date, 
53                project_uuid, area_uuid, 
54                notes, tags, 
55                created, modified
56            FROM TMTask
57            ORDER BY created DESC
58            ",
59        )
60        .fetch_all(&self.pool)
61        .await
62        .map_err(|e| ThingsError::unknown(format!("Failed to fetch tasks: {e}")))?;
63
64        let mut tasks = Vec::new();
65        for row in rows {
66            let task = Task {
67                uuid: ThingsId::from_trusted(row.get::<String, _>("uuid")),
68                title: row.get("title"),
69                status: TaskStatus::from_i32(row.get("status")).unwrap_or(TaskStatus::Incomplete),
70                task_type: TaskType::from_i32(row.get("type")).unwrap_or(TaskType::Todo),
71                start_date: row
72                    .get::<Option<String>, _>("start_date")
73                    .and_then(|s| NaiveDate::parse_from_str(&s, "%Y-%m-%d").ok()),
74                deadline: row
75                    .get::<Option<String>, _>("due_date")
76                    .and_then(|s| NaiveDate::parse_from_str(&s, "%Y-%m-%d").ok()),
77                project_uuid: row
78                    .get::<Option<String>, _>("project_uuid")
79                    .map(ThingsId::from_trusted),
80                area_uuid: row
81                    .get::<Option<String>, _>("area_uuid")
82                    .map(ThingsId::from_trusted),
83                parent_uuid: None, // Not available in this query
84                notes: row.get("notes"),
85                tags: row
86                    .get::<Option<String>, _>("tags")
87                    .map(|s| s.split(',').map(|s| s.trim().to_string()).collect())
88                    .unwrap_or_default(),
89                children: Vec::new(), // Not available in this query
90                created: DateTime::parse_from_rfc3339(&row.get::<String, _>("created"))
91                    .ok()
92                    .map_or_else(Utc::now, |dt| dt.with_timezone(&Utc)),
93                modified: DateTime::parse_from_rfc3339(&row.get::<String, _>("modified"))
94                    .ok()
95                    .map_or_else(Utc::now, |dt| dt.with_timezone(&Utc)),
96                stop_date: None, // Not available in this query context
97            };
98            tasks.push(task);
99        }
100
101        debug!("Fetched {} tasks", tasks.len());
102        Ok(tasks)
103    }
104
105    /// Get tasks by status
106    ///
107    /// # Errors
108    ///
109    /// Returns an error if the database query fails or if task data is invalid
110    #[instrument]
111    pub async fn get_tasks_by_status(&self, status: TaskStatus) -> ThingsResult<Vec<Task>> {
112        let status_value = status as i32;
113        let rows = sqlx::query(
114            r"
115            SELECT 
116                uuid, title, status, type, 
117                start_date, due_date, 
118                project_uuid, area_uuid, 
119                notes, tags, 
120                created, modified
121             FROM TMTask 
122            WHERE status = ?
123            ORDER BY created DESC
124            ",
125        )
126        .bind(status_value)
127        .fetch_all(&self.pool)
128        .await
129        .map_err(|e| ThingsError::unknown(format!("Failed to fetch tasks by status: {e}")))?;
130
131        let mut tasks = Vec::new();
132        for row in rows {
133            let task = Task {
134                uuid: ThingsId::from_trusted(row.get::<String, _>("uuid")),
135                title: row.get("title"),
136                status: TaskStatus::from_i32(row.get("status")).unwrap_or(TaskStatus::Incomplete),
137                task_type: TaskType::from_i32(row.get("type")).unwrap_or(TaskType::Todo),
138                start_date: row
139                    .get::<Option<String>, _>("start_date")
140                    .and_then(|s| NaiveDate::parse_from_str(&s, "%Y-%m-%d").ok()),
141                deadline: row
142                    .get::<Option<String>, _>("due_date")
143                    .and_then(|s| NaiveDate::parse_from_str(&s, "%Y-%m-%d").ok()),
144                project_uuid: row
145                    .get::<Option<String>, _>("project_uuid")
146                    .map(ThingsId::from_trusted),
147                area_uuid: row
148                    .get::<Option<String>, _>("area_uuid")
149                    .map(ThingsId::from_trusted),
150                parent_uuid: None, // Not available in this query
151                notes: row.get("notes"),
152                tags: row
153                    .get::<Option<String>, _>("tags")
154                    .map(|s| s.split(',').map(|s| s.trim().to_string()).collect())
155                    .unwrap_or_default(),
156                children: Vec::new(), // Not available in this query
157                created: DateTime::parse_from_rfc3339(&row.get::<String, _>("created"))
158                    .ok()
159                    .map_or_else(Utc::now, |dt| dt.with_timezone(&Utc)),
160                modified: DateTime::parse_from_rfc3339(&row.get::<String, _>("modified"))
161                    .ok()
162                    .map_or_else(Utc::now, |dt| dt.with_timezone(&Utc)),
163                stop_date: None, // Not available in this query context
164            };
165            tasks.push(task);
166        }
167
168        debug!("Fetched {} tasks with status {:?}", tasks.len(), status);
169        Ok(tasks)
170    }
171
172    /// Search tasks by title or notes
173    ///
174    /// # Errors
175    ///
176    /// Returns an error if the database query fails or if task data is invalid
177    #[instrument]
178    pub async fn search_tasks(&self, query: &str) -> ThingsResult<Vec<Task>> {
179        let search_pattern = format!("%{query}%");
180        let rows = sqlx::query(
181            r"
182            SELECT
183                uuid, title, status, type,
184                startDate, deadline, stopDate,
185                project, area, heading,
186                notes,
187                (SELECT GROUP_CONCAT(tg.title, char(31))
188                   FROM TMTaskTag tt
189                   JOIN TMTag tg ON tg.uuid = tt.tags
190                  WHERE tt.tasks = TMTask.uuid) AS tags_csv,
191                creationDate, userModificationDate
192            FROM TMTask
193            WHERE (title LIKE ? OR notes LIKE ?) AND type IN (0, 2) AND trashed = 0
194            ORDER BY creationDate DESC
195            ",
196        )
197        .bind(&search_pattern)
198        .bind(&search_pattern)
199        .fetch_all(&self.pool)
200        .await
201        .map_err(|e| ThingsError::unknown(format!("Failed to search tasks: {e}")))?;
202
203        let tasks = rows
204            .iter()
205            .map(map_task_row)
206            .collect::<ThingsResult<Vec<Task>>>()?;
207
208        debug!("Found {} tasks matching query: {}", tasks.len(), query);
209        Ok(tasks)
210    }
211
212    /// Query tasks using a [`TaskFilters`] struct produced by [`crate::query::TaskQueryBuilder`].
213    ///
214    /// All filter fields are optional and combined with AND semantics in SQL.
215    /// Tag and search-query filters are applied in Rust after the SQL query returns
216    /// (Things 3 stores tags as a BLOB). When those post-filters are active,
217    /// `LIMIT`/`OFFSET` is also applied in Rust so pagination counts only
218    /// matching rows; without post-filters it is applied in SQL for efficiency.
219    ///
220    /// Tag matching via `filters.tags` is case-sensitive.
221    ///
222    /// Filtering by [`TaskStatus::Trashed`] queries rows where `trashed = 1`
223    /// rather than adding a `status` condition, matching Things 3's soft-delete
224    /// semantics (trashed rows keep their original status value).
225    ///
226    /// # Errors
227    ///
228    /// Returns an error if the database query fails or task data cannot be mapped.
229    #[cfg(any(feature = "advanced-queries", feature = "batch-operations"))]
230    pub async fn query_tasks(&self, filters: &TaskFilters) -> ThingsResult<Vec<Task>> {
231        self.query_tasks_inner(filters, None).await
232    }
233
234    /// Internal query path that optionally applies a cursor WHERE clause.
235    ///
236    /// `after` is `(seconds_since_unix_epoch, uuid)` of the last-returned
237    /// task. When `Some`, an additional `WHERE` clause restricts results to
238    /// rows strictly older than that anchor in the canonical
239    /// `CAST(creationDate AS INTEGER) DESC, uuid DESC` ordering.
240    ///
241    /// Gated on either `advanced-queries` or `batch-operations` because both
242    /// public surfaces (`query_tasks` and `execute_paged`) share this engine.
243    #[cfg(any(feature = "advanced-queries", feature = "batch-operations"))]
244    pub(crate) async fn query_tasks_inner(
245        &self,
246        filters: &TaskFilters,
247        after: Option<(i64, Uuid)>,
248    ) -> ThingsResult<Vec<Task>> {
249        const COLS: &str = "uuid, title, type, status, notes, startDate, deadline, stopDate, \
250                            creationDate, userModificationDate, project, area, heading, \
251                            (SELECT GROUP_CONCAT(tg.title, char(31)) \
252                               FROM TMTaskTag tt \
253                               JOIN TMTag tg ON tg.uuid = tt.tags \
254                              WHERE tt.tasks = TMTask.uuid) AS tags_csv";
255
256        // Things 3 soft-deletes by setting trashed = 1; the status column is unchanged.
257        // Requesting Trashed means "show trashed rows", not a status = 3 predicate.
258        let trashed_val = i32::from(matches!(filters.status, Some(TaskStatus::Trashed)));
259        let mut conditions: Vec<String> = vec![format!("trashed = {trashed_val}")];
260
261        if let Some(status) = filters.status {
262            let n = match status {
263                TaskStatus::Incomplete => Some(0),
264                TaskStatus::Canceled => Some(2),
265                TaskStatus::Completed => Some(3),
266                TaskStatus::Trashed => None, // handled via trashed = 1 above
267            };
268            if let Some(n) = n {
269                conditions.push(format!("status = {n}"));
270            }
271        }
272
273        if let Some(task_type) = filters.task_type {
274            let n = match task_type {
275                TaskType::Todo => 0,
276                TaskType::Project => 1,
277                TaskType::Heading => 2,
278                TaskType::Area => 3,
279            };
280            conditions.push(format!("type = {n}"));
281        }
282
283        if let Some(ref uuid) = filters.project_uuid {
284            conditions.push(format!("project = '{uuid}'"));
285        }
286
287        if let Some(ref uuid) = filters.area_uuid {
288            conditions.push(format!("area = '{uuid}'"));
289        }
290
291        if let Some(from) = filters.start_date_from {
292            conditions.push(format!(
293                "startDate >= {}",
294                naive_date_to_things_timestamp(from)
295            ));
296        }
297        if let Some(to) = filters.start_date_to {
298            conditions.push(format!(
299                "startDate <= {}",
300                naive_date_to_things_timestamp(to)
301            ));
302        }
303
304        if let Some(from) = filters.deadline_from {
305            conditions.push(format!(
306                "deadline >= {}",
307                naive_date_to_things_timestamp(from)
308            ));
309        }
310        if let Some(to) = filters.deadline_to {
311            conditions.push(format!(
312                "deadline <= {}",
313                naive_date_to_things_timestamp(to)
314            ));
315        }
316
317        if let Some((after_seconds, _)) = after {
318            // Strictly less than the cursor in (truncated_seconds DESC, uuid DESC)
319            // ordering — i.e. older second, or same second with smaller uuid.
320            // Casting to INTEGER matches the precision of `Task::created`,
321            // which is reconstructed at second precision when reading rows.
322            // UUID is bound as a parameter (?) rather than interpolated for
323            // consistency with the rest of the codebase's query practices.
324            conditions.push(format!(
325                "(CAST(creationDate AS INTEGER) < {after_seconds} \
326                 OR (CAST(creationDate AS INTEGER) = {after_seconds} AND uuid < ?))"
327            ));
328        }
329
330        let where_clause = conditions.join(" AND ");
331        // ORDER BY uses the truncated-second value so it agrees with the
332        // cursor pagination logic (which compares at second precision because
333        // `Task::created` is reconstructed at second precision). `uuid DESC` is
334        // a deterministic tiebreak within the same second.
335        let mut sql = format!(
336            "SELECT {COLS} FROM TMTask WHERE {where_clause} \
337             ORDER BY CAST(creationDate AS INTEGER) DESC, uuid DESC"
338        );
339
340        // When tags or search_query are active, LIMIT/OFFSET must be applied in Rust
341        // after post-filtering, because SQL LIMIT would count non-matching rows.
342        let has_post_filters =
343            filters.tags.as_ref().is_some_and(|t| !t.is_empty()) || filters.search_query.is_some();
344
345        if !has_post_filters {
346            match (filters.limit, filters.offset) {
347                (Some(limit), Some(offset)) => {
348                    sql.push_str(&format!(" LIMIT {limit} OFFSET {offset}"));
349                }
350                (Some(limit), None) => {
351                    sql.push_str(&format!(" LIMIT {limit}"));
352                }
353                (None, Some(offset)) => {
354                    // SQLite requires LIMIT when OFFSET is used; -1 means unlimited
355                    sql.push_str(&format!(" LIMIT -1 OFFSET {offset}"));
356                }
357                (None, None) => {}
358            }
359        }
360
361        let rows = if let Some((_, after_uuid)) = after {
362            sqlx::query(&sql)
363                .bind(after_uuid.to_string())
364                .fetch_all(&self.pool)
365                .await
366        } else {
367            sqlx::query(&sql).fetch_all(&self.pool).await
368        }
369        .map_err(|e| ThingsError::unknown(format!("Failed to query tasks: {e}")))?;
370
371        let mut tasks = rows
372            .iter()
373            .map(map_task_row)
374            .collect::<ThingsResult<Vec<Task>>>()?;
375
376        if let Some(ref filter_tags) = filters.tags {
377            if !filter_tags.is_empty() {
378                tasks.retain(|task| filter_tags.iter().all(|f| task.tags.contains(f)));
379            }
380        }
381
382        if let Some(ref q) = filters.search_query {
383            let q_lower = q.to_lowercase();
384            tasks.retain(|task| {
385                task.title.to_lowercase().contains(&q_lower)
386                    || task
387                        .notes
388                        .as_deref()
389                        .unwrap_or("")
390                        .to_lowercase()
391                        .contains(&q_lower)
392            });
393        }
394
395        if has_post_filters {
396            let offset = filters.offset.unwrap_or(0);
397            tasks = tasks.into_iter().skip(offset).collect();
398            if let Some(limit) = filters.limit {
399                tasks.truncate(limit);
400            }
401        }
402
403        Ok(tasks)
404    }
405
406    /// Search completed tasks in the logbook
407    ///
408    /// Returns completed tasks matching the provided filters.
409    /// All filters are optional and can be combined.
410    ///
411    /// # Parameters
412    ///
413    /// - `search_text`: Search in task titles and notes (case-insensitive)
414    /// - `from_date`: Start date for completion date range
415    /// - `to_date`: End date for completion date range
416    /// - `project_uuid`: Filter by project UUID
417    /// - `area_uuid`: Filter by area UUID
418    /// - `tags`: Filter by tags (all tags must match)
419    /// - `limit`: Maximum number of results (default: 50)
420    ///
421    /// # Errors
422    ///
423    /// Returns an error if the database query fails or if task data is invalid
424    #[allow(clippy::too_many_arguments)]
425    #[instrument(skip(self))]
426    pub async fn search_logbook(
427        &self,
428        search_text: Option<String>,
429        from_date: Option<NaiveDate>,
430        to_date: Option<NaiveDate>,
431        project_uuid: Option<ThingsId>,
432        area_uuid: Option<ThingsId>,
433        tags: Option<Vec<String>>,
434        limit: Option<u32>,
435        offset: Option<u32>,
436    ) -> ThingsResult<Vec<Task>> {
437        // Apply limit and offset
438        let result_limit = limit.unwrap_or(50).min(500);
439        let result_offset = offset.unwrap_or(0);
440
441        // Build and execute query based on filters
442        // type = 0 (Todo) is intentional here: headings (type=2) have no stopDate and
443        // cannot appear in a stop-date-ordered logbook.
444        let rows = if let Some(ref text) = search_text {
445            let pattern = format!("%{text}%");
446            let mut q = String::from(
447                "SELECT uuid, title, status, type, startDate, deadline, stopDate, project, area, heading, notes, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv, creationDate, userModificationDate FROM TMTask WHERE status = 3 AND trashed = 0 AND type = 0",
448            );
449            q.push_str(" AND (title LIKE ? OR notes LIKE ?)");
450
451            if let Some(date) = from_date {
452                // stopDate is stored as Unix timestamp (seconds since 1970-01-01)
453                let date_time = date.and_hms_opt(0, 0, 0).unwrap().and_utc();
454                let timestamp = date_time.timestamp() as f64;
455                q.push_str(&format!(" AND stopDate >= {}", timestamp));
456            }
457
458            if let Some(date) = to_date {
459                // Include tasks completed on to_date by adding 1 day
460                let end_date = date + chrono::Duration::days(1);
461                let date_time = end_date.and_hms_opt(0, 0, 0).unwrap().and_utc();
462                let timestamp = date_time.timestamp() as f64;
463                q.push_str(&format!(" AND stopDate < {}", timestamp));
464            }
465
466            if let Some(ref id) = project_uuid {
467                q.push_str(&format!(" AND project = '{}'", id));
468            }
469
470            if let Some(ref id) = area_uuid {
471                q.push_str(&format!(" AND area = '{}'", id));
472            }
473
474            q.push_str(&format!(
475                " ORDER BY stopDate DESC LIMIT {result_limit} OFFSET {result_offset}"
476            ));
477
478            sqlx::query(&q)
479                .bind(&pattern)
480                .bind(&pattern)
481                .fetch_all(&self.pool)
482                .await
483                .map_err(|e| ThingsError::unknown(format!("Failed to search logbook: {e}")))?
484        } else {
485            let mut q = String::from(
486                "SELECT uuid, title, status, type, startDate, deadline, stopDate, project, area, heading, notes, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv, creationDate, userModificationDate FROM TMTask WHERE status = 3 AND trashed = 0 AND type = 0",
487            );
488
489            if let Some(date) = from_date {
490                // stopDate is stored as Unix timestamp (seconds since 1970-01-01)
491                let date_time = date.and_hms_opt(0, 0, 0).unwrap().and_utc();
492                let timestamp = date_time.timestamp() as f64;
493                q.push_str(&format!(" AND stopDate >= {}", timestamp));
494            }
495
496            if let Some(date) = to_date {
497                // Include tasks completed on to_date by adding 1 day
498                let end_date = date + chrono::Duration::days(1);
499                let date_time = end_date.and_hms_opt(0, 0, 0).unwrap().and_utc();
500                let timestamp = date_time.timestamp() as f64;
501                q.push_str(&format!(" AND stopDate < {}", timestamp));
502            }
503
504            if let Some(ref id) = project_uuid {
505                q.push_str(&format!(" AND project = '{}'", id));
506            }
507
508            if let Some(ref id) = area_uuid {
509                q.push_str(&format!(" AND area = '{}'", id));
510            }
511
512            q.push_str(&format!(
513                " ORDER BY stopDate DESC LIMIT {result_limit} OFFSET {result_offset}"
514            ));
515
516            sqlx::query(&q)
517                .fetch_all(&self.pool)
518                .await
519                .map_err(|e| ThingsError::unknown(format!("Failed to search logbook: {e}")))?
520        };
521
522        // Filter by tags if provided
523        let mut tasks = rows
524            .iter()
525            .map(map_task_row)
526            .collect::<ThingsResult<Vec<Task>>>()?;
527
528        if let Some(ref filter_tags) = tags {
529            if !filter_tags.is_empty() {
530                tasks.retain(|task| {
531                    // Check if task has all required tags
532                    filter_tags
533                        .iter()
534                        .all(|filter_tag| task.tags.contains(filter_tag))
535                });
536            }
537        }
538
539        debug!("Found {} completed tasks in logbook", tasks.len());
540        Ok(tasks)
541    }
542
543    /// Get inbox tasks (incomplete tasks without project)
544    ///
545    /// # Errors
546    ///
547    /// Returns an error if the database query fails or if task data is invalid
548    #[instrument(skip(self))]
549    pub async fn get_inbox(&self, limit: Option<usize>) -> ThingsResult<Vec<Task>> {
550        let query = if let Some(limit) = limit {
551            format!("SELECT uuid, title, type, status, notes, startDate, deadline, stopDate, creationDate, userModificationDate, project, area, heading, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv FROM TMTask WHERE type IN (0, 2) AND status = 0 AND project IS NULL AND trashed = 0 ORDER BY creationDate DESC LIMIT {limit}")
552        } else {
553            "SELECT uuid, title, type, status, notes, startDate, deadline, stopDate, creationDate, userModificationDate, project, area, heading, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv FROM TMTask WHERE type IN (0, 2) AND status = 0 AND project IS NULL AND trashed = 0 ORDER BY creationDate DESC"
554                .to_string()
555        };
556
557        let rows = sqlx::query(&query)
558            .fetch_all(&self.pool)
559            .await
560            .map_err(|e| ThingsError::unknown(format!("Failed to fetch inbox tasks: {e}")))?;
561
562        let tasks = rows
563            .iter()
564            .map(map_task_row)
565            .collect::<ThingsResult<Vec<Task>>>()?;
566
567        Ok(tasks)
568    }
569
570    /// Get today's tasks (incomplete tasks due today or started today)
571    ///
572    /// # Errors
573    ///
574    /// Returns an error if the database query fails or if task data is invalid
575    ///
576    /// # Panics
577    ///
578    /// Panics if the current date cannot be converted to a valid time with hours, minutes, and seconds
579    #[instrument(skip(self))]
580    pub async fn get_today(&self, limit: Option<usize>) -> ThingsResult<Vec<Task>> {
581        // Things 3 uses the `todayIndex` column to mark tasks that appear in "Today"
582        // A task is in "Today" if todayIndex IS NOT NULL AND todayIndex != 0
583        let query = if let Some(limit) = limit {
584            format!(
585                "SELECT uuid, title, type, status, notes, startDate, deadline, stopDate, creationDate, userModificationDate, project, area, heading, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv FROM TMTask WHERE status = 0 AND todayIndex IS NOT NULL AND todayIndex != 0 AND trashed = 0 ORDER BY todayIndex ASC LIMIT {limit}"
586            )
587        } else {
588            "SELECT uuid, title, type, status, notes, startDate, deadline, stopDate, creationDate, userModificationDate, project, area, heading, (SELECT GROUP_CONCAT(tg.title, char(31)) FROM TMTaskTag tt JOIN TMTag tg ON tg.uuid = tt.tags WHERE tt.tasks = TMTask.uuid) AS tags_csv FROM TMTask WHERE status = 0 AND todayIndex IS NOT NULL AND todayIndex != 0 AND trashed = 0 ORDER BY todayIndex ASC".to_string()
589        };
590
591        let rows = sqlx::query(&query)
592            .fetch_all(&self.pool)
593            .await
594            .map_err(|e| ThingsError::unknown(format!("Failed to fetch today's tasks: {e}")))?;
595
596        let tasks = rows
597            .iter()
598            .map(map_task_row)
599            .collect::<ThingsResult<Vec<Task>>>()?;
600
601        Ok(tasks)
602    }
603
604    /// Get a task by its UUID
605    ///
606    /// # Errors
607    ///
608    /// Returns an error if the task does not exist or if the database query fails
609    #[instrument(skip(self))]
610    pub async fn get_task_by_uuid(&self, id: &ThingsId) -> ThingsResult<Option<Task>> {
611        let row = sqlx::query(
612            r"
613            SELECT
614                uuid, title, status, type,
615                startDate, deadline, stopDate,
616                project, area, heading,
617                notes, (SELECT GROUP_CONCAT(tg.title, char(31))
618                          FROM TMTaskTag tt
619                          JOIN TMTag tg ON tg.uuid = tt.tags
620                         WHERE tt.tasks = TMTask.uuid) AS tags_csv,
621                creationDate, userModificationDate,
622                trashed
623            FROM TMTask
624            WHERE uuid = ?
625            ",
626        )
627        .bind(id.as_str())
628        .fetch_optional(&self.pool)
629        .await
630        .map_err(|e| ThingsError::unknown(format!("Failed to fetch task: {e}")))?;
631
632        if let Some(row) = row {
633            // Check if trashed
634            let trashed: i64 = row.get("trashed");
635            if trashed == 1 {
636                return Ok(None); // Return None for trashed tasks
637            }
638
639            // Use the centralized mapper
640            let task = map_task_row(&row)?;
641            Ok(Some(task))
642        } else {
643            Ok(None)
644        }
645    }
646}
things3_core/database/queries/tasks.rs

things3_core/database/queries/
tasks.rs
