Changeset 1720482 in Klonkt for src/routes/search.js

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@…>

File:

• src/routes/search.js (modified) (4 diffs)

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,