Changeset 83fb5e2 for database/drizzle/queries

Timestamp:

12/25/25 01:49:51 (7 months ago)

Author:

Tome <gjorgievtome@…>

Branches:

main

Children:

e7f1bfa

Parents:

d4842f4

Message:

fix returns and add checks

Location:

database/drizzle/queries

Files:

• builds.ts (modified) (12 diffs)
• components.ts (modified) (5 diffs)
• users.ts (modified) (7 diffs)

database/drizzle/queries/builds.ts

@@ -46 +46 @@
 
 export async function setBuildApprovalStatus(db: Database, buildId: number, isApproved: boolean){
-    const result = await db
+    const [result] = await db
         .update(buildsTable)
         .set({
@@ -61 +61 @@
         })
 
-    return result.length;
+    return result?.id ?? null;
 }
 
@@ -135 +135 @@
             created_at: buildsTable.createdAt,
             total_price: buildsTable.totalPrice,
-            avgRating: sql<number>`AVG(${ratingBuildsTable.value}::float)`
+            avgRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`
         })
         .from(buildsTable)
@@ -160 +160 @@
 
     return approvedBuildsList;
-}
-
-export async function getHighestRankedBuilds(db: Database, limit? : number) {
-    const highestRankedBuildsList = await db
-        .select({
-            id: buildsTable.id,
-            user_id: buildsTable.userId,
-            name: buildsTable.name,
-            created_at: buildsTable.createdAt,
-            total_price: buildsTable.totalPrice,
-            avgRating: sql<number>`AVG(${ratingBuildsTable.value}::float)`
-        })
-        .from(buildsTable)
-        .innerJoin(
-            ratingBuildsTable,
-            eq(buildsTable.id, ratingBuildsTable.buildId)
-        )
-        .where(
-            eq(buildsTable.isApproved, true)
-        )
-        .groupBy(buildsTable.id)
-        .orderBy(
-            desc(sql<number>`AVG(${ratingBuildsTable.value}::float)`)
-        )
-        .limit(limit || 100); // 100 placeholder
-
-    return highestRankedBuildsList;
 }
 
@@ -335 +308 @@
         .limit(1);
 
-    if (existing.length) {
+    if (existing.length > 0) {
         await db
             .delete(favoriteBuildsTable)
@@ -345 +318 @@
             );
 
-        return { favorite: false };
+        return null;
     }
 
@@ -355 +328 @@
         });
 
-    return { favorite: true };
+    return true;
 }
 
@@ -378 +351 @@
         })
 
-    return result;
+    return result ?? null;
 }
 
@@ -404 +377 @@
         })
 
-    return result;
+    return result ?? null;
 }
 
@@ -498 +471 @@
 
 export async function deleteBuild(db: Database, userId: number, buildId: number) {
-    const result = await db
+    const [result] = await db
         .delete(buildsTable)
         .where(
@@ -510 +483 @@
         })
 
-    return result.length;
+    return result?.id ?? null;
 }
 
@@ -550 +523 @@
         }
 
-        return newBuild.id;
+        return newBuild?.id ?? null;
     });
 }

database/drizzle/queries/components.ts

@@ -1 +1 @@
 import type { Database } from "../db";
 import {
+    buildComponentsTable,
     buildsTable, cablesTable, caseMoboFormFactorsTable, casePsFormFactorsTable, caseStorageFormFactorsTable,
     componentsTable, coolerCPUSocketsTable, coolersTable,
@@ -7 +8 @@
     memoryTable, motherboardsTable,
     networkAdaptersTable,
-    networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable, soundCardsTable, storageTable
+    networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable,
+    ratingBuildsTable, soundCardsTable, storageTable
 } from "../schema";
 import {and, desc, eq, ilike} from "drizzle-orm";
-
-export async function getAllComponents(db: Database, limit?: number,  q?: string, componentType?: string) {
+export async function getAllComponents(db: Database, limit?: number, componentType?: string, q?: string) {
     let queryConditions = [];
 
@@ -59 +60 @@
 
     if(!component) return null;
-
-    if (!component) return null;
 
     let details: any = {};
@@ -247 +246 @@
                     numRamSlots: specificData.numRamSlots,
                     maxRamCapacity: specificData.maxRamCapacity,
+                    pciExpressSlots: specificData.pciExpressSlots,
                 });
                 break;
@@ -322 +322 @@
     });
 }
+
+export async function getCompatibleComponents(db: Database, buildId: number, componentType: string) {
+    return db.transaction(async (tx) => {
+        const [build] = await tx
+            .select({
+                buildId: buildsTable.id,
+                userId: buildsTable.userId,
+            })
+            .from(buildsTable)
+            .where(
+                eq(buildsTable.id, buildId)
+            )
+            .limit(1);
+
+        if(!build) return null;
+
+
+    return null;
+    });
+}
+
+export async function addComponentToBuild(db: Database, buildId: number, componentId: number) {
+    const [build] = await db
+        .select()
+        .from(buildsTable)
+        .where(
+            eq(buildsTable.id, buildId)
+        )
+        .limit(1);
+
+    if(!build) return null;
+
+    const [component] = await db
+        .select()
+        .from(componentsTable)
+        .where(
+            eq(componentsTable.id, componentId)
+        )
+        .limit(1);
+
+    if(!component) return null;
+
+    const existing = await db
+        .select()
+        .from(buildComponentsTable)
+        .where(
+            and(
+                eq(buildComponentsTable.buildId, buildId),
+                eq(buildComponentsTable.componentId, componentId)
+            )
+        )
+        .limit(1);
+
+    if(existing.length > 0) return null;
+
+    const [result] = await db
+        .insert(buildComponentsTable)
+        .values({
+            buildId,
+            componentId
+        })
+        .returning({
+            id: buildComponentsTable.buildId
+        });
+
+    const buildComponents = await db
+        .select({
+            price:  componentsTable.price,
+        })
+        .from(buildComponentsTable)
+        .innerJoin(
+            componentsTable,
+            eq(buildComponentsTable.componentId, componentsTable.id)
+        )
+        .where(
+            eq(buildComponentsTable.buildId, buildId)
+        );
+
+    const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0);
+
+     await db
+        .update(buildsTable)
+        .set({
+            totalPrice: totalPrice.toFixed(2)
+        })
+        .where(
+            eq(buildsTable.id, buildId)
+        );
+
+    return result?.id ?? null;
+}
+
+export async function removeComponentFromBuild(db: Database, buildId: number, componentId: number) {
+    const [build] = await db
+        .select()
+        .from(buildsTable)
+        .where(
+            eq(buildsTable.id, buildId)
+        )
+        .limit(1);
+
+    if(!build) return null;
+
+    const [component] = await db
+        .select()
+        .from(componentsTable)
+        .where(
+            eq(componentsTable.id, componentId)
+        )
+        .limit(1);
+
+    if(!component) return null;
+
+    const result = await db
+        .delete(buildComponentsTable)
+        .where(
+            and(
+                eq(buildComponentsTable.buildId, buildId),
+                eq(buildComponentsTable.componentId, componentId)
+            )
+        );
+
+    if(result.rowCount === 0) return null;
+
+    const buildComponents = await db
+        .select({
+            price:  componentsTable.price,
+        })
+        .from(buildComponentsTable)
+        .innerJoin(
+            componentsTable,
+            eq(buildComponentsTable.componentId, componentsTable.id)
+        )
+        .where(
+            eq(buildComponentsTable.buildId, buildId)
+        );
+
+    const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0);
+
+    await db
+        .update(buildsTable)
+        .set({
+            totalPrice: totalPrice.toFixed(2)
+        })
+        .where(
+            eq(buildsTable.id, buildId)
+        );
+
+    return result;
+}
+

database/drizzle/queries/users.ts

@@ -4 +4 @@
 
 export async function createUser(db: Database, username: string, email: string, passwordHash: string) {
-    await db
+    const [newUser] = await db
         .insert(usersTable)
         .values({
@@ -10 +10 @@
             email: email,
             passwordHash: passwordHash,
+        })
+        .returning({
+            id: usersTable.id,
         });
+
+    return newUser?.id ?? null;
 }
 
@@ -29 +34 @@
 
 export async function isAdmin(db: Database, userId: number) {
-    const admin = await db
+    const [admin] = await db
         .selectDistinct()
         .from(adminsTable)
@@ -37 +42 @@
         .limit(1);
 
-    return admin.length ?? null;
+    return !!admin;
 }
 
@@ -55 +60 @@
 
 export async function setComponentSuggestionStatus(db: Database, suggestionId: number, adminId: number, status: string, adminComment: string) {
-    const result = await db
+    const [result] = await db
         .update(suggestionsTable)
         .set({
@@ -64 +69 @@
         .where(
             eq(suggestionsTable.id, suggestionId)
-        );
+        )
+        .returning({
+            id: suggestionsTable.id
+        });
 
-    return result.rowCount ?? null;
+    return result?.id ?? null;
 }
 
@@ -78 +86 @@
             componentType: componentType
         })
-        .returning({ id: suggestionsTable.id });
+        .returning({
+            id: suggestionsTable.id
+        });
 
-    return newSuggestion.id ?? null;
+    return newSuggestion?.id ?? null;
 }