← back to AsSeenInMovies
asseeninmovies: /spotted — 'Most-Spotted Films' rail (top 6 by placement count, with poster + count badge); parallelized against the stats CTE; only renders when >1 unique film has placements
992f4716c657af927562b7f0e42c2f39aa84a793 · 2026-05-12 17:56:21 -0700 · SteveStudio2
Files touched
Diff
commit 992f4716c657af927562b7f0e42c2f39aa84a793
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Tue May 12 17:56:21 2026 -0700
asseeninmovies: /spotted — 'Most-Spotted Films' rail (top 6 by placement count, with poster + count badge); parallelized against the stats CTE; only renders when >1 unique film has placements
---
server.js | 46 ++++++++++++++++++++++++++++++++++++++++------
1 file changed, 40 insertions(+), 6 deletions(-)
diff --git a/server.js b/server.js
index 8901be5..d5fc12f 100644
--- a/server.js
+++ b/server.js
@@ -268,13 +268,23 @@ app.get('/spotted', async (req, res) => {
const total = parseInt(totalR.rows[0].n, 10);
const totalPages = Math.max(1, Math.ceil(total / limit));
const catsR = await pool.query(`SELECT product_category, count(*) AS n FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL GROUP BY 1 ORDER BY 2 DESC`);
- const statsR = await pool.query(`
- SELECT
- (SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS placements,
- (SELECT count(DISTINCT movie_id) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS movies,
- (SELECT count(DISTINCT NULLIF(TRIM(brand),'')) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS brands,
- (SELECT count(DISTINCT product_category) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL) AS categories`);
+ const [statsR, topMoviesR] = await Promise.all([
+ pool.query(`
+ SELECT
+ (SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS placements,
+ (SELECT count(DISTINCT movie_id) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS movies,
+ (SELECT count(DISTINCT NULLIF(TRIM(brand),'')) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS brands,
+ (SELECT count(DISTINCT product_category) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL) AS categories`),
+ pool.query(`
+ SELECT s.movie_id, m.title, m.release_year, m.poster_url, m.poster_source, count(*) AS n
+ FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
+ WHERE s.verified_at IS NOT NULL AND (s.verified_by IS NULL OR s.verified_by NOT LIKE 'rejected%')
+ GROUP BY s.movie_id, m.title, m.release_year, m.poster_url, m.poster_source
+ ORDER BY count(*) DESC, m.title
+ LIMIT 6`)
+ ]);
const gs = statsR.rows[0] || {};
+ const topMovies = topMoviesR.rows;
const qs = (overrides = {}) => {
const u = new URLSearchParams();
if (cat) u.set('category', cat);
@@ -336,6 +346,17 @@ app.get('/spotted', async (req, res) => {
.sp-stats .num{font-family:var(--serif);font-size:30px;font-weight:500;color:var(--gold);line-height:1.0}
.sp-stats .lbl{font-size:10.5px;letter-spacing:0.1em;text-transform:uppercase;color:var(--ink-faint)}
@media (max-width:560px){.sp-stats{grid-template-columns:repeat(2,1fr)}}
+ .top-films{margin:0 0 28px}
+ .top-films h2{margin:0 0 12px;font-family:var(--serif);font-size:15px;letter-spacing:0.16em;text-transform:uppercase;color:var(--ink-faint);font-weight:400}
+ .top-films .rail{display:grid;grid-template-columns:repeat(6,1fr);gap:10px}
+ @media (max-width:900px){.top-films .rail{grid-template-columns:repeat(3,1fr)}}
+ @media (max-width:540px){.top-films .rail{grid-template-columns:repeat(2,1fr)}}
+ .top-films .tf-card{display:block;text-decoration:none;color:var(--ink);position:relative;border:1px solid var(--line);border-radius:6px;overflow:hidden;transition:transform .15s,border-color .15s}
+ .top-films .tf-card:hover{transform:translateY(-2px);border-color:var(--gold)}
+ .top-films .tf-poster{aspect-ratio:2/3;background:#15130f no-repeat center/cover}
+ .top-films .tf-meta{padding:6px 8px;font-size:11.5px;line-height:1.3;background:rgba(0,0,0,0.4)}
+ .top-films .tf-title{display:-webkit-box;-webkit-line-clamp:1;-webkit-box-orient:vertical;overflow:hidden}
+ .top-films .tf-count{display:inline-block;position:absolute;top:6px;right:6px;background:var(--gold);color:#1a1410;font-size:10.5px;letter-spacing:0.08em;font-weight:600;padding:2px 7px;border-radius:999px}
</style>
<h1 class="title">SPOTTED</h1>
<p class="meta-line">${total.toLocaleString()} verified product placements — set decor, wallcoverings, lighting, props.</p>
@@ -351,6 +372,19 @@ app.get('/spotted', async (req, res) => {
<a href="/spotted/submit">Submit a placement →</a>
</div>
+ ${topMovies.length > 1 ? `
+ <section class="top-films">
+ <h2>Most-Spotted Films</h2>
+ <div class="rail">
+ ${topMovies.map(m => `
+ <a class="tf-card" href="/movie/${m.movie_id}" aria-label="${esc(m.title)}">
+ <div class="tf-poster" style="background-image:url('${esc((m.poster_source||'').toLowerCase()!=='made-with-ai' && m.poster_url ? m.poster_url : '/img/made-with-ai.svg')}')"></div>
+ <span class="tf-count">${m.n}</span>
+ <div class="tf-meta"><div class="tf-title">${esc(m.title)}</div><div class="muted" style="font-size:10.5px">${m.release_year || ''}</div></div>
+ </a>`).join('')}
+ </div>
+ </section>` : ''}
+
${filterBar}
${rows || '<p class="empty">No verified placements in this category yet.</p>'}
${pager}`;
← ae66c63 asseeninmovies: GET /api/spotted/stats — JSON counts (pendin
·
back to AsSeenInMovies
·
asseeninmovies: /spotted — full parallelization of all 5 que 40fa8dd →