roman
Variable number of arguments in Ecto query
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.
Most Liked
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)
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.
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)
Popular in Questions
Other popular topics
Latest Phoenix Threads
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #hex
- #security









