Skip to main content

max / makenotwork

45.3 KB · 1281 lines History Blame Raw
1 //! Public discovery queries: search, browse, type/tag and price facets.
2 //!
3 //! Uses `pg_trgm` trigram indexes for fuzzy text matching.
4
5 use sqlx::{FromRow, PgPool, Row};
6 use std::fmt::Write as _;
7
8 use super::enums::{AiTierFilter, DiscoverSort, ItemType};
9 use super::models::{DbDiscoverItemRow, DbDiscoverProjectRow, DbItemTypeCount};
10 use crate::error::Result;
11
12 /// Shared filter parameters for discover item queries.
13 ///
14 /// Used by both [`discover_items`] and [`count_discover_items`] to keep
15 /// their filter logic in sync. The `sort_by` field is only relevant for
16 /// `discover_items`; `count_discover_items` ignores it.
17 pub struct DiscoverFilters<'a> {
18 pub search: Option<&'a str>,
19 /// Selected item types. Empty means unfiltered; multiple values are OR'd.
20 pub item_types: &'a [ItemType],
21 /// Selected tag slugs. Empty means unfiltered; multiple values are OR'd,
22 /// and each matches its own subtree.
23 pub tags: &'a [String],
24 pub min_price: Option<i32>,
25 pub max_price: Option<i32>,
26 pub sort_by: Option<DiscoverSort>,
27 /// Single-valued by nature: the three AI-disclosure choices are nested
28 /// ranges (everything ⊇ human-led ⊇ handmade), not independent values, so
29 /// OR-ing them would only ever widen back to the looser one.
30 pub ai_tier: Option<AiTierFilter>,
31 }
32
33 impl DiscoverFilters<'_> {
34 /// Item-type slugs for the `= ANY($2)` bind.
35 fn item_type_slugs(&self) -> Vec<String> {
36 self.item_types
37 .iter()
38 .map(std::string::ToString::to_string)
39 .collect()
40 }
41
42 /// `LIKE` patterns matching each selected tag's descendants, via tagtree so
43 /// the dot-path convention and the LIKE metacharacter escaping stay in one
44 /// place. Pairs with [`Self::tags`] as the exact-slug bind.
45 fn tag_descendant_patterns(&self) -> Vec<String> {
46 self.tags
47 .iter()
48 .map(|slug| tagtree::like_descendant_pattern(slug))
49 .collect()
50 }
51 }
52
53 // Shared SQL fragments for search (lexical tsvector + trigram/ILIKE fallback).
54 // The ILIKE escapes \, %, _ in the search term to prevent LIKE metacharacter interpretation.
55
56 /// Candidate membership: any word the searcher typed, none they excluded.
57 ///
58 /// A macro rather than a `const` so `concat!` can splice it into the clauses at
59 /// compile time; written once, it cannot drift between the predicate that
60 /// decides a row matches and the expression that counts how well.
61 ///
62 /// This is deliberately not `websearch_to_tsquery`, which ANDs every term. That
63 /// put a cliff at 100% coverage: a row holding four of five typed words was
64 /// excluded from the word tier alongside rows holding none, and the UI then
65 /// told both of them they had matched on spelling only. The rule is now the one
66 /// stated at the top of migration 179 -- exclusions filter, everything else
67 /// ranks -- and the tier is a count rather than a yes/no.
68 ///
69 /// The function is IMMUTABLE and takes a constant config, so repeating it in
70 /// the same statement does not repeat the work.
71 macro_rules! any_term_query {
72 () => {
73 "mnw_any_term_query($1)"
74 };
75 }
76
77 // The description terms are written as `COALESCE(i.description, '')` to match
78 // `idx_items_desc_trgm`, which is built on that expression rather than on the
79 // bare column. Written any other way Postgres will not use the index, and this
80 // clause runs on the busiest public page.
81 const ITEM_SEARCH_CLAUSE: &str = concat!(
82 r" AND (
83 i.search_tsv @@ ",
84 any_term_query!(),
85 r"
86 OR i.title % $1
87 OR i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
88 OR COALESCE(i.description, '') % $1
89 OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
90 )"
91 );
92
93 const PROJECT_SEARCH_CLAUSE: &str = concat!(
94 r" AND (
95 p.search_tsv @@ ",
96 any_term_query!(),
97 r"
98 OR p.title % $1
99 OR p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
100 )"
101 );
102
103 // ILIKE-only variants for short queries (1-2 chars) where trigram similarity is unreliable.
104
105 const ITEM_SEARCH_CLAUSE_SHORT: &str = r" AND (
106 i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
107 OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
108 )";
109
110 const PROJECT_SEARCH_CLAUSE_SHORT: &str = r" AND (
111 p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
112 )";
113
114 // Tiered relevance. Full rationale in migration 179; the short version:
115 //
116 // One cut, placed at the truth condition of the heading it drives.
117 //
118 // tier 2 -- holds at least one word the searcher typed. Renders in the main
119 // block; a row with some but not all of them prints its own count.
120 // tier 1 -- holds none of them, and is here on character overlap alone. Sits
121 // under "Near matches. Matched on spelling, not on words", which is
122 // true of every row in the tier by construction.
123 //
124 // The earlier version cut at *all* words present, so four of five ranked with
125 // zero of five and the heading lied about everything between. Coverage is
126 // continuous; the tier is the one place it is honest to make it binary, because
127 // that is where the sentence flips from false to true.
128 //
129 // The two tiers rank by different measures and never compare across the
130 // boundary. `ts_rank` above (frequency and length normalization over words that
131 // are actually there), `mnw_word_match_score` below (per-word trigram, so a
132 // misspelling costs one word instead of poisoning the comparison). They do not
133 // share a scale, which is fine precisely because the tier is decided first, by
134 // a count rather than by either score.
135 //
136 // See `templates/partials/discover_results.html` for the rendering.
137
138 /// Holds at least one word the searcher typed. Ranked by `ts_rank`.
139 pub const MATCH_TIER_LEXICAL: i16 = 2;
140
141 /// Holds none of the typed words; character overlap only. Ranked by per-word
142 /// trigram scoring (`mnw_word_match_score`, migration 179).
143 ///
144 /// Only the long-query path assigns tiers. A 1-2 character search is
145 /// substring-only by construction, so every row would land here and the
146 /// distinction would say nothing; those rows get `NULL` instead and the UI
147 /// draws no boundary.
148 pub const MATCH_TIER_FUZZY: i16 = 1;
149
150 /// Tier 2 when the row holds any typed word, tier 1 when it holds none.
151 /// `smallint` to match [`DbDiscoverItemRow::match_tier`]'s `Option<i16>`.
152 ///
153 /// The literals here and the constants above are checked against each other by
154 /// `match_tier_literals_match_constants`.
155 macro_rules! match_tier_expr {
156 ($tsv:expr) => {
157 concat!(
158 "CASE WHEN mnw_exact_terms(",
159 $tsv,
160 ", $1) > 0 THEN 2 ELSE 1 END::smallint as match_tier"
161 )
162 };
163 }
164
165 /// How many of the typed words this row holds, and how many were typed.
166 ///
167 /// Projected so the template can print "Matched 3 of 5 words" on a row that has
168 /// some but not all. The denominator is constant per query, but it is stemmed
169 /// in Postgres, so it comes back per row rather than being recomputed in Rust
170 /// off the raw string and disagreeing with the numerator.
171 macro_rules! match_terms_expr {
172 ($tsv:expr) => {
173 concat!(
174 "mnw_exact_terms(",
175 $tsv,
176 ", $1)::int as matched_terms,
177 cardinality(mnw_query_terms($1))::int as query_terms"
178 )
179 };
180 }
181
182 /// Rank within a tier: `ts_rank` for lexical rows, per-word trigram scoring for
183 /// fuzzy ones.
184 ///
185 /// Both arms read their weighting off the `setweight` A/B split in the
186 /// generated `search_tsv` column (migration 176) rather than carrying a
187 /// constant: `ts_rank` honours lexeme weights natively, and
188 /// `mnw_word_match_score` (migration 179) applies the same halving to
189 /// description lexemes that used to be written into this expression.
190 ///
191 /// The fuzzy arm scores each query word separately and averages, instead of
192 /// comparing the whole query string against the whole title. Whole-phrase
193 /// `similarity` is worst on precisely the queries that reach this tier: a long
194 /// query against a short title scores low however well the words line up,
195 /// because the query's surplus trigrams count against it. Per-word, a
196 /// misspelling costs one word.
197 macro_rules! match_score_expr {
198 ($tsv:expr) => {
199 concat!(
200 "CASE WHEN mnw_exact_terms(",
201 $tsv,
202 ", $1) > 0 THEN ts_rank(",
203 $tsv,
204 ", ",
205 any_term_query!(),
206 ")
207 ELSE mnw_word_match_score(",
208 $tsv,
209 ", $1) END::real as match_score"
210 )
211 };
212 }
213
214 /// Maximum allowed search term length. Queries longer than this are truncated.
215 const MAX_SEARCH_LEN: usize = 200;
216
217 /// Normalize a search term: trim whitespace, truncate to [`MAX_SEARCH_LEN`],
218 /// and return `None` if the result is empty.
219 pub(crate) fn normalize_search(raw: Option<&str>) -> Option<String> {
220 let trimmed = raw?.trim();
221 if trimmed.is_empty() {
222 return None;
223 }
224 if trimmed.len() > MAX_SEARCH_LEN {
225 // Truncate at a char boundary
226 let end = trimmed
227 .char_indices()
228 .take_while(|(i, _)| *i < MAX_SEARCH_LEN)
229 .last()
230 .map_or(MAX_SEARCH_LEN, |(i, c)| i + c.len_utf8());
231 Some(trimmed[..end].to_string())
232 } else {
233 Some(trimmed.to_string())
234 }
235 }
236
237 /// Returns `true` when the search term is too short for trigram matching (1-2 chars).
238 pub(crate) fn is_short_query(term: &str) -> bool {
239 term.trim().len() <= 2
240 }
241
242 /// The item-type clause. OR within the facet: any selected type matches.
243 const ITEM_TYPE_CLAUSE: &str = " AND i.item_type = ANY($2)";
244
245 /// The tag clause. OR within the facet, and each selected tag matches its own
246 /// subtree: `$5` holds the exact slugs, `$6` the `tagtree`-built descendant
247 /// patterns. LIKE's default escape character is backslash, which is what
248 /// `tagtree::like_descendant_pattern` escapes with.
249 const TAG_CLAUSE: &str = r" AND EXISTS (
250 SELECT 1 FROM item_tags it2
251 JOIN tags t2 ON t2.id = it2.tag_id
252 WHERE it2.item_id = i.id
253 AND (t2.slug = ANY($5) OR t2.path LIKE ANY($6))
254 )";
255
256 /// Append discover-item filter clauses to a dynamic query.
257 /// Parameter positions: $1=search, $2=item_types, $3=min_price, $4=max_price,
258 /// $5=tag_slugs, $6=tag_descendant_patterns. AI tier is inlined (see below).
259 ///
260 /// When `short_query` is `true`, the ILIKE-only clause is used instead of the
261 /// full trigram + ILIKE clause (trigram matching is unreliable for 1-2 char terms).
262 fn append_item_discover_filters(
263 query: &mut String,
264 filters: &DiscoverFilters<'_>,
265 has_search: bool,
266 short_query: bool,
267 ) {
268 if has_search {
269 if short_query {
270 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
271 } else {
272 query.push_str(ITEM_SEARCH_CLAUSE);
273 }
274 }
275 if !filters.item_types.is_empty() {
276 query.push_str(ITEM_TYPE_CLAUSE);
277 }
278 if filters.min_price.is_some() {
279 query.push_str(" AND i.price_cents >= $3");
280 }
281 if filters.max_price.is_some() {
282 query.push_str(" AND i.price_cents <= $4");
283 }
284 if !filters.tags.is_empty() {
285 query.push_str(TAG_CLAUSE);
286 }
287 // AI disclosure filter: `Handmade only` narrows to handmade; `Human-led`
288 // accepts handmade ∪ assisted. Values are enum-derived constants (not user
289 // input), so inlining the literals is safe and keeps the bind-position
290 // count stable across queries that use this fragment.
291 match filters.ai_tier {
292 Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
293 Some(AiTierFilter::HumanLed) => {
294 query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
295 }
296 None => {}
297 }
298 }
299
300 /// Bind the 6 discover-filter parameters ($1-$6) to a sqlx query.
301 /// The AI-tier filter is appended to the WHERE as a literal SQL fragment,
302 /// so it occupies no bind position.
303 ///
304 /// Array binds are always supplied even when the corresponding clause is
305 /// omitted, so every discover query has the same bind arity regardless of which
306 /// filters are active.
307 macro_rules! bind_item_discover_filters {
308 ($q:expr, $filters:expr, $search_term:expr) => {
309 $q.bind($search_term.unwrap_or(""))
310 .bind($filters.item_type_slugs())
311 .bind($filters.min_price.unwrap_or(0))
312 .bind($filters.max_price.unwrap_or(i32::MAX))
313 .bind($filters.tags.to_vec())
314 .bind($filters.tag_descendant_patterns())
315 };
316 }
317
318 /// The visibility predicate every public item query must carry.
319 ///
320 /// Kept as one constant because the facet counts and the result set drifted apart
321 /// once already: `get_tag_counts` was missing `scan_status` and `is_sandbox`
322 /// entirely, so it counted items the results would never show.
323 pub(crate) const ITEM_VISIBILITY_WHERE: &str = " WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE";
324
325 /// Which facet is being counted, and therefore which filter to omit.
326 ///
327 /// A facet's counts answer "what would I get if I picked this value", so the
328 /// facet's own current selection must not constrain them. Selecting
329 /// `item_type=audio` and then applying that filter to the item-type counts
330 /// would zero every other type and hide the 12 samples also available.
331 /// Every *other* active filter still applies.
332 #[derive(Debug, Clone, Copy, PartialEq, Eq)]
333 pub enum FacetAxis {
334 ItemType,
335 Tag,
336 Price,
337 AiTier,
338 }
339
340 /// Append facet-count filter clauses, omitting `axis`'s own dimension.
341 ///
342 /// Uses the same bind layout as [`append_item_discover_filters`]
343 /// ($1=search, $2=item_type, $3=min_price, $4=max_price, $5=tag) so every
344 /// discover query binds identically, and `ai_tier` is inlined as an
345 /// enum-derived literal exactly as the result queries do.
346 pub(crate) fn append_facet_filters(
347 query: &mut String,
348 filters: &DiscoverFilters<'_>,
349 axis: FacetAxis,
350 has_search: bool,
351 short_query: bool,
352 ) {
353 if has_search {
354 if short_query {
355 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
356 } else {
357 query.push_str(ITEM_SEARCH_CLAUSE);
358 }
359 }
360 if axis != FacetAxis::ItemType && !filters.item_types.is_empty() {
361 query.push_str(ITEM_TYPE_CLAUSE);
362 }
363 if axis != FacetAxis::Price {
364 if filters.min_price.is_some() {
365 query.push_str(" AND i.price_cents >= $3");
366 }
367 if filters.max_price.is_some() {
368 query.push_str(" AND i.price_cents <= $4");
369 }
370 }
371 if axis != FacetAxis::Tag && !filters.tags.is_empty() {
372 // Descendant match via the materialized dot-path, matching the result
373 // query. This previously used `parent_id`, which matched direct children
374 // only, so any tag with grandchildren counted differently than it filtered.
375 query.push_str(TAG_CLAUSE);
376 }
377 if axis != FacetAxis::AiTier {
378 match filters.ai_tier {
379 Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
380 Some(AiTierFilter::HumanLed) => {
381 query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
382 }
383 None => {}
384 }
385 }
386 }
387
388 /// Search/browse public items with optional text search, item_type, tag, and price filters.
389 ///
390 /// Search is tiered (see the `match_tier` notes above the tier/score
391 /// expressions):
392 /// - `search_tsv @@ websearch_to_tsquery` is the lexical tier: stemmed,
393 /// word-order independent, honours quoted phrases and `-exclusions`
394 /// - `%` (trigram similarity) and `ILIKE` form the fuzzy tier: misspellings,
395 /// partial words, and substrings a stemmer will not reach
396 ///
397 /// When a search term is present but no explicit sort is requested, results
398 /// are ordered by relevance (`match_tier DESC, match_score DESC`) and the UI
399 /// draws the tier boundary. Otherwise, the caller can choose `most_sold`,
400 /// `price_asc`, `price_desc`, or the default `newest`.
401 #[tracing::instrument(skip_all)]
402 pub async fn discover_items(
403 pool: &PgPool,
404 filters: &DiscoverFilters<'_>,
405 limit: i64,
406 offset: i64,
407 ) -> Result<Vec<DbDiscoverItemRow>> {
408 let search_term = normalize_search(filters.search);
409 let has_search = search_term.is_some();
410 let short_query = search_term.as_deref().is_some_and(is_short_query);
411
412 // Build the base query with optional similarity score.
413 // For short queries (1-2 chars) use a constant match_score since trigram
414 // similarity is unreliable at that length.
415 // Use LEFT JOIN with pre-aggregated transaction counts to avoid N+1 subquery per row
416 // LEFT JOIN item_tags/tags for primary tag display
417 let mut query = if has_search && !short_query {
418 format!(
419 r"
420 SELECT
421 i.id,
422 i.title,
423 i.description,
424 i.price_cents,
425 i.item_type,
426 i.created_at,
427 u.username,
428 u.settlement_currency,
429 p.title as project_title,
430 i.sales_count::bigint,
431 pt.name as primary_tag_name,
432 i.pwyw_enabled,
433 i.pwyw_min_cents,
434 i.ai_tier,
435 {tier},
436 {terms},
437 {score}
438 FROM items i
439 JOIN projects p ON i.project_id = p.id
440 JOIN users u ON p.user_id = u.id
441 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
442 LEFT JOIN tags pt ON pt.id = pit.tag_id
443 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
444 ",
445 tier = match_tier_expr!("i.search_tsv"),
446 terms = match_terms_expr!("i.search_tsv"),
447 score = match_score_expr!("i.search_tsv"),
448 )
449 } else if has_search {
450 // Short query: constant match_score, skip trigram similarity computation
451 String::from(
452 r"
453 SELECT
454 i.id,
455 i.title,
456 i.description,
457 i.price_cents,
458 i.item_type,
459 i.created_at,
460 u.username,
461 u.settlement_currency,
462 p.title as project_title,
463 i.sales_count::bigint,
464 pt.name as primary_tag_name,
465 i.pwyw_enabled,
466 i.pwyw_min_cents,
467 i.ai_tier,
468 NULL::smallint as match_tier,
469 NULL::int as matched_terms,
470 NULL::int as query_terms,
471 1.0::real as match_score
472 FROM items i
473 JOIN projects p ON i.project_id = p.id
474 JOIN users u ON p.user_id = u.id
475 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
476 LEFT JOIN tags pt ON pt.id = pit.tag_id
477 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
478 ",
479 )
480 } else {
481 String::from(
482 r"
483 SELECT
484 i.id,
485 i.title,
486 i.description,
487 i.price_cents,
488 i.item_type,
489 i.created_at,
490 u.username,
491 u.settlement_currency,
492 p.title as project_title,
493 i.sales_count::bigint,
494 pt.name as primary_tag_name,
495 i.pwyw_enabled,
496 i.pwyw_min_cents,
497 i.ai_tier,
498 NULL::smallint as match_tier,
499 NULL::int as matched_terms,
500 NULL::int as query_terms,
501 NULL::real as match_score
502 FROM items i
503 JOIN projects p ON i.project_id = p.id
504 JOIN users u ON p.user_id = u.id
505 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
506 LEFT JOIN tags pt ON pt.id = pit.tag_id
507 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
508 ",
509 )
510 };
511
512 append_item_discover_filters(&mut query, filters, has_search, short_query);
513
514 // Determine ordering
515 let order = if has_search
516 && (filters.sort_by.is_none() || filters.sort_by == Some(DiscoverSort::Newest))
517 {
518 // Tier first: a lexical match outranks every fuzzy one, whatever their
519 // scores say. The two scores are not on one scale, so this is the only
520 // ordering that means anything.
521 "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, i.created_at DESC"
522 } else {
523 match filters.sort_by {
524 Some(DiscoverSort::MostSold) => "sales_count DESC, i.created_at DESC",
525 Some(DiscoverSort::PriceAsc) => "i.price_cents ASC, i.created_at DESC",
526 Some(DiscoverSort::PriceDesc) => "i.price_cents DESC, i.created_at DESC",
527 _ => "i.created_at DESC",
528 }
529 };
530
531 write!(query, " ORDER BY {order} LIMIT $7 OFFSET $8").unwrap();
532
533 let items = bind_item_discover_filters!(
534 sqlx::query_as::<_, DbDiscoverItemRow>(&query),
535 filters,
536 search_term.as_deref()
537 )
538 .bind(limit)
539 .bind(offset)
540 .fetch_all(pool)
541 .await?;
542
543 Ok(items)
544 }
545
546 /// Count total matching items for pagination (same filters as [`discover_items`]).
547 #[tracing::instrument(skip_all)]
548 pub async fn count_discover_items(pool: &PgPool, filters: &DiscoverFilters<'_>) -> Result<i64> {
549 let search_term = normalize_search(filters.search);
550 let has_search = search_term.is_some();
551 let short_query = search_term.as_deref().is_some_and(is_short_query);
552
553 let mut query = String::from(
554 r"
555 SELECT COUNT(*)
556 FROM items i
557 JOIN projects p ON i.project_id = p.id
558 JOIN users u ON p.user_id = u.id
559 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE
560 ",
561 );
562
563 append_item_discover_filters(&mut query, filters, has_search, short_query);
564
565 let count: i64 =
566 bind_item_discover_filters!(sqlx::query_scalar(&query), filters, search_term.as_deref())
567 .fetch_one(pool)
568 .await?;
569
570 Ok(count)
571 }
572
573 /// Search/browse public projects with optional text search and category filters.
574 ///
575 /// Same trigram + ILIKE strategy as [`discover_items`], but without price
576 /// filters. Aggregates a `item_count` via LEFT JOIN so the discover UI can
577 /// show "N items" per project without a separate query.
578 #[tracing::instrument(skip_all)]
579 pub async fn discover_projects(
580 pool: &PgPool,
581 search: Option<&str>,
582 category_slug: Option<&str>,
583 sort_by: Option<DiscoverSort>,
584 has_source_code: bool,
585 limit: i64,
586 offset: i64,
587 ) -> Result<Vec<DbDiscoverProjectRow>> {
588 let search_term = normalize_search(search);
589 let has_search = search_term.is_some();
590 let short_query = search_term.as_deref().is_some_and(is_short_query);
591
592 let mut query = if has_search && !short_query {
593 format!(
594 r"
595 SELECT
596 p.slug,
597 p.title,
598 p.description,
599 p.project_type,
600 p.created_at,
601 u.username,
602 p.item_count::bigint as item_count,
603 {tier},
604 {terms},
605 {score},
606 pc.name as category_name,
607 pc.slug as category_slug
608 FROM projects p
609 JOIN users u ON p.user_id = u.id
610 LEFT JOIN project_categories pc ON pc.id = p.category_id
611 WHERE p.is_public = true AND u.is_sandbox = FALSE
612 ",
613 tier = match_tier_expr!("p.search_tsv"),
614 terms = match_terms_expr!("p.search_tsv"),
615 score = match_score_expr!("p.search_tsv"),
616 )
617 } else if has_search {
618 // Short query: constant match_score, skip trigram similarity computation
619 String::from(
620 r"
621 SELECT
622 p.slug,
623 p.title,
624 p.description,
625 p.project_type,
626 p.created_at,
627 u.username,
628 p.item_count::bigint as item_count,
629 NULL::smallint as match_tier,
630 NULL::int as matched_terms,
631 NULL::int as query_terms,
632 1.0::real as match_score,
633 pc.name as category_name,
634 pc.slug as category_slug
635 FROM projects p
636 JOIN users u ON p.user_id = u.id
637 LEFT JOIN project_categories pc ON pc.id = p.category_id
638 WHERE p.is_public = true AND u.is_sandbox = FALSE
639 ",
640 )
641 } else {
642 String::from(
643 r"
644 SELECT
645 p.slug,
646 p.title,
647 p.description,
648 p.project_type,
649 p.created_at,
650 u.username,
651 p.item_count::bigint as item_count,
652 NULL::smallint as match_tier,
653 NULL::int as matched_terms,
654 NULL::int as query_terms,
655 NULL::real as match_score,
656 pc.name as category_name,
657 pc.slug as category_slug
658 FROM projects p
659 JOIN users u ON p.user_id = u.id
660 LEFT JOIN project_categories pc ON pc.id = p.category_id
661 WHERE p.is_public = true AND u.is_sandbox = FALSE
662 ",
663 )
664 };
665
666 if has_search {
667 if short_query {
668 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
669 } else {
670 query.push_str(PROJECT_SEARCH_CLAUSE);
671 }
672 }
673
674 if category_slug.is_some() {
675 query.push_str(" AND pc.slug = $2");
676 }
677
678 if has_source_code {
679 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
680 }
681
682 // No GROUP BY: item_count is now a denormalized column on projects (maintained
683 // by a trigger), so the query has no aggregate to group (Run 11 Perf SER-3).
684
685 let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
686 "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, p.created_at DESC"
687 } else {
688 match sort_by {
689 Some(DiscoverSort::MostSold) => "item_count DESC, p.created_at DESC",
690 _ => "p.created_at DESC",
691 }
692 };
693
694 write!(query, " ORDER BY {order} LIMIT $3 OFFSET $4").unwrap();
695
696 let projects = sqlx::query_as::<_, DbDiscoverProjectRow>(&query)
697 .bind(search_term.as_deref().unwrap_or(""))
698 .bind(category_slug.unwrap_or(""))
699 .bind(limit)
700 .bind(offset)
701 .fetch_all(pool)
702 .await?;
703
704 Ok(projects)
705 }
706
707 /// Count total matching projects for pagination (same filters as [`discover_projects`]).
708 #[tracing::instrument(skip_all)]
709 pub async fn count_discover_projects(
710 pool: &PgPool,
711 search: Option<&str>,
712 category_slug: Option<&str>,
713 has_source_code: bool,
714 ) -> Result<i64> {
715 let search_term = normalize_search(search);
716 let has_search = search_term.is_some();
717 let short_query = search_term.as_deref().is_some_and(is_short_query);
718
719 let mut query = String::from(
720 r"
721 SELECT COUNT(*)
722 FROM projects p
723 JOIN users u ON p.user_id = u.id
724 WHERE p.is_public = true AND u.is_sandbox = FALSE
725 ",
726 );
727
728 if has_search {
729 if short_query {
730 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
731 } else {
732 query.push_str(PROJECT_SEARCH_CLAUSE);
733 }
734 }
735
736 if category_slug.is_some() {
737 query.push_str(
738 " AND EXISTS (SELECT 1 FROM project_categories pc WHERE pc.id = p.category_id AND pc.slug = $2)",
739 );
740 }
741
742 if has_source_code {
743 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
744 }
745
746 let count: i64 = sqlx::query_scalar(&query)
747 .bind(search_term.as_deref().unwrap_or(""))
748 .bind(category_slug.unwrap_or(""))
749 .fetch_one(pool)
750 .await?;
751
752 Ok(count)
753 }
754
755 /// Get item type counts for discover page (items mode).
756 ///
757 /// Counts what each type *would* yield, so `filters.item_type` is omitted
758 /// (see [`FacetAxis`]). Every other active filter applies.
759 #[tracing::instrument(skip_all)]
760 pub async fn get_item_type_counts(
761 pool: &PgPool,
762 filters: &DiscoverFilters<'_>,
763 ) -> Result<Vec<DbItemTypeCount>> {
764 let search_term = normalize_search(filters.search);
765 let has_search = search_term.is_some();
766 let short_query = search_term.as_deref().is_some_and(is_short_query);
767
768 let mut query = String::from(
769 r"
770 SELECT i.item_type as category, COUNT(*) as count
771 FROM items i
772 JOIN projects p ON i.project_id = p.id
773 JOIN users u ON p.user_id = u.id
774 ",
775 );
776 query.push_str(ITEM_VISIBILITY_WHERE);
777
778 append_facet_filters(
779 &mut query,
780 filters,
781 FacetAxis::ItemType,
782 has_search,
783 short_query,
784 );
785
786 query.push_str(" GROUP BY i.item_type ORDER BY count DESC");
787
788 let counts = bind_item_discover_filters!(
789 sqlx::query_as::<_, DbItemTypeCount>(&query),
790 filters,
791 search_term.as_deref()
792 )
793 .fetch_all(pool)
794 .await?;
795
796 Ok(counts)
797 }
798
799 /// The discover price buckets: label, inclusive lower bound, inclusive upper
800 /// bound (`None` = unbounded).
801 ///
802 /// The single source of truth for both the `COUNT(*) FILTER` clauses that
803 /// produce the sidebar numbers and the `min_price`/`max_price` links those
804 /// numbers are clickable through. They were duplicated across two files and
805 /// kept in step only by a comment at each site; a bucket whose bounds drifted
806 /// from its own count would display a number its link could not reproduce.
807 pub const PRICE_BUCKETS: [(&str, i32, Option<i32>); 5] = [
808 ("Free", 0, Some(0)),
809 ("Under $25", 1, Some(2499)),
810 ("$25-50", 2500, Some(4999)),
811 ("$50-100", 5000, Some(9999)),
812 ("$100+", 10000, None),
813 ];
814
815 /// The `FILTER (WHERE ...)` predicate for one bucket.
816 fn price_bucket_predicate(min: i32, max: Option<i32>) -> String {
817 match max {
818 Some(max) => format!("i.price_cents >= {min} AND i.price_cents <= {max}"),
819 None => format!("i.price_cents >= {min}"),
820 }
821 }
822
823 /// Get price range counts for the discover page sidebar (items mode only).
824 ///
825 /// Buckets are in cents: free (0), under $25 (1..2499), $25-$50 (2500..4999),
826 /// $50-$100 (5000..9999), over $100 (10000+). Uses PostgreSQL `FILTER (WHERE ...)`
827 /// to compute all five counts in a single table scan.
828 #[tracing::instrument(skip_all)]
829 pub async fn get_price_range_counts(
830 pool: &PgPool,
831 filters: &DiscoverFilters<'_>,
832 ) -> Result<Vec<i64>> {
833 let search_term = normalize_search(filters.search);
834 let has_search = search_term.is_some();
835 let short_query = search_term.as_deref().is_some_and(is_short_query);
836
837 // One aggregate per bucket, generated from PRICE_BUCKETS so the counts and
838 // the bucket links can never describe different ranges. Bounds are integer
839 // literals from a const, not user input.
840 let selects: Vec<String> = PRICE_BUCKETS
841 .iter()
842 .enumerate()
843 .map(|(i, (_, min, max))| {
844 format!(
845 "COUNT(*) FILTER (WHERE {}) as bucket_{i}",
846 price_bucket_predicate(*min, *max)
847 )
848 })
849 .collect();
850
851 let mut query = format!(
852 r"
853 SELECT
854 {}
855 FROM items i
856 JOIN projects p ON i.project_id = p.id
857 JOIN users u ON p.user_id = u.id
858 ",
859 selects.join(",\n ")
860 );
861 query.push_str(ITEM_VISIBILITY_WHERE);
862
863 append_facet_filters(
864 &mut query,
865 filters,
866 FacetAxis::Price,
867 has_search,
868 short_query,
869 );
870
871 let row = bind_item_discover_filters!(sqlx::query(&query), filters, search_term.as_deref())
872 .fetch_one(pool)
873 .await?;
874
875 // Positional read, in PRICE_BUCKETS order.
876 let counts = (0..PRICE_BUCKETS.len())
877 .map(|i| row.try_get::<Option<i64>, _>(i).ok().flatten().unwrap_or(0))
878 .collect();
879
880 Ok(counts)
881 }
882
883 /// Get AI tier counts for the discover page sidebar (items mode only).
884 #[tracing::instrument(skip_all)]
885 pub async fn get_ai_tier_counts(
886 pool: &PgPool,
887 filters: &DiscoverFilters<'_>,
888 ) -> Result<Vec<DbItemTypeCount>> {
889 let search_term = normalize_search(filters.search);
890 let has_search = search_term.is_some();
891 let short_query = search_term.as_deref().is_some_and(is_short_query);
892
893 let mut query = String::from(
894 r"
895 SELECT i.ai_tier as category, COUNT(*) as count
896 FROM items i
897 JOIN projects p ON i.project_id = p.id
898 JOIN users u ON p.user_id = u.id
899 ",
900 );
901 query.push_str(ITEM_VISIBILITY_WHERE);
902
903 append_facet_filters(
904 &mut query,
905 filters,
906 FacetAxis::AiTier,
907 has_search,
908 short_query,
909 );
910
911 query.push_str(" GROUP BY i.ai_tier ORDER BY count DESC");
912
913 let counts = bind_item_discover_filters!(
914 sqlx::query_as::<_, DbItemTypeCount>(&query),
915 filters,
916 search_term.as_deref()
917 )
918 .fetch_all(pool)
919 .await?;
920
921 Ok(counts)
922 }
923
924 /// A search suggestion with a category label (tag, project, or creator).
925 #[derive(Debug, FromRow)]
926 pub struct DbSearchSuggestion {
927 pub label: String,
928 pub category: String,
929 pub url: String,
930 }
931
932 /// Return combined search suggestions from tags, projects, and creators.
933 /// Uses ILIKE prefix match for fast results, limited to 8 total.
934 #[tracing::instrument(skip_all)]
935 pub async fn search_suggestions(pool: &PgPool, query: &str) -> Result<Vec<DbSearchSuggestion>> {
936 let q = query.trim();
937 if q.is_empty() {
938 return Ok(vec![]);
939 }
940
941 let pattern = format!(
942 "{}%",
943 q.replace('\\', "\\\\")
944 .replace('%', "\\%")
945 .replace('_', "\\_")
946 );
947
948 let rows = sqlx::query_as::<_, DbSearchSuggestion>(
949 r"
950 (
951 SELECT name AS label, 'tag' AS category, '/discover?mode=items&tag=' || slug AS url
952 FROM tags
953 WHERE name ILIKE $1
954 ORDER BY name
955 LIMIT 3
956 )
957 UNION ALL
958 (
959 SELECT title AS label, 'project' AS category, '/p/' || slug AS url
960 FROM projects
961 WHERE is_public = true AND title ILIKE $1
962 ORDER BY title
963 LIMIT 3
964 )
965 UNION ALL
966 (
967 SELECT username AS label, 'creator' AS category, '/u/' || username AS url
968 FROM users
969 WHERE is_sandbox = false AND suspended_at IS NULL AND deactivated_at IS NULL AND username ILIKE $1
970 ORDER BY username
971 LIMIT 2
972 )
973 ",
974 )
975 .bind(&pattern)
976 .fetch_all(pool)
977 .await?;
978
979 Ok(rows)
980 }
981
982 #[cfg(test)]
983 mod tests {
984 use super::*;
985
986 #[test]
987 fn normalize_search_none() {
988 assert_eq!(normalize_search(None), None);
989 }
990
991 #[test]
992 fn normalize_search_empty() {
993 assert_eq!(normalize_search(Some("")), None);
994 }
995
996 #[test]
997 fn normalize_search_whitespace_only() {
998 assert_eq!(normalize_search(Some(" ")), None);
999 }
1000
1001 #[test]
1002 fn normalize_search_trims() {
1003 assert_eq!(
1004 normalize_search(Some(" hello ")),
1005 Some("hello".to_string())
1006 );
1007 }
1008
1009 #[test]
1010 fn normalize_search_truncates_long() {
1011 let long = "a".repeat(300);
1012 let result = normalize_search(Some(&long)).unwrap();
1013 assert_eq!(result.len(), MAX_SEARCH_LEN);
1014 }
1015
1016 #[test]
1017 fn normalize_search_truncates_at_char_boundary() {
1018 // Multi-byte chars: each is 2 bytes. 150 chars = 300 bytes.
1019 let long: String = std::iter::repeat_n('\u{00E9}', 150).collect();
1020 let result = normalize_search(Some(&long)).unwrap();
1021 assert!(result.len() <= MAX_SEARCH_LEN);
1022 // Must end at a valid char boundary (no panic on indexing)
1023 assert!(result.is_char_boundary(result.len()));
1024 }
1025
1026 #[test]
1027 fn normalize_search_exact_limit() {
1028 let exact = "b".repeat(MAX_SEARCH_LEN);
1029 assert_eq!(normalize_search(Some(&exact)), Some(exact));
1030 }
1031
1032 #[test]
1033 fn is_short_query_empty() {
1034 assert!(is_short_query(""));
1035 }
1036
1037 #[test]
1038 fn is_short_query_two_chars() {
1039 assert!(is_short_query("ab"));
1040 }
1041
1042 #[test]
1043 fn is_short_query_three_chars() {
1044 assert!(!is_short_query("abc"));
1045 }
1046
1047 #[test]
1048 fn append_filters_no_filters() {
1049 let filters = DiscoverFilters {
1050 search: None,
1051 item_types: &[],
1052 tags: &[],
1053 min_price: None,
1054 max_price: None,
1055 sort_by: None,
1056 ai_tier: None,
1057 };
1058 let mut q = String::from("SELECT 1 WHERE true");
1059 append_item_discover_filters(&mut q, &filters, false, false);
1060 assert_eq!(q, "SELECT 1 WHERE true");
1061 }
1062
1063 #[test]
1064 fn append_filters_with_item_type() {
1065 let filters = DiscoverFilters {
1066 search: None,
1067 item_types: &[ItemType::Audio],
1068 tags: &[],
1069 min_price: None,
1070 max_price: None,
1071 sort_by: None,
1072 ai_tier: None,
1073 };
1074 let mut q = String::new();
1075 append_item_discover_filters(&mut q, &filters, false, false);
1076 assert!(q.contains("i.item_type = ANY($2)"));
1077 }
1078
1079 #[test]
1080 fn append_filters_tags_match_slug_or_subtree() {
1081 let tags = vec!["audio.genre".to_string()];
1082 let filters = DiscoverFilters {
1083 search: None,
1084 item_types: &[],
1085 tags: &tags,
1086 min_price: None,
1087 max_price: None,
1088 sort_by: None,
1089 ai_tier: None,
1090 };
1091 let mut q = String::new();
1092 append_item_discover_filters(&mut q, &filters, false, false);
1093 // Exact slug OR descendant path, both array-bound so the facet can
1094 // hold several selections at once.
1095 assert!(q.contains("t2.slug = ANY($5)"));
1096 assert!(q.contains("t2.path LIKE ANY($6)"));
1097 }
1098
1099 #[test]
1100 fn tag_descendant_patterns_escape_like_metacharacters() {
1101 // tagtree owns the escaping; pin that we actually route through it,
1102 // since an unescaped `_` in a slug would silently widen the match.
1103 let tags = vec!["audio.genre_x".to_string()];
1104 let filters = DiscoverFilters {
1105 search: None,
1106 item_types: &[],
1107 tags: &tags,
1108 min_price: None,
1109 max_price: None,
1110 sort_by: None,
1111 ai_tier: None,
1112 };
1113 let patterns = filters.tag_descendant_patterns();
1114 assert_eq!(patterns.len(), 1);
1115 assert!(
1116 patterns[0].contains("\\_"),
1117 "underscore must be escaped, got {:?}",
1118 patterns[0]
1119 );
1120 assert!(patterns[0].ends_with(".%"));
1121 }
1122
1123 #[test]
1124 fn append_filters_search_uses_short_clause() {
1125 let filters = DiscoverFilters {
1126 search: Some("ab"),
1127 item_types: &[],
1128 tags: &[],
1129 min_price: None,
1130 max_price: None,
1131 sort_by: None,
1132 ai_tier: None,
1133 };
1134 let mut q = String::new();
1135 append_item_discover_filters(&mut q, &filters, true, true);
1136 assert!(q.contains("ILIKE"));
1137 assert!(!q.contains("i.title % $1"));
1138 }
1139
1140 #[test]
1141 fn append_filters_search_uses_trigram_clause() {
1142 let filters = DiscoverFilters {
1143 search: Some("hello"),
1144 item_types: &[],
1145 tags: &[],
1146 min_price: None,
1147 max_price: None,
1148 sort_by: None,
1149 ai_tier: None,
1150 };
1151 let mut q = String::new();
1152 append_item_discover_filters(&mut q, &filters, true, false);
1153 assert!(q.contains("i.title % $1"));
1154 // The word branch has to be OR'd in alongside the fuzzy one, or a
1155 // stemmed-only match ("loops" for "looping") never reaches the result
1156 // set and the tier it would have been sorted into stays empty.
1157 assert!(q.contains("i.search_tsv @@ mnw_any_term_query($1)"));
1158 // Membership must be ANY typed word, never all of them. websearch_to_tsquery
1159 // ANDs its terms, which is the cliff this replaced: four of five words
1160 // present ranked with none of them, under a heading that said so falsely.
1161 assert!(!q.contains("websearch_to_tsquery"));
1162 }
1163
1164 /// The tier values live twice: as SQL literals inside `match_tier_expr!`,
1165 /// and as the constants Rust compares against. Nothing links them, so a
1166 /// change to one silently inverts the ordering rather than failing.
1167 #[test]
1168 fn match_tier_literals_match_constants() {
1169 let expr = match_tier_expr!("i.search_tsv");
1170 assert!(
1171 expr.contains(&format!(
1172 "THEN {MATCH_TIER_LEXICAL} ELSE {MATCH_TIER_FUZZY} END"
1173 )),
1174 "tier literals in the SQL drifted from the constants: {expr}"
1175 );
1176 // The cut is "holds any typed word", not "holds all of them". Anything
1177 // stricter puts partial matches under a heading that denies they matched
1178 // words at all.
1179 assert!(
1180 expr.contains("mnw_exact_terms(i.search_tsv, $1) > 0"),
1181 "tier is no longer a coverage cut: {expr}"
1182 );
1183 // Both sides are constants, so this is a compile-time check rather than
1184 // a runtime one: a bad edit fails to build instead of failing this test.
1185 const _: () = assert!(
1186 MATCH_TIER_LEXICAL > MATCH_TIER_FUZZY,
1187 "ORDER BY match_tier DESC puts lexical first only if it is the larger value"
1188 );
1189 }
1190
1191 /// The fuzzy arm calls a function defined in a migration, so a rename on
1192 /// either side only fails at runtime, against the busiest public page.
1193 /// Pin the name here and the pairing is at least stated in one place.
1194 /// The label's denominator and the tier's numerator must come from one
1195 /// place. Counting query words in Rust off the raw string would disagree
1196 /// with Postgres the moment stemming or stopword removal applied, and the
1197 /// row would read "Matched 3 of 7 words" against a 5-word tsquery.
1198 #[test]
1199 fn label_counts_come_from_the_same_stemmer_as_the_tier() {
1200 let terms = match_terms_expr!("i.search_tsv");
1201 assert!(terms.contains("mnw_exact_terms(i.search_tsv, $1)"));
1202 assert!(terms.contains("cardinality(mnw_query_terms($1))"));
1203 assert!(match_tier_expr!("i.search_tsv").contains("mnw_exact_terms"));
1204 }
1205
1206 #[test]
1207 fn fuzzy_arm_calls_the_word_match_function() {
1208 let expr = match_score_expr!("i.search_tsv");
1209 assert!(
1210 expr.contains("mnw_word_match_score(i.search_tsv, $1)"),
1211 "fuzzy ranking no longer calls mnw_word_match_score (migrations/179): {expr}"
1212 );
1213 // Whole-phrase similarity is what 179 replaced. If it comes back here,
1214 // the long-query case it fails on is back too.
1215 assert!(!expr.contains("similarity("));
1216 }
1217
1218 /// A tier is only meaningful when both kinds of match can occur. The short
1219 /// path is ILIKE-only, so it must not claim one.
1220 #[test]
1221 fn short_query_rows_are_untiered() {
1222 assert!(!ITEM_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
1223 assert!(!PROJECT_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
1224 }
1225
1226 #[test]
1227 fn append_filters_handmade_only_narrows_to_one_tier() {
1228 let filters = DiscoverFilters {
1229 search: None,
1230 item_types: &[],
1231 tags: &[],
1232 min_price: None,
1233 max_price: None,
1234 sort_by: None,
1235 ai_tier: Some(AiTierFilter::HandmadeOnly),
1236 };
1237 let mut q = String::new();
1238 append_item_discover_filters(&mut q, &filters, false, false);
1239 assert!(q.contains("i.ai_tier = 'handmade'"));
1240 assert!(!q.contains("assisted"));
1241 }
1242
1243 #[test]
1244 fn append_filters_human_led_includes_handmade_and_assisted() {
1245 // Locks the policy commitment that Human-led covers BOTH handmade
1246 // and assisted. A future rename of the literals or a swap to a
1247 // single-tier match would silently weaken the filter.
1248 let filters = DiscoverFilters {
1249 search: None,
1250 item_types: &[],
1251 tags: &[],
1252 min_price: None,
1253 max_price: None,
1254 sort_by: None,
1255 ai_tier: Some(AiTierFilter::HumanLed),
1256 };
1257 let mut q = String::new();
1258 append_item_discover_filters(&mut q, &filters, false, false);
1259 assert!(q.contains("i.ai_tier IN ('handmade', 'assisted')"));
1260 assert!(!q.contains("generated"));
1261 }
1262
1263 #[test]
1264 fn ai_tier_filter_round_trip() {
1265 // Parses the query-string value the route receives back into the
1266 // typed enum the SQL builder expects.
1267 assert_eq!(
1268 "handmade_only".parse::<AiTierFilter>().unwrap(),
1269 AiTierFilter::HandmadeOnly
1270 );
1271 assert_eq!(
1272 "human_led".parse::<AiTierFilter>().unwrap(),
1273 AiTierFilter::HumanLed
1274 );
1275 assert!("everything".parse::<AiTierFilter>().is_err());
1276 assert!("assisted".parse::<AiTierFilter>().is_err());
1277 assert_eq!(AiTierFilter::HumanLed.to_string(), "human_led");
1278 assert_eq!(AiTierFilter::HandmadeOnly.label(), "Handmade only");
1279 }
1280 }
1281