Ash FrameworkAF
Ash Framework•4w ago•
32 replies
jannibeu

Sorting by pg_trgm similarity not possible

I just spent another 2 hours trying to fix the trigram search. I thought it was working before but apparently it doesn't.

I tried wrapping the calc into an expr
    read :search do
      argument :q, :string, allow_nil?: false

      # Filter by trigram similarity cutoff
      filter expr(
        fragment(
          "similarity(?, ?) > 0.2",
          name,
          ^arg(:q)
        )
      )

      # Order by trigram similarity (desc) - using calculation
      prepare build(sort: [{calc(fragment("similarity(?, ?)", name, ^arg(:q))), :desc}])
    end


[debug] QUERY OK source="terms" db=0.6ms idle=13.7ms
SELECT t0."id", t0."name", t0."description", t0."created_at", t0."updated_at", t0."example", t0."translation_en", t0."translation_es", t0."owner_id", t0."term_of_the_day", t0."scraped_from_mundmische", (t0."owner_id"::uuid = $1::uuid)::boolean FROM "terms" AS t0 WHERE ((similarity(t0."name"::text, $2) > 0.2)) ORDER BY (similarity(t0."name"::text, NULL)) DESC ["2dbc514e-1fd3-4a1d-b905-beaf2dc63fc6", "Test"]


As you can see (similarity(t0."name"::text, NULL)) keeps evaluating to NULL

So what's inside prepare just doesn't get the parameters to the query.
Was this page helpful?