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

docbox_database/models/
folder.rs
