Changes between Initial Version and Version 1 of Views

Timestamp:

01/29/26 21:16:48 (10 days ago)

Author:

233194

Comment:

--

Views

@@ +1 @@
+== Views ==
+
+=== 1) View for Approved Builds Page ===
+
+**Purpose:** \\
+-Shows all the approved builds built by users, sorted by price by default \\
+-Calculates average ratings for each build, so the user can compare the builds with that metric. \\
+-Includes all data needed for filtering (name search using the build names) and sorting (by price, rating and date). \\
+{{{
+CREATE OR REPLACE VIEW v_approved_builds_browse AS
+SELECT 
+    b.id,
+    b.user_id,
+    b.name,
+    b.created_at,
+    b.total_price,
+    u.username AS creator_username,
+    COUNT(DISTINCT fb.user_id) AS favorite_count,
+    COUNT(DISTINCT rb.user_id) AS total_ratings,
+    COALESCE(AVG(rb.value::float), 0) AS avg_rating,
+    COUNT(DISTINCT r.id) AS review_count
+FROM build b
+JOIN users u ON u.id = b.user_id
+LEFT JOIN favorite_build fb ON fb.build_id = b.id
+LEFT JOIN rating_build rb ON rb.build_id = b.id
+LEFT JOIN review r ON r.build_id = b.id
+WHERE b.is_approved = TRUE
+GROUP BY b.id, b.user_id, b.name, b.created_at, b.total_price, u.username;
+}}}
+
+=== 2) View for User Dashboard ===
+**Purpose:** \\
+-Shows the user's created builds with their metrics \\
+-Shows the user's favorited builds from other creators \\
+-Combines data from both queries to one view \\
+-Includes average ratings for all the builds displayed \\
+{{{
+CREATE OR REPLACE VIEW v_user_dashboard_data AS
+WITH user_created_builds AS (
+    SELECT 
+        b.id AS build_id,
+        b.user_id AS dashboard_user_id,
+        b.user_id AS creator_id,
+        u.username AS creator_username,
+        b.name,
+        b.created_at,
+        b.total_price,
+        b.is_approved,
+        'created' AS build_source,
+        COALESCE(AVG(rb.value::float), 0) AS avg_rating,
+        COUNT(DISTINCT rb.user_id) AS rating_count
+    FROM build b
+    JOIN users u ON u.id = b.user_id
+    LEFT JOIN rating_build rb ON rb.build_id = b.id
+    GROUP BY b.id, b.user_id, u.username, b.name, b.created_at, b.total_price, b.is_approved
+),
+user_favorited_builds AS (
+    SELECT 
+        b.id AS build_id,
+        fb.user_id AS dashboard_user_id,
+        b.user_id AS creator_id,
+        u.username AS creator_username,
+        b.name,
+        b.created_at,
+        b.total_price,
+        b.is_approved,
+        'favorited' AS build_source,
+        COALESCE(AVG(rb.value::float), 0) AS avg_rating,
+        COUNT(DISTINCT rb.user_id) AS rating_count
+    FROM favorite_build fb
+    JOIN build b ON b.id = fb.build_id
+    JOIN users u ON u.id = b.user_id
+    LEFT JOIN rating_build rb ON rb.build_id = b.id
+    GROUP BY b.id, fb.user_id, b.user_id, u.username, b.name, b.created_at, b.total_price, b.is_approved
+)
+SELECT * FROM user_created_builds
+UNION ALL
+SELECT * FROM user_favorited_builds;
+}}}
+
+=== 3) View for Build Details Page ===
+**Purpose:** \\
+-Pre-joins build with the creator's username \\
+-Aggregates rating statistics \\
+-Counts reviews and favorites for the build \\ 
+{{{
+CREATE OR REPLACE VIEW v_build_details_summary AS
+SELECT 
+    b.id AS build_id,
+    b.user_id,
+    b.name,
+    b.created_at,
+    b.description,
+    b.total_price,
+    b.is_approved,
+    u.username AS creator_username,
+    COALESCE(AVG(rb.value::float), 0) AS average_rating,
+    COUNT(DISTINCT rb.user_id) AS rating_count,
+    COUNT(DISTINCT r.id) AS review_count,
+    COUNT(DISTINCT fb.user_id) AS favorite_count
+FROM build b
+JOIN users u ON u.id = b.user_id
+LEFT JOIN rating_build rb ON rb.build_id = b.id
+LEFT JOIN review r ON r.build_id = b.id
+LEFT JOIN favorite_build fb ON fb.build_id = b.id
+GROUP BY b.id, b.user_id, b.name, b.created_at, b.description, 
+         b.total_price, b.is_approved, u.username;
+}}}
+
+=== 4) View for Admin Dashboard ===
+**Purpose:** \\
+-Shows component and build suggestions waiting for approval or rejection \\
+-Shows user information for both builds and components \\
+-Provides data for the admin verification queue \\ 
+{{{
+CREATE OR REPLACE VIEW v_admin_pending_items AS
+WITH pending_builds AS (
+    SELECT 
+        b.id,
+        'build' AS item_type,
+        b.user_id,
+        u.username,
+        u.email,
+        b.name AS item_name,
+        b.description,
+        b.created_at AS submitted_date,
+        b.total_price,
+        NULL AS component_type,
+        NULL AS link,
+        'pending' AS status
+    FROM build b
+    JOIN users u ON u.id = b.user_id
+    WHERE b.is_approved = FALSE
+),
+pending_suggestions AS (
+    SELECT 
+        s.id,
+        'suggestion' AS item_type,
+        s.user_id,
+        u.username,
+        u.email,
+        s.component_type AS item_name,
+        s.description,
+        NULL::DATE AS submitted_date,
+        NULL::NUMERIC AS total_price,
+        s.component_type,
+        s.link,
+        s.status
+    FROM suggestions s
+    JOIN users u ON u.id = s.user_id
+    WHERE s.status = 'pending'
+)
+SELECT * FROM pending_builds
+UNION ALL
+SELECT * FROM pending_suggestions
+ORDER BY item_type, id DESC;
+}}}