Variable number of arguments in Ecto query

roman

Hi everyone,

I’m trying to do a full text search with PostgreSQL. I receive a list of words and want to produce a query where each of them is wrapped in a to_tsquery function, so the resulting query would look like:

SELECT "things"."id" FROM "things" WHERE (
	("things"."fulltext_index") @@ (
		to_tsquery('simple', ''' ' || 'term1' || ' ''' || ':*') &&
		to_tsquery('simple', ''' ' || 'term2' || ' ''' || ':*') &&
		…
		to_tsquery('simple', ''' ' || 'termN' || ' ''' || ':*')
	)
)

The bummer is that the number of query terms is unknown, so I need to generate the string dynamically. I’ve tried iterating over the terms and using fragment, but that gave me a confusing compilation error

… If you want to invoke Enum.reduce/2 in a query, make sure that the module Enum is required and that reduce/2 is a macro

Googling also didn’t give me any relevant results. So this is my last hope to figure it out. Could you guide me, please?

I would very much prefer not to change the query as I’m porting it from Rails code and the behavior must stay intact. But if this is not possible with Ecto, that would also be useful information.

Thank you.

6 comments

/phoenix #ecto #dynamic

0 1380 6

2019-07-23 13:58:36 UTC

Showing Posts 1 to 6

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

al2o3cr

Based on that message, it sounds like you tried code with a line like where: Enum.reduce(...). Building the fragment outside the Ecto macro plumbing may make things clearer.

Posting the code would help sort this out.

Post #1

roman

You’re right, this is what my latest attempt look like:

from(thing in Thing,
  where: fragment("? @@ ?",
    String.split(value, " ")
    |> Enum.map(fn (term) ->
      fragment("to_tsquery('simple', ' ? ':*')", String.replace(term, ~r/['?\\:‘’]/, " "))
    end)
    |> Enum.reduce(fn (sql_term_condition, acc) ->
      fragment("? && ?", acc, sql_term_condition)
    end),
    thing.fulltext_index
  )
)

Could you show an example of what you mean? Because if I’m not mistaken, calling fragment outside of the Ecto’s scope also gave some error like it’s not defined.

Post #2

LostKobrakai

You‘re probably looking for the dynamic macro.

Post #3

roman

The name does sound like it could be it, but I wasn’t able to figure out how to use it for my case. From the examples it seems it doesn’t work with strings. If you have used it for a similar purpose, I’d be grateful if you shared a sketch of the code.

Post #4

LostKobrakai

Something akin to the following, which is neither tested nor I’m sure it works as posted:

dynamic = 
	value 
	|> String.split(" ")
	|> Enum.map(fn term ->
		term = String.replace(term, ~r/['?\\:‘’]/, " ")
		dynamic([], fragment("to_tsquery('simple', ' ? ':*')", ^term))
	end)
	|> Enum.reduce(fn dynamic, acc ->
		dynamic([], ^acc and ^dynamic)
	end

complete = dynamic([thing], fragment("? @@ ?", ^dynamic, thing.fulltext_index))

query = from(thing in Thing, where: ^complete)

Post #5

roman

Thank you, this helped!
Here’s a slightly adjusted version I ended up with, for posterity:

sql_joined_query_terms =
  value
  |> String.split(" ")
  |> Enum.map(fn term ->
    term = String.replace(term, ~r/['?\\:‘’]/, " ")
    dynamic([], fragment("to_tsquery('simple', ''' ' || ? || ' '':*')", ^term))
  end)
  |> Enum.reduce(fn sql_query_term, acc ->
    dynamic([], fragment("? && ?", ^acc, ^sql_query_term))
  end)

sql_query_conditions =
  dynamic([thing], fragment("? @@ ?", ^sql_joined_query_terms, thing.fulltext_index))

from(Thing, where: ^sql_query_conditions)

Post #6

— All posts loaded —