Actually limit verified-brand chips to 3 — LIMIT next to array_agg was a no-op

1e77ada8a4285305ae8265fe017e5ffe4d815a22 · 2026-05-19 07:58:30 -0700 · Steve

Files touched

M routes/public.js

Diff

commit 1e77ada8a4285305ae8265fe017e5ffe4d815a22
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue May 19 07:58:30 2026 -0700

    Actually limit verified-brand chips to 3 — LIMIT next to array_agg was a no-op
---
 routes/public.js | 29 +++++++++++++++++++++--------
 1 file changed, 21 insertions(+), 8 deletions(-)

diff --git a/routes/public.js b/routes/public.js
index 39fff8d..05b0f36 100644
--- a/routes/public.js
+++ b/routes/public.js
@@ -83,10 +83,17 @@ router.get('/find', async (req, res, next) => {
                 WHERE c.installer_id = i.id AND c.ops_verified = true
              ), 0)::int AS verified_brands_count,
              COALESCE((
-               SELECT array_agg(c.brand ORDER BY c.display_order, c.year_issued DESC NULLS LAST)
-                 FROM installer_credentials c
-                WHERE c.installer_id = i.id AND c.ops_verified = true
-                LIMIT 3
+               -- LIMIT must be inside a sub-SELECT: applied next to array_agg
+               -- it does nothing (the aggregate collapses to one row), so the
+               -- card would show every verified brand instead of the top 3.
+               SELECT array_agg(b.brand)
+                 FROM (
+                   SELECT c.brand
+                     FROM installer_credentials c
+                    WHERE c.installer_id = i.id AND c.ops_verified = true
+                    ORDER BY c.display_order, c.year_issued DESC NULLS LAST
+                    LIMIT 3
+                 ) b
              ), ARRAY[]::text[]) AS verified_brands
         FROM installers i
        WHERE ${where.join(' AND ')}
@@ -931,10 +938,16 @@ router.get('/api/installers.geo', async (req, res, next) => {
                  WHERE c.installer_id = i.id AND c.ops_verified = true
               ), 0)::int AS verified_brands_count,
               COALESCE((
-                SELECT array_agg(c.brand ORDER BY c.display_order, c.year_issued DESC NULLS LAST)
-                  FROM installer_credentials c
-                 WHERE c.installer_id = i.id AND c.ops_verified = true
-                 LIMIT 2
+                -- LIMIT must be inside a sub-SELECT (see /find above) —
+                -- placed next to array_agg it's a no-op.
+                SELECT array_agg(b.brand)
+                  FROM (
+                    SELECT c.brand
+                      FROM installer_credentials c
+                     WHERE c.installer_id = i.id AND c.ops_verified = true
+                     ORDER BY c.display_order, c.year_issued DESC NULLS LAST
+                     LIMIT 2
+                  ) b
               ), ARRAY[]::text[]) AS verified_brands
          FROM installers i
         WHERE i.latitude IS NOT NULL AND i.longitude IS NOT NULL

← 124a38f Coerce a garbage COI project-value to null instead of 500ing · back to NationalPaperHangers · Validate the plan tier on billing checkout so installers can 50ae349 →