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;