Changeset 1720482 in Klonkt

Timestamp:

06/20/2026 02:33:25 AM (3 months ago)

Author:

roboburr <roboburr@…>

Branches:

main

Children:

5f47671

Parents:

1ee967d

Message:

feat(search): tracks searchable + prefix-matching for posts

• Posts: FTS5 query is now prefix-AND ("astr"* finds "astra") instead of a single strict phrase — nicer search-as-you-type; title/author/content are searched.
• Tracks: new search on audio_tracks (title/artist/album, LIKE), as playable rows (audio-player picks up data-pcms-track) + "in post →" link to the post/album/playlist the track appears in (in-memory match over the site's published posts).
• View updated to two-section layout (Tracks / Posts).

Co-Authored-By: Claude <noreply@…>

Files:

• CHANGELOG.md (modified) (1 diff)
• src/routes/search.js (modified) (4 diffs)
• src/views/pages/search.ejs (modified) (5 diffs)

CHANGELOG.md

@@ -14 +14 @@
 ## [Unreleased]
 
+### Toegevoegd
+- Zoeken doorzoekt nu ook **nummers** (op titel, artiest én album), niet alleen posts.
+  Gevonden nummers zijn direct afspeelbaar in de resultatenlijst, met een
+  "in post →"-link naar de post/album/playlist waarin het nummer voorkomt.
+- Posts-zoek gebruikt nu **prefix-matching**: typ "astr" en je vindt "astra"
+  (elk woord als prefix, AND tussen de woorden) — fijner zoeken-terwijl-je-typt.
+
 ### Opgelost
+- Mini-speler: spring + scroll naar de spelende track werkt nu ook vanuit een
+  **album of playlist** (de track-id zat niet in de afspeel-queue).
+- De track die nu speelt krijgt een **blijvende highlight** in de post.
+- Lege **album/playlist-covers** hersteld (een gemiste verwijzing in de
+  WebP-conversie) + ze vallen nu terug op de cover van de eerste track.
+- Kapotte **avatar/profielfoto** in de kop na de WebP-conversie: de viewer-avatar
+  (en rol) wordt nu vers uit de database gelezen, dus een oude sessie herstelt
+  zichzelf zonder opnieuw inloggen.
+
+### Opgelost (eerder)
 - Inline content-layout op touch versimpeld: geen opmaak-toolbar/rand meer inline
   (je bewerkt toch fullscreen) — alleen de content-preview + de tik-om-te-bewerken-pill.

src/routes/search.js

@@ -2 +2 @@
  * GET /search?q=...
  *
- * Queries the posts_fts virtual table (FTS5) for the current site.
- * Search is restricted to published posts of the resolved site.
+ * Doorzoekt de huidige site op twee dingen:
+ *   1. Posts via de posts_fts virtuele tabel (FTS5) — published only.
+ *   2. Nummers (audio_tracks) op titel / artiest / album.
  *
- * FTS5 quirks handled:
- *   - Empty / whitespace-only query: render the form with no results.
- *   - User input is wrapped in double quotes so FTS5 treats it as a phrase
- *     (avoids syntax errors from special chars like "OR", parentheses, etc.).
- *   - Snippet() builds the highlighted excerpt; we keep markup minimal so
- *     the EJS view can wrap the matches in <mark>.
+ * Verbeteringen t.o.v. de oude versie:
+ *   - Prefix-matching: elk woord wordt als prefix-term gezocht ("astr"* vindt
+ *     "astra"), met AND tussen de woorden — typen-terwijl-je-zoekt werkt nu.
+ *   - Nummers zijn doorzoekbaar en direct afspeelbaar in de resultatenlijst,
+ *     met een link naar de post/album/playlist waarin het nummer voorkomt.
+ *
+ * FTS5-randgevallen:
+ *   - Lege / whitespace-only query: form zonder resultaten.
+ *   - User-input wordt getokeniseerd op niet-letter/cijfer en elk token tussen
+ *     dubbele quotes + `*` gezet → geen operator-soup, geen syntax-errors.
  */
 
@@ -16 +21 @@
 import db from '../config/database.js';
 import { renderPage } from '../middleware/render.js';
+import { audioUrl } from '../services/AudioStreamService.js';
 
 const router = express.Router();
 
-// Wrap user input as a single FTS5 phrase. Strip embedded double-quotes so
-// the wrapping stays balanced. FTS5 phrase queries are forgiving and avoid
-// the operator-soup pitfalls of bare user input.
-function asPhrase(q) {
-  return '"' + q.replace(/"/g, '') + '"';
+// Bouw een veilige FTS5-prefix-query: tokeniseer op alles wat geen letter/cijfer
+// is, en maak van elk token een prefix-term. Spatie = impliciete AND.
+// Bv. 'rock astr' -> '"rock"* "astr"*'. Geeft null als er geen bruikbaar token is.
+function buildFtsQuery(q) {
+  const terms = q.split(/[^\p{L}\p{N}]+/u).filter(Boolean);
+  if (!terms.length) return null;
+  return terms.map((t) => '"' + t + '"*').join(' ');
+}
+
+// Escape LIKE-wildcards in user-input zodat % en _ letterlijk matchen.
+function likeArg(q) {
+  return '%' + q.replace(/[%_\\]/g, '\\$&') + '%';
 }
 
@@ -29 +42 @@
   const site = res.locals.site;
   const rawQ = (req.query.q || '').toString().trim();
+  const isHub = res.locals.tenancy === 'hub';
+  const urlFor = (slug) => (isHub ? `/user/${site.slug}/${slug}` : `/${slug}`);
 
   if (!site) return res.status(404).send('No site');
 
-  // Empty query — render the page with the form and no results.
+  // Lege query — toon het formulier zonder resultaten.
   if (!rawQ) {
     return renderPage(req, res, 'pages/search', {
-      pageTitle: 'Search',
+      pageTitle: 'Zoeken',
       bodyClass: 'on-special',
       query: '',
       results: [],
+      tracks: [],
       total: 0,
     });
@@ -44 +60 @@
 
   let results = [];
-  let total = 0;
+  let tracks = [];
   let queryError = null;
 
-  try {
-    // FTS5 join → posts table, filter by site + published
-    results = db.prepare(`
-      SELECT
-        p.slug,
-        p.title,
-        p.excerpt,
-        p.published_at,
-        u.username AS author_username,
-        snippet(posts_fts, 0, '<mark>', '</mark>', '…', 18) AS snippet,
-        bm25(posts_fts) AS score
-      FROM posts_fts
-      JOIN posts p ON p.id = posts_fts.post_id
-      JOIN users u ON u.id = p.author_id
-      WHERE posts_fts MATCH ?
-        AND p.site_id = ?
-        AND p.status = 'published'
-      ORDER BY score ASC
-      LIMIT 50
-    `).all(asPhrase(rawQ), site.id);
-
-    total = results.length;
-  } catch (err) {
-    queryError = err.message;
+  // ── Posts (FTS5, prefix) ───────────────────────────────────────────
+  const ftsQuery = buildFtsQuery(rawQ);
+  if (ftsQuery) {
+    try {
+      results = db.prepare(`
+        SELECT
+          p.slug,
+          p.title,
+          p.excerpt,
+          p.published_at,
+          u.username AS author_username,
+          snippet(posts_fts, 0, '<mark>', '</mark>', '…', 18) AS snippet,
+          bm25(posts_fts) AS score
+        FROM posts_fts
+        JOIN posts p ON p.id = posts_fts.post_id
+        JOIN users u ON u.id = p.author_id
+        WHERE posts_fts MATCH ?
+          AND p.site_id = ?
+          AND p.status = 'published'
+        ORDER BY score ASC
+        LIMIT 50
+      `).all(ftsQuery, site.id);
+    } catch (err) {
+      queryError = err.message;
+    }
   }
 
+  // ── Nummers (audio_tracks: titel / artiest / album) ────────────────
+  try {
+    const like = likeArg(rawQ);
+    const trackRows = db.prepare(`
+      SELECT t.id, t.title, t.artist, t.album, t.cover_url, t.play_count, m.filename
+      FROM audio_tracks t
+      LEFT JOIN media m ON m.id = t.media_id
+      WHERE t.site_id = @site
+        AND ( t.title  LIKE @like ESCAPE '\\'
+           OR t.artist LIKE @like ESCAPE '\\'
+           OR t.album  LIKE @like ESCAPE '\\' )
+      ORDER BY t.play_count DESC, t.title ASC
+      LIMIT 25
+    `).all({ site: site.id, like });
+
+    // Eén keer alle published posts van de site ophalen om per nummer de
+    // post/album/playlist-pagina te vinden waarin 'ie voorkomt (in-memory match).
+    const playable = trackRows.filter((t) => t.filename);
+    let posts = [];
+    if (playable.length) {
+      posts = db.prepare(`
+        SELECT slug, content FROM posts
+        WHERE site_id = ? AND status = 'published'
+        ORDER BY published_at DESC
+      `).all(site.id);
+    }
+    const postUrlForTrack = (t) => {
+      let hit = posts.find((p) => p.content && p.content.includes('[[track:' + t.id + ']]'));
+      if (!hit && t.album) {
+        hit = posts.find((p) => p.content && p.content.includes('[[album:' + t.album + ']]'));
+      }
+      if (!hit) {
+        const plids = db.prepare('SELECT playlist_id FROM playlist_tracks WHERE track_id = ?')
+          .all(t.id).map((r) => r.playlist_id);
+        if (plids.length) {
+          hit = posts.find((p) => p.content && plids.some((pl) => p.content.includes('[[playlist:' + pl + ']]')));
+        }
+      }
+      return hit ? urlFor(hit.slug) : null;
+    };
+
+    tracks = playable.map((t) => ({
+      id: t.id,
+      title: t.title || 'Untitled',
+      artist: t.artist || '',
+      album: t.album || '',
+      cover: t.cover_url || '',
+      url: audioUrl(t.filename),
+      postUrl: postUrlForTrack(t),
+    }));
+  } catch (err) {
+    if (!queryError) queryError = err.message;
+  }
+
+  const total = results.length + tracks.length;
+
   renderPage(req, res, 'pages/search', {
-    pageTitle: `Search: ${rawQ}`,
+    pageTitle: `Zoeken: ${rawQ}`,
     bodyClass: 'on-special',
     query: rawQ,
     results,
+    tracks,
     total,
     queryError,

src/views/pages/search.ejs

@@ -1 +1 @@
 <% const _base = (typeof siteUrlBase !== 'undefined' && siteUrlBase) ? siteUrlBase : ''; %>
+<% const _tracks = (typeof tracks !== 'undefined' && tracks) ? tracks : []; %>
 <div class="container search-page">
-  <h1>Search</h1>
+  <h1>Zoeken</h1>
 
   <form method="get" action="<%= _base %>/search" class="search-page-form">
@@ -8 +9 @@
       name="q"
       value="<%= query %>"
-      placeholder="Search posts…"
+      placeholder="Zoek posts en nummers…"
       autocomplete="off"
       autofocus
-      aria-label="Search query">
-    <button type="submit" class="btn btn-primary">Search</button>
+      aria-label="Zoekopdracht">
+    <button type="submit" class="btn btn-primary">Zoek</button>
   </form>
 
   <% if (queryError) { %>
-    <p class="search-error">Couldn't run that query. Try a simpler term.</p>
+    <p class="search-error">Kon die zoekopdracht niet uitvoeren. Probeer een eenvoudiger term.</p>
   <% } %>
 
   <% if (query && !queryError) { %>
     <p class="search-meta">
-      <%= total %> result<%= total === 1 ? '' : 's' %> for &ldquo;<%= query %>&rdquo;
+      <%= total %> resulta<%= total === 1 ? 'at' : 'ten' %> voor &ldquo;<%= query %>&rdquo;
     </p>
   <% } %>
 
+  <% if (_tracks.length) { %>
+    <h2 class="search-section-title">Nummers</h2>
+    <div class="search-tracks">
+      <% _tracks.forEach(function(t) {
+           const tj = JSON.stringify({ id: t.id, url: t.url, title: t.title, artist: t.artist || '', cover: t.cover || '' })
+             .replace(/&/g, '&amp;').replace(/'/g, '&#39;').replace(/</g, '&lt;'); %>
+        <div class="post-audio-track search-track" id="track-<%= t.id %>"
+             data-pcms-track-id="<%= t.id %>" data-pcms-track-url="<%= t.url %>"
+             data-pcms-track='<%- tj %>'>
+          <button type="button" class="pat-play" aria-label="Speel <%= t.title %>">
+            <svg viewBox="0 0 24 24" fill="currentColor" aria-hidden="true"><path d="M8 4l12 8-12 8z"/></svg>
+          </button>
+          <% if (t.cover) { %>
+            <span class="search-track-cover" style="background-image:url('<%= t.cover %>')" aria-hidden="true"></span>
+          <% } %>
+          <div class="pat-info">
+            <div class="pat-title"><%= t.title %></div>
+            <% if (t.artist || t.album) { %>
+              <div class="pat-artist">
+                <%= t.artist %><% if (t.artist && t.album) { %> &middot; <% } %><%= t.album %>
+              </div>
+            <% } %>
+          </div>
+          <% if (t.postUrl) { %>
+            <a class="search-track-link"
+               href="<%= t.postUrl %>"
+               hx-get="<%= t.postUrl %>?partial=1"
+               hx-target="#pcms-main"
+               hx-swap="innerHTML"
+               hx-push-url="<%= t.postUrl %>"
+               aria-label="Open in post">in post →</a>
+          <% } %>
+        </div>
+      <% }); %>
+    </div>
+  <% } %>
+
   <% if (results && results.length) { %>
+    <% if (_tracks.length) { %><h2 class="search-section-title">Posts</h2><% } %>
     <ol class="search-results">
       <% results.forEach(function(r) { %>
         <li class="search-result">
-          <h2 class="search-result-title">
-            <a href="/<%= r.slug %>"
-               hx-get="/<%= r.slug %>?partial=1"
+          <h3 class="search-result-title">
+            <a href="<%= _base %>/<%= r.slug %>"
+               hx-get="<%= _base %>/<%= r.slug %>?partial=1"
                hx-target="#pcms-main"
                hx-swap="innerHTML"
-               hx-push-url="/<%= r.slug %>"
+               hx-push-url="<%= _base %>/<%= r.slug %>"
                hx-indicator="#pcms-loading">
-              <%= r.title || '(untitled)' %>
+              <%= r.title || '(zonder titel)' %>
             </a>
-          </h2>
+          </h3>
           <p class="search-result-meta">
             <%= r.author_username %>
@@ -47 +86 @@
       <% }); %>
     </ol>
-  <% } else if (query && !queryError) { %>
-    <p class="search-empty">No posts found.</p>
+  <% } %>
+
+  <% if (query && !queryError && !results.length && !_tracks.length) { %>
+    <p class="search-empty">Niets gevonden.</p>
   <% } %>
 </div>
@@ -80 +121 @@
   margin: 0 0 1rem;
 }
+.search-section-title {
+  font-family: var(--font-display, serif);
+  font-size: 1.1rem;
+  margin: 1.75rem 0 0.75rem;
+  color: var(--ink-soft);
+  text-transform: uppercase;
+  letter-spacing: 0.05em;
+}
 .search-error {
   background: rgba(200, 60, 60, 0.15);
@@ -93 +142 @@
 }
 
+/* Nummer-resultaten */
+.search-tracks { display: flex; flex-direction: column; gap: 0.25rem; }
+.search-track.post-audio-track {
+  display: flex;
+  align-items: center;
+  gap: 0.75rem;
+  padding: 0.5rem 0.6rem;
+  border-radius: 8px;
+}
+.search-track .search-track-cover {
+  width: 38px; height: 38px;
+  flex: 0 0 38px;
+  border-radius: 5px;
+  background-size: cover;
+  background-position: center;
+  background-color: var(--paper-2);
+}
+.search-track .pat-info { flex: 1; min-width: 0; }
+.search-track .pat-title { font-weight: 600; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
+.search-track .pat-artist { font-size: 0.82rem; color: var(--ink-soft); white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
+.search-track-link {
+  flex: 0 0 auto;
+  font-size: 0.82rem;
+  color: var(--accent);
+  text-decoration: none;
+  white-space: nowrap;
+}
+.search-track-link:hover { text-decoration: underline; }
+
 .search-results {
   list-style: none;
   margin: 0;
   padding: 0;
-  counter-reset: search-result;
 }
 .search-result {