docbox_database/models/
file.rs

1use chrono::{DateTime, Utc};
2use serde::Serialize;
3use sqlx::{postgres::PgRow, prelude::FromRow};
4use utoipa::ToSchema;
5use uuid::Uuid;
6
7use super::{
8    document_box::DocumentBoxScopeRaw,
9    folder::{FolderId, FolderPathSegment},
10    user::{User, UserId},
11};
12use crate::{
13    DbExecutor, DbResult,
14    models::folder::{WithFullPath, WithFullPathScope},
15};
16
17pub type FileId = Uuid;
18
19#[derive(Debug, Clone, FromRow, Serialize)]
20pub struct File {
21    /// Unique identifier for the file
22    pub id: FileId,
23    /// Name of the file
24    pub name: String,
25    /// Mime type of the file content
26    pub mime: String,
27    /// Parent folder ID
28    pub folder_id: FolderId,
29    /// Hash of the file bytes stored in S3
30    pub hash: String,
31    /// Size of the file in bytes
32    pub size: i32,
33    /// Whether the file was determined to be encrypted when processing
34    pub encrypted: bool,
35    /// Whether the file is marked as pinned
36    pub pinned: bool,
37    /// S3 key pointing to the file
38    #[serde(skip)]
39    pub file_key: String,
40    /// When the file was created
41    pub created_at: DateTime<Utc>,
42    /// User who created the file
43    pub created_by: Option<UserId>,
44    /// Optional parent file ID if the file is a child of
45    /// some other file (i.e attachment for an email file)
46    pub parent_id: Option<Uuid>,
47}
48
49#[derive(Debug, FromRow, Serialize)]
50pub struct FileWithScope {
51    #[sqlx(flatten)]
52    pub file: File,
53    pub scope: String,
54}
55
56/// File with the resolved creator and last modified data
57#[derive(Debug, Clone, FromRow, Serialize, ToSchema)]
58pub struct FileWithExtra {
59    /// Unique identifier for the file
60    #[schema(value_type = Uuid)]
61    pub id: FileId,
62    /// Name of the file
63    pub name: String,
64    /// Mime type of the file content
65    pub mime: String,
66    /// Parent folder ID
67    #[schema(value_type = Uuid)]
68    pub folder_id: FolderId,
69    /// Hash of the file bytes stored in S3
70    pub hash: String,
71    /// Size of the file in bytes
72    pub size: i32,
73    /// Whether the file was determined to be encrypted when processing
74    pub encrypted: bool,
75    /// Whether the file is marked as pinned
76    pub pinned: bool,
77    /// When the file was created
78    pub created_at: DateTime<Utc>,
79    /// User who created the file
80    #[sqlx(flatten)]
81    #[schema(nullable, value_type = User)]
82    pub created_by: CreatedByUser,
83    /// Last time the file was modified
84    pub last_modified_at: Option<DateTime<Utc>>,
85    /// User who last modified the file
86    #[sqlx(flatten)]
87    #[schema(nullable, value_type = User)]
88    pub last_modified_by: LastModifiedByUser,
89    /// Optional parent file if the file is a child
90    #[schema(value_type = Option<Uuid>)]
91    pub parent_id: Option<FileId>,
92}
93
94/// File with extra with an additional resolved full path
95#[derive(Debug, FromRow, Serialize, ToSchema)]
96pub struct ResolvedFileWithExtra {
97    #[serde(flatten)]
98    #[sqlx(flatten)]
99    pub file: FileWithExtra,
100    #[sqlx(json)]
101    pub full_path: Vec<FolderPathSegment>,
102}
103
104/// Wrapper type for extracting a [User] that was joined
105/// from another table where the fields are prefixed with "cb_"
106#[derive(Debug, Clone, Serialize)]
107#[serde(transparent)]
108pub struct CreatedByUser(pub Option<User>);
109
110impl<'r> FromRow<'r, PgRow> for CreatedByUser {
111    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
112        use sqlx::Row;
113
114        let id: Option<UserId> = row.try_get("cb_id")?;
115        if let Some(id) = id {
116            let name: Option<String> = row.try_get("cb_name")?;
117            let image_id: Option<String> = row.try_get("cb_image_id")?;
118            return Ok(CreatedByUser(Some(User { id, name, image_id })));
119        }
120
121        Ok(CreatedByUser(None))
122    }
123}
124
125/// Wrapper type for extracting a [User] that was joined
126/// from another table where the fields are prefixed with "lmb_"
127#[derive(Debug, Clone, Serialize)]
128#[serde(transparent)]
129pub struct LastModifiedByUser(pub Option<User>);
130
131impl<'r> FromRow<'r, PgRow> for LastModifiedByUser {
132    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
133        use sqlx::Row;
134
135        let id: Option<UserId> = row.try_get("lmb_id")?;
136        if let Some(id) = id {
137            let name: Option<String> = row.try_get("lmb_name")?;
138            let image_id: Option<String> = row.try_get("lmb_image_id")?;
139            return Ok(LastModifiedByUser(Some(User { id, name, image_id })));
140        }
141
142        Ok(LastModifiedByUser(None))
143    }
144}
145
146#[derive(Debug, Serialize)]
147pub struct FileIdWithScope {
148    pub file_id: FileId,
149    pub scope: DocumentBoxScopeRaw,
150}
151
152pub struct CreateFile {
153    /// Fixed file ID to use instead of a randomly
154    /// generated file ID
155    pub fixed_id: Option<FileId>,
156    /// Optional parent file if the file was created
157    /// as the result of another file (i.e. email attachments)
158    pub parent_id: Option<FileId>,
159
160    pub name: String,
161    pub mime: String,
162    pub folder_id: FolderId,
163    pub hash: String,
164    pub size: i32,
165    pub file_key: String,
166    pub created_by: Option<UserId>,
167    pub encrypted: bool,
168}
169
170impl File {
171    pub async fn all(
172        db: impl DbExecutor<'_>,
173        offset: u64,
174        page_size: u64,
175    ) -> DbResult<Vec<FileWithScope>> {
176        sqlx::query_as(
177            r#"
178            SELECT 
179            "file".*,
180            "folder"."document_box" AS "scope" 
181            FROM "docbox_files" "file"
182            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id" 
183            ORDER BY "created_at" ASC
184            OFFSET $1
185            LIMIT $2
186        "#,
187        )
188        .bind(offset as i64)
189        .bind(page_size as i64)
190        .fetch_all(db)
191        .await
192    }
193
194    pub async fn all_by_mime(
195        db: impl DbExecutor<'_>,
196        mime: &str,
197        offset: u64,
198        page_size: u64,
199    ) -> DbResult<Vec<FileWithScope>> {
200        sqlx::query_as(
201            r#"
202            SELECT 
203            "file".*,
204            "folder"."document_box" AS "scope" 
205            FROM "docbox_files" "file"
206            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id" 
207            WHERE "file"."mime" = $1
208            ORDER BY "created_at" ASC
209            OFFSET $2
210            LIMIT $3
211        "#,
212        )
213        .bind(mime)
214        .bind(offset as i64)
215        .bind(page_size as i64)
216        .fetch_all(db)
217        .await
218    }
219
220    pub async fn move_to_folder(
221        mut self,
222        db: impl DbExecutor<'_>,
223        folder_id: FolderId,
224    ) -> DbResult<File> {
225        sqlx::query(r#"UPDATE "docbox_files" SET "folder_id" = $1 WHERE "id" = $2"#)
226            .bind(folder_id)
227            .bind(self.id)
228            .execute(db)
229            .await?;
230
231        self.folder_id = folder_id;
232
233        Ok(self)
234    }
235
236    pub async fn rename(mut self, db: impl DbExecutor<'_>, name: String) -> DbResult<File> {
237        sqlx::query(r#"UPDATE "docbox_files" SET "name" = $1 WHERE "id" = $2"#)
238            .bind(name.as_str())
239            .bind(self.id)
240            .execute(db)
241            .await?;
242
243        self.name = name;
244
245        Ok(self)
246    }
247
248    /// Updates the pinned state of the file
249    pub async fn set_pinned(mut self, db: impl DbExecutor<'_>, pinned: bool) -> DbResult<File> {
250        sqlx::query(r#"UPDATE "docbox_files" SET "pinned" = $1 WHERE "id" = $2"#)
251            .bind(pinned)
252            .bind(self.id)
253            .execute(db)
254            .await?;
255
256        self.pinned = pinned;
257
258        Ok(self)
259    }
260
261    /// Updates the encryption state of the file
262    pub async fn set_encrypted(
263        mut self,
264        db: impl DbExecutor<'_>,
265        encrypted: bool,
266    ) -> DbResult<File> {
267        sqlx::query(r#"UPDATE "docbox_files" SET "encrypted" = $1 WHERE "id" = $2"#)
268            .bind(encrypted)
269            .bind(self.id)
270            .execute(db)
271            .await?;
272
273        self.encrypted = encrypted;
274
275        Ok(self)
276    }
277
278    /// Updates the mime type of a file
279    pub async fn set_mime(mut self, db: impl DbExecutor<'_>, mime: String) -> DbResult<File> {
280        sqlx::query(r#"UPDATE "docbox_files" SET "mime" = $1 WHERE "id" = $2"#)
281            .bind(&mime)
282            .bind(self.id)
283            .execute(db)
284            .await?;
285
286        self.mime = mime;
287
288        Ok(self)
289    }
290
291    pub async fn create(
292        db: impl DbExecutor<'_>,
293        CreateFile {
294            fixed_id,
295            parent_id,
296            name,
297            mime,
298            folder_id,
299            hash,
300            size,
301            file_key,
302            created_by,
303            encrypted,
304        }: CreateFile,
305    ) -> DbResult<File> {
306        let id = fixed_id.unwrap_or_else(Uuid::new_v4);
307        let created_at = Utc::now();
308
309        sqlx::query(
310            r#"INSERT INTO "docbox_files" (
311                    "id", "name", "mime", "folder_id", "hash", "size",
312                    "encrypted", "file_key", "created_by", "created_at",
313                    "parent_id"
314                )
315                VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
316                "#,
317        )
318        .bind(id)
319        .bind(name.as_str())
320        .bind(mime.as_str())
321        .bind(folder_id)
322        .bind(hash.as_str())
323        .bind(size)
324        .bind(encrypted)
325        .bind(file_key.as_str())
326        .bind(created_by.as_ref())
327        .bind(created_at)
328        .bind(parent_id)
329        .execute(db)
330        .await?;
331
332        Ok(File {
333            id,
334            name,
335            mime,
336            folder_id,
337            hash,
338            size,
339            encrypted,
340            file_key,
341            created_by,
342            created_at,
343            parent_id,
344            pinned: false,
345        })
346    }
347
348    pub async fn all_convertable_paged(
349        db: impl DbExecutor<'_>,
350        offset: u64,
351        page_size: u64,
352        convertable_formats: Vec<&str>,
353    ) -> DbResult<Vec<FileWithScope>> {
354        sqlx::query_as(
355            r#"
356            SELECT 
357                "file".*,
358                "folder"."document_box" AS "scope"
359            FROM "docbox_files" AS "file"
360            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
361            WHERE "mime" IS IN $1 AND "file"."encrypted" = FALSE
362            ORDER BY "file"."created_at" ASC   
363            OFFSET $2
364            LIMIT $3
365        "#,
366        )
367        .bind(convertable_formats)
368        .bind(offset as i64)
369        .bind(page_size as i64)
370        .fetch_all(db)
371        .await
372    }
373
374    /// Finds a specific file using its full path scope -> folder -> file
375    pub async fn find(
376        db: impl DbExecutor<'_>,
377        scope: &DocumentBoxScopeRaw,
378        file_id: FileId,
379    ) -> DbResult<Option<File>> {
380        sqlx::query_as(
381            r#"
382            SELECT "file".*
383            FROM "docbox_files" AS "file"
384            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
385            WHERE "file"."id" = $1 AND "folder"."document_box" = $2
386        "#,
387        )
388        .bind(file_id)
389        .bind(scope)
390        .fetch_optional(db)
391        .await
392    }
393
394    /// Collects the IDs and names of all parent folders of the
395    /// provided folder
396    pub async fn resolve_path(
397        db: impl DbExecutor<'_>,
398        file_id: FileId,
399    ) -> DbResult<Vec<FolderPathSegment>> {
400        sqlx::query_as(
401            r#"
402            WITH RECURSIVE "folder_hierarchy" AS (
403                SELECT "id", "name", "folder_id", 0 AS "depth"
404                FROM "docbox_files" 
405                WHERE "docbox_files"."id" = $1 
406                UNION ALL (
407                    SELECT 
408                        "folder"."id", 
409                        "folder"."name", 
410                        "folder"."folder_id", 
411                        "folder_hierarchy"."depth" + 1 as "depth"
412                    FROM "docbox_folders" AS "folder" 
413                    INNER JOIN "folder_hierarchy" ON "folder"."id" = "folder_hierarchy"."folder_id"
414                )
415            ) 
416            CYCLE "id" SET "looped" USING "traversal_path" 
417            SELECT "folder_hierarchy"."id", "folder_hierarchy"."name" 
418            FROM "folder_hierarchy" 
419            WHERE "folder_hierarchy"."id" <> $1
420            ORDER BY "folder_hierarchy"."depth" DESC
421        "#,
422        )
423        .bind(file_id)
424        .fetch_all(db)
425        .await
426    }
427
428    pub async fn find_by_parent(
429        db: impl DbExecutor<'_>,
430        parent_id: FolderId,
431    ) -> DbResult<Vec<File>> {
432        sqlx::query_as(r#"SELECT * FROM "docbox_files" WHERE "folder_id" = $1"#)
433            .bind(parent_id)
434            .fetch_all(db)
435            .await
436    }
437
438    /// Deletes the file
439    pub async fn delete(&self, db: impl DbExecutor<'_>) -> DbResult<()> {
440        sqlx::query(r#"DELETE FROM "docbox_files" WHERE "id" = $1"#)
441            .bind(self.id)
442            .execute(db)
443            .await?;
444        Ok(())
445    }
446
447    /// Finds a collection of files that are all within the same document box, resolves
448    /// both the files themselves and the folder path to traverse to get to each file
449    pub async fn resolve_with_extra(
450        db: impl DbExecutor<'_>,
451        scope: &DocumentBoxScopeRaw,
452        file_ids: Vec<Uuid>,
453    ) -> DbResult<Vec<WithFullPath<FileWithExtra>>> {
454        if file_ids.is_empty() {
455            return Ok(Vec::new());
456        }
457
458        sqlx::query_as(
459            r#"
460        -- Recursively resolve the file paths for each file creating a JSON array for the path
461        WITH RECURSIVE "folder_hierarchy" AS (
462            SELECT
463                "f"."id" AS "file_id",
464                "folder"."id" AS "folder_id",
465                "folder"."name" AS "folder_name",
466                "folder"."folder_id" AS "parent_folder_id",
467                0 AS "depth",
468                jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
469            FROM "docbox_files" "f"
470            JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
471            WHERE "f"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2
472            UNION ALL
473            SELECT
474                "fh"."file_id",
475                "parent"."id",
476                "parent"."name",
477                "parent"."folder_id",
478                "fh"."depth" + 1,
479                jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
480            FROM "folder_hierarchy" "fh"
481            JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
482        ),
483        "folder_paths" AS (
484            SELECT "file_id", "path", ROW_NUMBER() OVER (PARTITION BY "file_id" ORDER BY "depth" DESC) AS "rn"
485            FROM "folder_hierarchy"
486        )
487        SELECT 
488            -- File itself 
489            "file".*,
490            -- Creator user details
491            "cu"."id" AS "cb_id", 
492            "cu"."name" AS "cb_name", 
493            "cu"."image_id" AS "cb_image_id", 
494            -- Last modified date
495            "ehl"."created_at" AS "last_modified_at", 
496            -- Last modified user details
497            "mu"."id" AS "lmb_id",  
498            "mu"."name" AS "lmb_name", 
499            "mu"."image_id" AS "lmb_image_id" ,
500            -- File path from path lookup
501            "fp"."path" AS "full_path" 
502        FROM "docbox_files" AS "file"
503        -- Join on the creator
504        LEFT JOIN "docbox_users" AS "cu" 
505            ON "file"."created_by" = "cu"."id" 
506        -- Join on the parent folder
507        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
508        -- Join on the edit history (Latest only)
509        LEFT JOIN (
510            -- Get the latest edit history entry
511            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at" 
512            FROM "docbox_edit_history"
513            ORDER BY "file_id", "created_at" DESC 
514        ) AS "ehl" ON "file"."id" = "ehl"."file_id" 
515        -- Join on the editor history latest edit user
516        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id" 
517        -- Join on the resolved folder path
518        LEFT JOIN "folder_paths" "fp" ON "file".id = "fp"."file_id" AND "fp".rn = 1
519        WHERE "file"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2"#,
520        )
521        .bind(file_ids)
522        .bind(scope)
523        .fetch_all(db)
524        .await
525    }
526
527    /// Finds a collection of files that are within various document box scopes, resolves
528    /// both the files themselves and the folder path to traverse to get to each file
529    pub async fn resolve_with_extra_mixed_scopes(
530        db: impl DbExecutor<'_>,
531        files_scope_with_id: Vec<(DocumentBoxScopeRaw, FileId)>,
532    ) -> DbResult<Vec<WithFullPathScope<FileWithExtra>>> {
533        if files_scope_with_id.is_empty() {
534            return Ok(Vec::new());
535        }
536
537        let (scopes, file_ids): (Vec<String>, Vec<FileId>) =
538            files_scope_with_id.into_iter().unzip();
539
540        sqlx::query_as(
541            r#"
542        -- Recursively resolve the file paths for each file creating a JSON array for the path
543        WITH RECURSIVE 
544            "input_files" AS (
545                SELECT file_id, document_box
546                FROM UNNEST($1::text[], $2::uuid[]) AS t(document_box, file_id)
547            ),
548            "folder_hierarchy" AS (
549                SELECT
550                    "f"."id" AS "file_id",
551                    "folder"."id" AS "folder_id",
552                    "folder"."name" AS "folder_name",
553                    "folder"."folder_id" AS "parent_folder_id",
554                    0 AS "depth",
555                    jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
556                FROM "docbox_files" "f"
557                JOIN "input_files" "i" ON "f"."id" = "i"."file_id"
558                JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
559                WHERE "folder"."document_box" = "i"."document_box"
560                UNION ALL
561                SELECT
562                    "fh"."file_id",
563                    "parent"."id",
564                    "parent"."name",
565                    "parent"."folder_id",
566                    "fh"."depth" + 1,
567                    jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
568                FROM "folder_hierarchy" "fh"
569                JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
570            ),
571            "folder_paths" AS (
572                SELECT "file_id", "path", ROW_NUMBER() OVER (PARTITION BY "file_id" ORDER BY "depth" DESC) AS "rn"
573                FROM "folder_hierarchy"
574            )
575        SELECT 
576            -- File itself 
577            "file".*,
578            -- Creator user details
579            "cu"."id" AS "cb_id", 
580            "cu"."name" AS "cb_name", 
581            "cu"."image_id" AS "cb_image_id", 
582            -- Last modified date
583            "ehl"."created_at" AS "last_modified_at", 
584            -- Last modified user details
585            "mu"."id" AS "lmb_id",  
586            "mu"."name" AS "lmb_name", 
587            "mu"."image_id" AS "lmb_image_id" ,
588            -- File path from path lookup
589            "fp"."path" AS "full_path",
590            -- Include document box in response
591            "folder"."document_box" AS "document_box" 
592        FROM "docbox_files" AS "file"
593        -- Join on the creator
594        LEFT JOIN "docbox_users" AS "cu" 
595            ON "file"."created_by" = "cu"."id" 
596        -- Join on the parent folder
597        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
598        -- Join on the edit history (Latest only)
599        LEFT JOIN (
600            -- Get the latest edit history entry
601            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at" 
602            FROM "docbox_edit_history"
603            ORDER BY "file_id", "created_at" DESC 
604        ) AS "ehl" ON "file"."id" = "ehl"."file_id" 
605        -- Join on the editor history latest edit user
606        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id" 
607        -- Join on the resolved folder path
608        LEFT JOIN "folder_paths" "fp" ON "file".id = "fp"."file_id" AND "fp".rn = 1
609        -- Join on the input files for filtering
610        JOIN "input_files" "i" ON "file"."id" = "i"."file_id"
611        -- Ensure correct document box
612        WHERE "folder"."document_box" = "i"."document_box""#,
613        )
614        .bind(scopes)
615        .bind(file_ids)
616        .fetch_all(db)
617        .await
618    }
619
620    /// Finds a specific file using its full path scope -> folder -> file
621    /// fetching the additional details about the file like the creator and
622    /// last modified
623    pub async fn find_with_extra(
624        db: impl DbExecutor<'_>,
625        scope: &DocumentBoxScopeRaw,
626        file_id: FileId,
627    ) -> DbResult<Option<FileWithExtra>> {
628        sqlx::query_as(
629            r#"
630        SELECT 
631            -- File itself 
632            "file".*,
633            -- Creator user details
634            "cu"."id" AS "cb_id", 
635            "cu"."name" AS "cb_name", 
636            "cu"."image_id" AS "cb_image_id", 
637            -- Last modified date
638            "ehl"."created_at" AS "last_modified_at", 
639            -- Last modified user details
640            "mu"."id" AS "lmb_id",  
641            "mu"."name" AS "lmb_name", 
642            "mu"."image_id" AS "lmb_image_id" 
643        FROM "docbox_files" AS "file"
644        -- Join on the creator
645        LEFT JOIN "docbox_users" AS "cu" 
646            ON "file"."created_by" = "cu"."id" 
647        -- Join on the parent folder
648        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
649        -- Join on the edit history (Latest only)
650        LEFT JOIN (
651            -- Get the latest edit history entry
652            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at" 
653            FROM "docbox_edit_history"
654            ORDER BY "file_id", "created_at" DESC 
655        ) AS "ehl" ON "file"."id" = "ehl"."file_id" 
656        -- Join on the editor history latest edit user
657        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id" 
658        WHERE "file"."id" = $1 AND "folder"."document_box" = $2"#,
659        )
660        .bind(file_id)
661        .bind(scope)
662        .fetch_optional(db)
663        .await
664    }
665
666    pub async fn find_by_parent_folder_with_extra(
667        db: impl DbExecutor<'_>,
668        parent_id: FolderId,
669    ) -> DbResult<Vec<FileWithExtra>> {
670        sqlx::query_as(
671            r#"
672        SELECT 
673            -- File itself 
674            "file".*,
675            -- Creator user details
676            "cu"."id" AS "cb_id", 
677            "cu"."name" AS "cb_name", 
678            "cu"."image_id" AS "cb_image_id", 
679            -- Last modified date
680            "ehl"."created_at" AS "last_modified_at", 
681            -- Last modified user details
682            "mu"."id" AS "lmb_id",  
683            "mu"."name" AS "lmb_name", 
684            "mu"."image_id" AS "lmb_image_id" 
685        FROM "docbox_files" AS "file"
686        -- Join on the creator
687        LEFT JOIN "docbox_users" AS "cu" 
688            ON "file"."created_by" = "cu"."id" 
689        -- Join on the edit history (Latest only)
690        LEFT JOIN (
691            -- Get the latest edit history entry
692            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at" 
693            FROM "docbox_edit_history"
694            ORDER BY "file_id", "created_at" DESC 
695        ) AS "ehl" ON "file"."id" = "ehl"."file_id" 
696        -- Join on the editor history latest edit user
697        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id" 
698        WHERE "file"."folder_id" = $1"#,
699        )
700        .bind(parent_id)
701        .fetch_all(db)
702        .await
703    }
704
705    pub async fn find_by_parent_file_with_extra(
706        db: impl DbExecutor<'_>,
707        parent_id: FileId,
708    ) -> DbResult<Vec<FileWithExtra>> {
709        sqlx::query_as(
710            r#"
711        SELECT 
712            -- File itself 
713            "file".*,
714            -- Creator user details
715            "cu"."id" AS "cb_id", 
716            "cu"."name" AS "cb_name", 
717            "cu"."image_id" AS "cb_image_id", 
718            -- Last modified date
719            "ehl"."created_at" AS "last_modified_at", 
720            -- Last modified user details
721            "mu"."id" AS "lmb_id",  
722            "mu"."name" AS "lmb_name", 
723            "mu"."image_id" AS "lmb_image_id" 
724        FROM "docbox_files" AS "file"
725        -- Join on the creator
726        LEFT JOIN "docbox_users" AS "cu" 
727            ON "file"."created_by" = "cu"."id" 
728        -- Join on the edit history (Latest only)
729        LEFT JOIN (
730            -- Get the latest edit history entry
731            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at" 
732            FROM "docbox_edit_history"
733            ORDER BY "file_id", "created_at" DESC 
734        ) AS "ehl" ON "file"."id" = "ehl"."file_id" 
735        -- Join on the editor history latest edit user
736        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id" 
737        WHERE "file"."parent_id" = $1"#,
738        )
739        .bind(parent_id)
740        .fetch_all(db)
741        .await
742    }
743}
docbox_database/models/file.rs

docbox_database/models/
file.rs
