Ash FrameworkAF
Ash Framework•2mo ago•
25 replies
jannibeu

Can't get trigram search _sorting_ to work to save my life

Struggling to get fragments to work properly.

I've tried various variants of read actions, query builders etc.

What I'm trying to do is use pg_trgm's similarity function to 1. filter by similarity > 0.2 (that works) and then order by similarity (this doesn't work).

What I have right now is this:
    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: [fragment("similarity(?, ?)", name, ^arg(:q))])
    end

    calculate :similarity, :float,
      expr(
        fragment(
          "similarity(?, ?)",
          name,
          ^arg(:q)
        )
      )


The resulting SQL
SELECT t0."id", t0."name", t0."description", t0."translation_en", t0."term_of_the_day", t0."scraped_from_mundmische", t0."created_at", t0."updated_at", t0."owner_id", (similarity(t0."name"::text, NULL))::float, (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))::float DESC ["2dbc514e-1fd3-4a1d-b905-beaf2dc63fc6", "My search term."]


You can see similarity(t0."name"::text, NULL) ends up being NULL. I tried moving this into query builder expressions but the fields aren't recognized. I tried inlining the the expression into the action, also nothing. I'm really lost right now. What's the right way to do this? Thank you.
Was this page helpful?