How to do a similarity search with Ash?

msonawane

Hello,

I am trying to get search results with postgres trgm like below

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

with following read action and calculation.

    read :search do
      argument :term, :string, allow_nil?: false

      prepare build(
                select: [:name, :id, :country_code, :state_code],
                sort: [similarity: {%{term: arg(:term)}, :desc}],
                limit: 10
              )

      filter expr(fragment("name % (?)", ^arg(:term)))
    end


    calculate :similarity, :float do
      argument :term, :string, allow_nil?: false
      calculation expr(fragment("similarity(name,?)", ^arg(:term)))
    end

i get sql but similarity function is called twice, once in select clause and again in order by clause.

iex(4)> City |>  Ash.Query.for_read(:search, %{term: "york"}) |> Ash.read
[debug] QUERY OK source="cities" db=29.8ms queue=4.4ms idle=590.6ms
SELECT c0."name", c0."id", c0."country_code", c0."state_code", ((similarity(name,$1)))::float::float FROM "cities" AS c0 WHERE (((name % ($2)))) ORDER BY ((similarity(name,$3)))::float::float DESC LIMIT $4 ["york", "york", "york", 10]
↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:767

Questions

can this be improved ?
any recipies for calling raw sql queries in ash way?

Thanks

8 comments

/ash #ash-postgres

0 657 8

2024-08-16 14:29:47 UTC

Showing Posts 1 to 8

Show Best Posts
Show All (oldest first)
Show All (newest first)

zachdaniel

Creator of Ash

Repeating expressions on the same subject in a query does not mean that the expression will be computed multiple times. Generally speaking postgres optimizes this kind of thing away.

If you are having performance issues of that function then perhaps you are missing an appropriate full text index?

Post #1

zachdaniel

Creator of Ash

For calling raw queries, you’d want to look into manual actions.

Post #2

msonawane

Thanks for reply!
I have following manual read

  use Ash.Resource.ManualRead

  def read(ash_query, _ecto_query, _opts, _context) do
    result =
      Ecto.Adapters.SQL.query!(
        Bajirao.Repo,
        "select id, name, state_code, country_code, similarity(name,$1) as sml from cities where name % $2 ORDER BY sml desc",
        [ash_query.arguments.term, ash_query.arguments.term]
      )

    {:ok, result.rows}
  end
end

but that returns following error, I think i will have to transform results.row in a shape ash would like, but i dont know what exactly it is

iex(4)> City |>  Ash.Query.for_read(:search1, %{term: "york"}) |> Ash.read
[debug] QUERY OK db=2.0ms queue=0.8ms idle=1304.6ms
select id, name, state_code, country_code, similarity(name,$1) as sml from cities where name % $2 ORDER BY sml desc ["york", "york"]
** (BadMapError) expected a map, got: [8025, "York", "WA", "AU", 1.0]
    :erlang.map_get(:__metadata__, [8025, "York", "WA", "AU", 1.0])
    (elixir 1.17.2) lib/map.ex:318: Map.update!/3
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ash 3.3.3) lib/ash/actions/read/read.ex:464: anonymous fn/5 in Ash.Actions.Read.do_read/4
    (ash 3.3.3) lib/ash/actions/read/read.ex:786: Ash.Actions.Read.maybe_in_transaction/3
    (ash 3.3.3) lib/ash/actions/read/read.ex:249: Ash.Actions.Read.do_run/3
    (ash 3.3.3) lib/ash/actions/read/read.ex:66: anonymous fn/3 in Ash.Actions.Read.run/3
    (ash 3.3.3) lib/ash/actions/read/read.ex:65: Ash.Actions.Read.run/3
    (ash 3.3.3) lib/ash.ex:1863: Ash.read/2
    iex:4: (file)

Post #3

zachdaniel

Creator of Ash

A manual read returns instances of the resource’s struct.

Post #4

zachdaniel

Creator of Ash

I.e {:ok, [%Resource{…}]}

Post #5

msonawane

thank you! following manual read worked.

  use Ash.Resource.ManualRead

  def read(ash_query, _ecto_query, _opts, _context) do
    %{rows: rows, columns: columns} =
      Repo.query!(
        "select id, name, state_code, country_code, similarity(name,$1) as sml from cities where name % $2 ORDER BY sml desc limit $3",
        [ash_query.arguments.term, ash_query.arguments.term, ash_query.arguments.limit]
      )

    result = Enum.map(rows, fn row -> Repo.load(City, {columns, row}) end)

    {:ok, result}
  end

Post #6

zachdaniel

Creator of Ash

Out of curiosity, why do you need manual read for this?

Post #7

msonawane

sorry for delay. few reasons

we have few custom sql functions which require escape hatches for any orm
to learn idiomatic ways to do tasks without fighting framework. so far ash seems really well thought out and i havent had to fight it much after initial learning.
Thank you for your work.

Post #8

— All posts loaded —