docbox_database/models/
folder.rs

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

docbox_database/models/
folder.rs
