AE
Ash Elixir•3y ago
Blibs

Ash.Query fragment inside a select?

Hello, I'm trying to replicate this query with Ash.Query: 
WITH constants (value) AS ( values ('hue') )
SELECT
  id,
  first_name,
  surname,
  email,
  similarity(concat_ws(' ', first_name, surname), value) as sml_full_name
FROM users, constants name
WHERE value <% concat_ws(' ', first_name, surname)
ORDER BY sml_full_name DESC;
WITH constants (value) AS ( values ('hue') )
SELECT
  id,
  first_name,
  surname,
  email,
  similarity(concat_ws(' ', first_name, surname), value) as sml_full_name
FROM users, constants name
WHERE value <% concat_ws(' ', first_name, surname)
ORDER BY sml_full_name DESC;
Note: Ignore the contants line, that is only to testing. What I was able to get working is this so far: 
Markets.Property.Offeror
|> Ash.Query.filter(fragment("? <% concat_ws(' ', ?, ?)", ^value, first_name, surname))
|> Markets.read!()
Markets.Property.Offeror
|> Ash.Query.filter(fragment("? <% concat_ws(' ', ?, ?)", ^value, first_name, surname))
|> Markets.read!()
What I'm not sure how to do is create that select that will run the similarity function and then use it in my order by. I believe I probably can get that using calculation, but if possible I would like to have that directly in the query.
4 Replies
ZachDaniel
ZachDaniel•3y ago
When you say "if possible you would like to have that directly in the query", what do you mean? Like you just want to make sure that its running it in the SQL query?
Blibs
BlibsOP•3y ago
I would like to be able to do something like this: Ash.Query.select([fragment("similarity(?, ?) as something", a, b) so I can reference that in the sort_by The same way I'm doing in the raw query
ZachDaniel
ZachDaniel•3y ago
At the moment, you'll need to make it a calculation on the resource I'd like to support Ash.Query.sort([expr(...)]) but that isn't currently supported 
calculate :sml_full_name, :float, expr(trigram_similarity(string_join([first_name, surname], ' '), ^arg(:value))) do
  argument :value, :string, allow_nil?: false
end

calculate :sml_full_name_matches, :boolean, expr(fragment("? <% ?", ^arg(:value), string_join([first_name, surname], ' '))) do
  argument :value, :string, allow_nil?: false
end
calculate :sml_full_name, :float, expr(trigram_similarity(string_join([first_name, surname], ' '), ^arg(:value))) do
  argument :value, :string, allow_nil?: false
end

calculate :sml_full_name_matches, :boolean, expr(fragment("? <% ?", ^arg(:value), string_join([first_name, surname], ' '))) do
  argument :value, :string, allow_nil?: false
end
value = "hue"

Resource
|> Ash.Query.load(sml_full_name: %{value: value})
|> Ash.Query.filter(sml_full_name_matches(value: value))
value = "hue"

Resource
|> Ash.Query.load(sml_full_name: %{value: value})
|> Ash.Query.filter(sml_full_name_matches(value: value))
You could also simplify the expressions by adding a full_name calculation: 
calculate :full_name, :string, expr(string_join([first_name, surname], ' '))

# Then modify the calculations to use it
calculate :sml_full_name, :float, expr(trigram_similarity(full_name, ^arg(:value))) do
  argument :value, :string, allow_nil?: false
end
calculate :full_name, :string, expr(string_join([first_name, surname], ' '))

# Then modify the calculations to use it
calculate :sml_full_name, :float, expr(trigram_similarity(full_name, ^arg(:value))) do
  argument :value, :string, allow_nil?: false
end
Blibs
BlibsOP•3y ago
Thank you! That worked like a charm!

Did you find this page helpful?