roman

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

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

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

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)

Where Next?

Popular in Questions Top

jononomo
I am trying to figure out how Mix knows whether the environment is test, dev, or prod – where is this set? Thanks.
New
Emily
I have VueJS GUIs with the project generated using Webpack. I have Elixir modules that will need to be used by the VueJS GUIs. I forese...
New
skosch
To my knowledge, put_in, Map.update etc. all have the one limitation of not automatically creating intermediate keys when needed (for exa...
New
aadeshere1
I have a another noob question about loop. Since elixir is immutable, while loop is not directly possible. total = 10 while total != 0 ...
New
greenz1
I have a phoenix application from which a user can download multiple(5-6) files of size 1MB. I couldn’t find anything related to sending ...
New
sergio_101
I am VERY much an elixir newbie. I have taken one elixir course and one phoenix course on Udemy. During that course, I saw the instructor...
New
SoCreat
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
New

Other popular topics Top

joaquinalcerro
Hi there, I am working with Ecto-Postgresql and I need to call all of the records from a specific table but the table has 40,000 records...
New
JakeBecker
TL;DR: I’ve just released an implementation of Microsoft’s IDE-independent Language Server Protocol for Elixir. It adds language support ...
1144 54921 245
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New
openscript
Hello! Sorry for this astonishing simple question, but I’m really stuck. I try to set up the intellij-elixir plugin, but I don’t know ho...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
AstonJ
Seen any cool LiveView demos, sample apps or examples? Please post them here! :003:
New

We're in Beta

About us Mission Statement