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

docbox_database/models/
file.rs
