Changeset c4dd17d for database/drizzle/queries/builds.ts

Timestamp:

12/20/25 04:03:51 (7 months ago)

Author:

Tome <gjorgievtome@…>

Branches:

main

Children:

5ce5904

Parents:

37bcb87

Message:

update queries

File:

• database/drizzle/queries/builds.ts (modified) (8 diffs)

database/drizzle/queries/builds.ts

@@ -1 +1 @@
 import type { Database } from "../db";
-import {buildComponentsTable, buildsTable, favoriteBuildsTable, ratingBuildsTable, reviewsTable} from "../schema";
+import {
+    buildComponentsTable,
+    buildsTable,
+    componentsTable,
+    favoriteBuildsTable,
+    ratingBuildsTable,
+    reviewsTable, usersTable
+} from "../schema";
 import {eq, desc, and, sql, ilike} from "drizzle-orm";
 
@@ -72 +79 @@
 
 export async function getApprovedBuilds(db: Database, limit?: number, q?: string) {
-    let queryConditions = [];
+    let queryConditions = [eq(buildsTable.isApproved, true)];
 
     if (q) {
@@ -91 +98 @@
         .where(
             and (
-                eq(buildsTable.isApproved, true),
                 ...queryConditions
             )
@@ -110 +116 @@
             name: buildsTable.name,
             created_at: buildsTable.createdAt,
-            total_price: buildsTable.totalPrice
+            total_price: buildsTable.totalPrice,
+            avgRating: sql<number>`AVG(${ratingBuildsTable.value}::float)`
         })
         .from(buildsTable)
@@ -120 +127 @@
             eq(buildsTable.isApproved, true)
         )
-        .groupBy(
-            buildsTable.id
-        )
+        .groupBy(buildsTable.id)
         .orderBy(
             desc(sql<number>`AVG(${ratingBuildsTable.value}::float)`)
@@ -131 +136 @@
 }
 
-export async function getBuildDetails(db: Database, buildId: number) {
-    const buildDetails = await db
-        .select()
-        .from(buildsTable)
-        .where(
-            eq(buildsTable.id, buildId)
-        )
-        .limit(1);
-
-    return buildDetails[0] ?? null;
+export async function getBuildDetails(db: Database, buildId: number, userId?: number) {
+    return db.transaction(async (tx) => {
+        const [buildDetails] = await tx
+            .select({
+                id: buildsTable.id,
+                userId: buildsTable.userId,
+                name: buildsTable.name,
+                createdAt: buildsTable.createdAt,
+                description: buildsTable.description,
+                totalPrice: buildsTable.totalPrice,
+                isApproved: buildsTable.isApproved,
+                creator: usersTable.username
+            })
+            .from(buildsTable)
+            .innerJoin(
+                usersTable,
+                eq(buildsTable.userId, usersTable.id)
+            )
+            .where(
+                eq(buildsTable.id, buildId)
+            )
+            .limit(1);
+
+        if (!buildDetails) return null;
+
+        const components = await tx
+            .select({
+                componentId: buildComponentsTable.componentId,
+                component: componentsTable
+            })
+            .from(buildComponentsTable)
+            .innerJoin(
+                componentsTable,
+                eq(buildComponentsTable.componentId, componentsTable.id)
+            )
+            .where(
+                eq(buildComponentsTable.buildId, buildId)
+            );
+
+        const reviews = await tx
+            .select({
+                username: usersTable.username,
+                content: reviewsTable.content,
+                createdAt: reviewsTable.createdAt
+            })
+            .from(reviewsTable)
+            .innerJoin(
+                usersTable,
+                eq(reviewsTable.userId, usersTable.id)
+            )
+            .where(
+                eq(reviewsTable.buildId, buildId)
+            )
+            .orderBy(
+                desc(reviewsTable.createdAt)
+            );
+
+        let [ratingStatistics] = await tx
+            .select({
+                averageRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float), 0)`.as("averageRating"),
+                ratingCount: sql<number>`COUNT(${ratingBuildsTable.value})`.as("ratingCount")
+            })
+            .from(ratingBuildsTable)
+            .where(
+                eq(ratingBuildsTable.buildId, buildId)
+            )
+            .groupBy(ratingBuildsTable.buildId);
+
+        ratingStatistics = {
+            averageRating: Number(ratingStatistics?.averageRating ?? 0),
+            ratingCount: Number(ratingStatistics?.ratingCount ?? 0),
+        }
+
+        let userRating = null;
+        let isFavorite = false;
+        let userReview = null;
+
+        if(userId) {
+            const [rating] = await tx
+                .select()
+                .from(ratingBuildsTable)
+                .where(
+                    and(
+                        eq(ratingBuildsTable.buildId, buildId),
+                        eq(ratingBuildsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            userRating = rating?.value ? Number(rating.value) : null;
+
+            const [favorite] = await tx
+                .select()
+                .from(favoriteBuildsTable)
+                .where(
+                    and(
+                        eq(favoriteBuildsTable.buildId, buildId),
+                        eq(favoriteBuildsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            isFavorite = !!favorite;
+
+            const [review] = await tx
+                .select()
+                .from(reviewsTable)
+                .where(
+                    and(
+                        eq(reviewsTable.buildId, buildId),
+                        eq(reviewsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            userReview = review?.content;
+        }
+
+        return {
+            ...buildDetails,
+            components: components.map(c => c.component),
+            reviews: reviews,
+            ratingStatistics: ratingStatistics,
+            userRating,
+            userReview,
+            isFavorite
+        };
+    });
 }
 
@@ -184 +307 @@
             userId,
             buildId,
-            value: value.toString()
+            value: value
         })
         .onConflictDoUpdate({
             target: [ratingBuildsTable.userId, ratingBuildsTable.buildId],
             set: {
-                value: value.toString(),
+                value: value,
             },
         });
@@ -275 +398 @@
     return newBuild.id;
 }
-
-
-