source: database/drizzle/queries/builds.ts@ 41a2f81

main
Last change on this file since 41a2f81 was 3870834, checked in by Tome <gjorgievtome@…>, 6 months ago

Add transanction to getBuildState
  • Property mode set to 100644
File size: 15.7 KB
Line 
1import type { Database } from "../db";
2import {
3 buildComponentsTable,
4 buildsTable,
5 componentsTable,
6 favoriteBuildsTable,
7 ratingBuildsTable,
8 reviewsTable, usersTable
9} from "../schema";
10import {eq, desc, and, sql, ilike, asc} from "drizzle-orm";
11import {inArray} from "drizzle-orm/sql/expressions/conditions";
12import {addComponentToBuild} from "./components";
13
14export async function getPendingBuilds(db: Database) {
15 const pendingBuilds = await db
16 .select({
17 id: buildsTable.id,
18 user_id: buildsTable.userId,
19 name: buildsTable.name,
20 created_at: buildsTable.createdAt,
21 total_price: buildsTable.totalPrice
22 })
23 .from(buildsTable)
24 .where(
25 eq(buildsTable.isApproved, false)
26 )
27 .orderBy(
28 desc(buildsTable.createdAt)
29 );
30
31 return pendingBuilds;
32}
33
34export async function getUserBuilds(db: Database, userId: number) {
35 const userBuilds = await db
36 .select({
37 id: buildsTable.id,
38 user_id: buildsTable.userId,
39 name: buildsTable.name,
40 created_at: buildsTable.createdAt,
41 total_price: buildsTable.totalPrice
42 })
43 .from(buildsTable)
44 .where(
45 eq(buildsTable.userId, userId)
46 )
47 .orderBy(
48 desc(buildsTable.createdAt)
49 );
50
51 return userBuilds;
52}
53
54export async function setBuildApprovalStatus(db: Database, buildId: number, isApproved: boolean){
55 const [result] = await db
56 .update(buildsTable)
57 .set({
58 isApproved: isApproved
59 })
60 .where(
61 and(
62 eq(buildsTable.id, buildId),
63 eq(buildsTable.isApproved, !isApproved)
64 )
65 )
66 .returning({
67 id: buildsTable.id
68 })
69
70 return result?.id ?? null;
71}
72
73export async function getFavoriteBuilds(db: Database, userId: number) {
74 const favoriteBuilds = await db
75 .select({
76 id: buildsTable.id,
77 user_id: buildsTable.userId,
78 name: buildsTable.name,
79 created_at: buildsTable.createdAt,
80 total_price: buildsTable.totalPrice,
81 avgRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`
82 })
83 .from(buildsTable)
84 .innerJoin(
85 favoriteBuildsTable,
86 eq(buildsTable.id, favoriteBuildsTable.buildId)
87 )
88 .leftJoin(
89 ratingBuildsTable,
90 eq(buildsTable.id, ratingBuildsTable.buildId)
91 )
92 .where(
93 eq(favoriteBuildsTable.userId, userId)
94 )
95 .groupBy(
96 buildsTable.id,
97 buildsTable.userId,
98 buildsTable.name,
99 buildsTable.createdAt,
100 buildsTable.totalPrice
101 )
102 .orderBy(
103 desc(sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`)
104 );
105
106 return favoriteBuilds;
107}
108
109export async function getApprovedBuilds(db: Database, limit?: number, sort?: string, q?: string) {
110 let queryConditions = [eq(buildsTable.isApproved, true)];
111 let sortCondition:any;
112
113 if (q) {
114 queryConditions.push(
115 ilike(buildsTable.name, `%${q}%`)
116 );
117 }
118
119 switch(sort) {
120 case 'price_asc':
121 sortCondition = asc(buildsTable.totalPrice)
122 break;
123 case 'price_desc':
124 sortCondition = desc(buildsTable.totalPrice)
125 break;
126 case 'rating_desc':
127 sortCondition = desc(sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`)
128 break;
129 case 'oldest':
130 sortCondition = asc(buildsTable.createdAt)
131 break;
132 case 'newest':
133 sortCondition = desc(buildsTable.createdAt)
134 break;
135 default:
136 sortCondition = desc(buildsTable.createdAt)
137 break;
138 }
139
140 const approvedBuilds = await db
141 .select({
142 id: buildsTable.id,
143 user_id: buildsTable.userId,
144 name: buildsTable.name,
145 created_at: buildsTable.createdAt,
146 total_price: buildsTable.totalPrice,
147 avgRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`
148 })
149 .from(buildsTable)
150 .leftJoin(
151 ratingBuildsTable,
152 eq(buildsTable.id, ratingBuildsTable.buildId)
153 )
154 .groupBy(
155 buildsTable.id,
156 buildsTable.userId,
157 buildsTable.name,
158 buildsTable.createdAt,
159 buildsTable.totalPrice
160 )
161 .where(
162 and (
163 ...queryConditions
164 )
165 )
166 .orderBy(
167 sortCondition
168 )
169 .limit(limit || 100); // 100 placeholder
170
171 return approvedBuilds;
172}
173
174export async function getBuildDetails(db: Database, buildId: number, userId?: number) {
175 return db.transaction(async (tx) => {
176 const [buildDetails] = await tx
177 .select({
178 id: buildsTable.id,
179 userId: buildsTable.userId,
180 name: buildsTable.name,
181 createdAt: buildsTable.createdAt,
182 description: buildsTable.description,
183 totalPrice: buildsTable.totalPrice,
184 isApproved: buildsTable.isApproved,
185 creator: usersTable.username
186 })
187 .from(buildsTable)
188 .innerJoin(
189 usersTable,
190 eq(buildsTable.userId, usersTable.id)
191 )
192 .where(
193 eq(buildsTable.id, buildId)
194 )
195 .limit(1);
196
197 if (!buildDetails) return null;
198
199 const components = await tx
200 .select({
201 componentId: buildComponentsTable.componentId,
202 component: componentsTable
203 })
204 .from(buildComponentsTable)
205 .innerJoin(
206 componentsTable,
207 eq(buildComponentsTable.componentId, componentsTable.id)
208 )
209 .where(
210 eq(buildComponentsTable.buildId, buildId)
211 );
212
213 const reviews = await tx
214 .select({
215 username: usersTable.username,
216 content: reviewsTable.content,
217 createdAt: reviewsTable.createdAt
218 })
219 .from(reviewsTable)
220 .innerJoin(
221 usersTable,
222 eq(reviewsTable.userId, usersTable.id)
223 )
224 .where(
225 eq(reviewsTable.buildId, buildId)
226 )
227 .orderBy(
228 desc(reviewsTable.createdAt)
229 );
230
231 let [ratingStatistics] = await tx
232 .select({
233 averageRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float), 0)`.as("averageRating"),
234 ratingCount: sql<number>`COUNT(${ratingBuildsTable.value})`.as("ratingCount")
235 })
236 .from(ratingBuildsTable)
237 .where(
238 eq(ratingBuildsTable.buildId, buildId)
239 )
240 .groupBy(ratingBuildsTable.buildId);
241
242 ratingStatistics = {
243 averageRating: Number(ratingStatistics?.averageRating ?? 0),
244 ratingCount: Number(ratingStatistics?.ratingCount ?? 0),
245 }
246
247 let userRating = null;
248 let isFavorite = false;
249 let userReview = null;
250
251 if(userId) {
252 const [rating] = await tx
253 .select()
254 .from(ratingBuildsTable)
255 .where(
256 and(
257 eq(ratingBuildsTable.buildId, buildId),
258 eq(ratingBuildsTable.userId, userId)
259 )
260 )
261 .limit(1);
262
263 userRating = rating?.value ? Number(rating.value) : null;
264
265 const [favorite] = await tx
266 .select()
267 .from(favoriteBuildsTable)
268 .where(
269 and(
270 eq(favoriteBuildsTable.buildId, buildId),
271 eq(favoriteBuildsTable.userId, userId)
272 )
273 )
274 .limit(1);
275
276 isFavorite = !!favorite;
277
278 const [review] = await tx
279 .select()
280 .from(reviewsTable)
281 .where(
282 and(
283 eq(reviewsTable.buildId, buildId),
284 eq(reviewsTable.userId, userId)
285 )
286 )
287 .limit(1);
288
289 userReview = review?.content;
290 }
291
292 return {
293 ...buildDetails,
294 components: components.map(c => c.component),
295 reviews: reviews.map(r => ({
296 username: r.username,
297 content: r.content,
298 createdAt: r.createdAt
299 })),
300 ratingStatistics: ratingStatistics,
301 userRating,
302 userReview,
303 isFavorite
304 };
305 });
306}
307
308export async function toggleFavoriteBuild(db: Database, userId: number, buildId: number) {
309 const existing = await db
310 .select()
311 .from(favoriteBuildsTable)
312 .where(
313 and(
314 eq(favoriteBuildsTable.userId, userId),
315 eq(favoriteBuildsTable.buildId, buildId)
316 )
317 )
318 .limit(1);
319
320 if (existing.length > 0) {
321 await db
322 .delete(favoriteBuildsTable)
323 .where(
324 and(
325 eq(favoriteBuildsTable.userId, userId),
326 eq(favoriteBuildsTable.buildId, buildId)
327 )
328 );
329
330 return null;
331 }
332
333 await db
334 .insert(favoriteBuildsTable)
335 .values({
336 userId,
337 buildId,
338 });
339
340 return true;
341}
342
343export async function setBuildRating(db: Database, userId: number, buildId: number, value: number) {
344 const [result] = await db
345 .insert(ratingBuildsTable)
346 .values({
347 userId,
348 buildId,
349 value: value
350 })
351 .onConflictDoUpdate({
352 target: [ratingBuildsTable.userId, ratingBuildsTable.buildId],
353 set: {
354 value: value,
355 },
356 })
357 .returning({
358 userId: ratingBuildsTable.userId,
359 buildId: ratingBuildsTable.buildId,
360 value: ratingBuildsTable.value
361 })
362
363 return result ?? null;
364}
365
366export async function setBuildReview(db: Database, userId: number, buildId: number, content: string) {
367 const [result] = await db
368 .insert(reviewsTable)
369 .values({
370 userId,
371 buildId,
372 content,
373 createdAt: new Date().toISOString().split('T')[0]
374 })
375 .onConflictDoUpdate({
376 target: [reviewsTable.userId, reviewsTable.buildId],
377 set: {
378 content: content,
379 createdAt: new Date().toISOString().split('T')[0]
380 },
381 })
382 .returning({
383 userId: reviewsTable.userId,
384 buildId: reviewsTable.buildId,
385 content: reviewsTable.content,
386 createdAt: reviewsTable.createdAt
387 })
388
389 return result ?? null;
390}
391
392export async function cloneBuild(db: Database, userId: number, buildId: number) {
393 return db.transaction(async (tx) => {
394 const [buildToClone] = await tx
395 .select()
396 .from(buildsTable)
397 .where(
398 eq(buildsTable.id, buildId)
399 )
400 .limit(1);
401
402 if (!buildToClone) return null;
403
404 const [newBuild] = await tx
405 .insert(buildsTable)
406 .values({
407 userId: userId,
408 name: `${buildToClone.name} (copy)`,
409 createdAt: new Date().toISOString().split('T')[0],
410 description: buildToClone.description,
411 totalPrice: buildToClone.totalPrice,
412 isApproved: false
413 })
414 .returning({
415 id: buildsTable.id
416 });
417
418 if(!newBuild) return null;
419
420 const existing = await tx
421 .select({ componentId: buildComponentsTable.componentId })
422 .from(buildComponentsTable)
423 .where(eq(buildComponentsTable.buildId, buildId));
424
425 if (existing.length > 0) {
426 await tx.insert(buildComponentsTable).values(
427 existing.map((r) => ({
428 buildId: newBuild.id,
429 componentId: r.componentId,
430 })),
431 );
432 }
433
434 return newBuild.id;
435 });
436}
437
438export async function deleteBuild(db: Database, userId: number, buildId: number) {
439 const [result] = await db
440 .delete(buildsTable)
441 .where(
442 and(
443 eq(buildsTable.id, buildId),
444 eq(buildsTable.userId, userId)
445 )
446 )
447 .returning({
448 id: buildsTable.id
449 })
450
451 return result?.id ?? null;
452}
453
454export async function addNewBuild(db: Database, userId: number, name: string, description: string) {
455 const [newBuild] = await db
456 .insert(buildsTable)
457 .values({
458 userId: userId,
459 name: name,
460 createdAt: new Date().toISOString().split('T')[0],
461 description: description,
462 totalPrice: Number(0).toFixed(2),
463 isApproved: false
464 })
465 .returning({
466 id: buildsTable.id
467 });
468
469 return newBuild?.id ?? null;
470}
471
472export async function editBuild(db: Database, userId: number, buildId: number) {
473 const [buildToEdit] = await db
474 .select()
475 .from(buildsTable)
476 .where(
477 and(
478 eq(buildsTable.id, buildId),
479 eq(buildsTable.userId, userId)
480 )
481 )
482 .limit(1);
483
484 if (!buildToEdit || buildToEdit.isApproved) return null;
485
486
487 return buildToEdit?.id ?? null;
488}
489
490export async function saveBuildState(db: Database, userId: number, buildId: number, name: string, description: string ) {
491 const [build] = await db
492 .select({
493 id: buildsTable.id,
494 isApproved: buildsTable.isApproved
495 })
496 .from(buildsTable)
497 .where(
498 and(
499 eq(buildsTable.id, buildId),
500 eq(buildsTable.userId, userId)
501 )
502 )
503 .limit(1);
504
505 if (!build || build.isApproved) return null;
506
507 const [updated] = await db
508 .update(buildsTable)
509 .set({
510 name,
511 description
512 })
513 .where(
514 eq(buildsTable.id, buildId)
515 )
516 .returning({
517 id: buildsTable.id
518 });
519
520 return updated?.id ?? null;
521}
522
523export async function getBuildState(db: Database, userId: number, buildId: number) {
524 return db.transaction(async (tx) => {
525 const [build] = await tx
526 .select({
527 id: buildsTable.id,
528 userId: buildsTable.userId,
529 isApproved: buildsTable.isApproved,
530 name: buildsTable.name,
531 description: buildsTable.description,
532 totalPrice: buildsTable.totalPrice,
533 })
534 .from(buildsTable)
535 .where(
536 and(
537 eq(buildsTable.id, buildId),
538 eq(buildsTable.userId, userId)
539 )
540 )
541 .limit(1);
542
543 if (!build || build.isApproved) return null;
544
545 const components = await tx
546 .select({
547 componentId: buildComponentsTable.componentId
548 })
549 .from(buildComponentsTable)
550 .where(
551 eq(buildComponentsTable.buildId, buildId)
552 );
553
554 return {
555 build,
556 componentIds: components.map(c => c.componentId)
557 };
558 });
559}
Note: See TracBrowser for help on using the repository browser.