AshJsonApi: Filter by similiarity

Ash Forum Ash Questions / Help
, ,
1

Hi!

I’m trying to filter by similarity eg, name like “eliel” where name could be “Eliel Gordon”.
Is there a way to do this with the filters on ash_json_api or do I have to create my own custom query to handle this?

So far trying filter[name]=eliel only does exact matches

2

What you’d need to do to expose custom filters is to add them as calculations. I realized ash_json_api had no good way to accept calculation inputs, so I’ve added it as an available key when building a filter from input on a field. This is currently on ash main branch. I haven’t documented it, but honestly we need a whole guide on building filters from input. Given a calculation like this:

calculate :name_matches, :boolean, expr(trigram_similarity(name, ^arg(:search)) > 0.7) do
  argument :search, :string, allow_nil? false
end

filter[name_matches][input][search]=foo would do it, which is equivalent to filter[name_matches][input][search]=true&filter[name_matches][eq]=true.

1 Like
3

Yeah I often get lost when trying to figure out how to query/filter from json_api in general, the spec isn’t detailed enough either so it makes it really difficult.

4

Hey @zachdaniel , following up on this. How do you hook up the calculation to a read action?

5

Hook it up in what way? Calculations can be loaded, or they can be filtered on like attributes that kind of thing.

6

I mean I have first_name and last_name fields, I would like to perform the similarity search you have above in a read action.

I tried loading it with this read action:

    read :search_by_name do
      argument :name, :string, allow_nil?: true

      prepare build(load: :name_matches)
    end

but gettin this error:

{ :error, %Ash.Error.Invalid{errors: [%Ash.Error.Query.InvalidCalculationArgument{calculation: :name_matches, field: :name, message: "is required", value: nil, changeset: nil, query: nil, error_context: [], vars: [], path: [:load], stacktrace: #Stacktrace<>, class: :invalid}], stacktraces?: true, changeset: nil, query: #Ash.Query<resource: IdentityService.Organizations.Organization, arguments: %{name: "Blue"}, errors: [%Ash.Error.Query.InvalidCalculationArgument{calculation: :name_matches, field: :name, message: "is required", value: nil, changeset: nil, query: nil, error_context: [], vars: [], path: [:load], stacktrace: #Stacktrace<>, class: :invalid}]>, error_context: [nil], vars: [], path: [], stacktrace: #Stacktrace<>, class: :invalid}
  }
7

Ah, right, so loading and filtering are unrelated.

read :search_by_name do
  argument :name, :string, allow_nil?: true

  filter expr(name_matches(name: arg(:name)))
end
8

That worked! Another question on calculations…

Does this work on other calculated fields? For example, I have a first_name and last_name field, but a full_name calculation. Do I have to store the full_name for this to work? Or can I compose the trigram_similarity on multiple fields?

As far as I remember, the trigram similarity works by creating trigrams of the stored fields? Not sure if calculations would work here

9

Calculations will work :slight_smile: However, you may find that you want to create some indexes on things like that.

10

Thanks @zachdaniel. I wasnt able to get it to work on the full name field with calculations yet (search results were off/ not showing up), but I added this index:

    custom_statements do
      statement :account_name_gin_trigram_index do
        up "CREATE INDEX account_name_gin_trigram_index ON accounts USING gin ((first_name || ' ' || last_name) gin_trgm_ops)"
        down "DROP INDEX account_name_gin_trigram_index"
      end
    end

And searched with:

  calculations do
    calculate :name_matches,
              :boolean,
              expr(
                trigram_similarity(first_name, ^arg(:name)) > 0.3 or
                  trigram_similarity(last_name, ^arg(:name)) > 0.3
              ) do
      argument :name, :string, allow_nil?: false
    end
  end

When I get the full_name calculations working I’ll follow up in case anyone (or myself) needs this in the future :slight_smile: :laughing:

11

Interesting…what happens when you do it with the full name field? Do you get an error?

12

It runs without errors, it just doesn’t return any results when running tests. It might have to do with reducing the similarity threshold to be honest, when i bump it down to 0.1 I get some results, but needs tweaking

13

Yeah, I think it may have to do with trigram similarity. You may need to use other fragment based full text search tools available in pg