Changeset c4dd17d for database

Timestamp:

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

Author:

Tome <gjorgievtome@…>

Branches:

main

Children:

5ce5904

Parents:

37bcb87

Message:

update queries

Location:

database/drizzle

Files:

• queries/builds.ts (modified) (8 diffs)
• queries/components.ts (modified) (1 diff)
• queries/users.ts (modified) (4 diffs)
• schema/components.ts (modified) (2 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;
 }
-
-
-

database/drizzle/queries/components.ts

@@ -1 +1 @@
 import type { Database } from "../db";
-import { componentsTable, suggestionsTable } from "../schema";
+import {buildsTable, componentsTable } from "../schema";
+import {and, desc, eq, ilike} from "drizzle-orm";
 
-export async function getComponentSuggestions(db: Database) {
+export async function getAllComponents(db: Database, limit?: number,  q?: string, componentType?: string) {
+    let queryConditions = [];
 
+    if (q) {
+        queryConditions.push(
+            ilike(componentsTable.name, `%${q}%`)
+        );
+    }
+
+    if(componentType && componentType.trim() !== 'all') {
+        queryConditions.push(
+            eq(componentsTable.type, componentType.trim().toLowerCase())
+        );
+    }
+
+    const componentsList = await db
+        .select({
+            id: componentsTable.id,
+            name: componentsTable.name,
+            brand: componentsTable.brand,
+            price: componentsTable.price,
+        })
+        .from(componentsTable)
+        .where(
+            queryConditions.length > 0 ?
+                and (
+                    ...queryConditions
+                )
+                : undefined
+        )
+        .orderBy(
+            desc(componentsTable.price)
+        )
+        .limit(limit || 100); // 100 placeholder
+
+    return componentsList;
 }
 
-export async function setComponentSuggestionStatus(db: Database, suggestionId: number, status: string, adminComment: string) {
+export async function getComponentDetails(db: Database, componentId: number) {
+    const [componentDetails] = await db
+        .select()
+        .from(componentsTable)
+        .where(
+            eq(componentsTable.id, componentId)
+        )
+        .limit(1);
 
+    return componentDetails ?? null;
 }
-
-export async function getAllComponents(db: Database, componentType?: string, q?: string, page?: number, pageSize?: number) {
-
-}

database/drizzle/queries/users.ts

@@ -1 +1 @@
 import type { Database } from "../db";
-import {adminsTable, usersTable } from "../schema";
-import { eq } from "drizzle-orm";
+import {adminsTable, suggestionsTable, usersTable} from "../schema";
+import {desc, eq} from "drizzle-orm";
 
 export async function createUser(db: Database, username: string, email: string, passwordHash: string) {
@@ -14 +14 @@
 
 export async function getUserProfile(db: Database, userId: number) {
-    const user = await db
+    const [user] = await db
         .select({
             username: usersTable.username,
@@ -25 +25 @@
         .limit(1);
 
-    return user[0] ?? null;
+    return user ?? null;
 }
 
@@ -39 +39 @@
     return admin.length > 0;
 }
+
+export async function getComponentSuggestions(db: Database) {
+    const suggestionsList = await db
+        .select()
+        .from(suggestionsTable)
+        .where(
+            eq(suggestionsTable.status, 'pending')
+        )
+        .orderBy(
+            desc(suggestionsTable.id)
+        )
+
+    return suggestionsList;
+}
+
+export async function setComponentSuggestionStatus(db: Database, suggestionId: number, adminId: number, status: string, adminComment: string) {
+    const result = await db
+        .update(suggestionsTable)
+        .set({
+            adminId: adminId,
+            status: status,
+            adminComment: adminComment
+        })
+        .where(
+            eq(suggestionsTable.id, suggestionId)
+        );
+
+    return result.rowCount;
+}
+
+export async function addComponentSuggestion(db: Database, userId: number, link: string, description: string, componentType: string) {
+    const [newSuggestion] = await db
+        .insert(suggestionsTable)
+        .values({
+            userId: userId,
+            link: link,
+            description: description,
+            componentType: componentType
+        })
+        .returning({ id: suggestionsTable.id });
+
+    return newSuggestion.id ?? null;
+}

database/drizzle/schema/components.ts

@@ -1 @@
-import { pgTable, serial, integer, text, numeric, boolean, date, primaryKey } from "drizzle-orm/pg-core";
+import {pgTable, serial, integer, text, numeric, boolean, date, primaryKey, check} from "drizzle-orm/pg-core";
+import {sql} from "drizzle-orm";
 
 export const componentsTable = pgTable("components", {
@@ -7 +8 @@
     price: numeric("price").notNull(),
     imgUrl: text("img_url"),
-});
+    type: text("type").notNull()
+    },
+    (t) => ({
+        checkType: check("check_type", sql`${t.type} in 
+      ('cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard', 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card', 'cables', 'network_adapter', 'network_card')`)
+    }),
+);
 
 // Base Components