View of all builds & their details

Actors

Guest, User, Admin

Scenario

1. The user clicks on the "COMPLETED BUILDS" button

2. A new page with all the completed and approved builds is displayed

3. The user can sort by creation date, price and search by name

   SELECT
     b.id,
     b.user_id,
     b.name,
     b.created_at,
     b.total_price,
     COALESCE(AVG(rb.value::float), 0) AS avgRating
   FROM build AS b
   LEFT JOIN rating_build AS rb
     ON b.id = rb.build_id
   WHERE
     b.is_approved = true
     AND b.name ILIKE ('%' || $q || '%')
   GROUP BY
     b.id, b.user_id, b.name, b.created_at, b.total_price
   ORDER BY
     CASE WHEN $sort = 'price_asc'   THEN b.total_price END ASC,
     CASE WHEN $sort = 'price_desc'  THEN b.total_price END DESC,
     CASE WHEN $sort = 'rating_desc' THEN COALESCE(AVG(rb.value::float), 0) END DESC,
     CASE WHEN $sort = 'oldest'      THEN b.created_at END ASC,
     CASE WHEN $sort = 'newest'      THEN b.created_at END DESC,
     b.created_at DESC
   LIMIT COALESCE($limit, 100);
   

4. The user clicks on one of the build cards and a popup with the build details is displayed

   BEGIN;
   
   SELECT
     b.id,
     b.user_id,
     b.name,
     b.created_at,
     b.description,
     b.total_price,
     b.is_approved,
     u.username     AS "creator"
   FROM build AS b
   INNER JOIN users AS u
     ON b.user_id = u.id
   WHERE b.id = $buildId
   LIMIT 1;
   
   SELECT
     bc.component_id AS "componentId",
     c.*             AS "component"
   FROM build_component AS bc
   INNER JOIN components AS c
     ON bc.component_id = c.id
   WHERE bc.build_id = $buildId;
   
   SELECT
     u.username,
     r.content,
     r.created_at
   FROM review AS r
   INNER JOIN users AS u
     ON r.user_id = u.id
   WHERE r.build_id = $buildId
   ORDER BY r.created_at DESC;
   
   SELECT
     COALESCE(AVG(rb.value::float), 0) AS "averageRating",
     COUNT(rb.value)                  AS "ratingCount"
   FROM rating_build AS rb
   WHERE rb.build_id = $buildId;
   
   SELECT value
   FROM rating_build
   WHERE build_id = $buildId AND user_id = $userId
   LIMIT 1;
   
   SELECT 1
   FROM favorite_build
   WHERE build_id = $buildId AND user_id = $userId
   LIMIT 1;
   
   SELECT content
   FROM review
   WHERE build_id = $buildId AND user_id = $userId
   LIMIT 1;
   
   COMMIT;