Changeset 59f8f5a for database/drizzle/queries/builds.ts

Timestamp:

12/24/25 23:41:42 (7 months ago)

Author:

Tome <gjorgievtome@…>

Branches:

main

Children:

2c6d75a

Parents:

3fca46c

Message:

update getApprovedBuilds

File:

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

database/drizzle/queries/builds.ts

@@ -8 +8 @@
     reviewsTable, usersTable
 } from "../schema";
-import {eq, desc, and, sql, ilike} from "drizzle-orm";
+import {eq, desc, and, sql, ilike, asc} from "drizzle-orm";
 import {inArray} from "drizzle-orm/sql/expressions/conditions";
 
@@ -85 +85 @@
 }
 
-export async function getApprovedBuilds(db: Database, limit?: number, q?: string) {
+export async function getApprovedBuilds(db: Database, limit?: number, sort?: string, q?: string) {
     let queryConditions = [eq(buildsTable.isApproved, true)];
+    let sortConditions = [];
 
     if (q) {
@@ -93 +94 @@
         );
     }
+
+    switch(sort) {
+        case 'price_asc':
+            sortConditions.push(
+                asc(buildsTable.totalPrice)
+            );
+            break;
+        case 'price_desc':
+            sortConditions.push(
+                desc(buildsTable.totalPrice)
+            );
+            break;
+        case 'rating_desc':
+            sortConditions.push(
+                desc(sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`)
+            );
+            break;
+        case 'oldest':
+            sortConditions.push(
+                asc(buildsTable.createdAt)
+            );
+            break;
+        case 'newest':
+            sortConditions.push(
+                desc(buildsTable.createdAt)
+            );
+            break;
+        default:
+            sortConditions.push(
+                desc(buildsTable.createdAt)
+            );
+            break;
+        }
 
     const approvedBuildsList = await db
@@ -104 +138 @@
         })
         .from(buildsTable)
+        .leftJoin(
+            ratingBuildsTable,
+            eq(buildsTable.id, ratingBuildsTable.buildId)
+        )
+        .groupBy(
+            buildsTable.userId,
+            buildsTable.name,
+            buildsTable.createdAt,
+            buildsTable.totalPrice
+        )
         .where(
             and (
@@ -110 +154 @@
         )
         .orderBy(
-            desc(buildsTable.totalPrice)
+            ...sortConditions
         )
         .limit(limit || 100); // 100 placeholder