How to do a similarity search with Ash?

Ash Forum Ash Questions / Help
, ,
1

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

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

Thanks

2

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?

3

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

4

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)
5

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

6

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

7

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
8

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

9

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.
1 Like