Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run custom sql query with Ecto

Tags:

elixir

ecto

I’m playing with Elixir&Ecto stuff. I’d like to create custom SQL query, which uses some postgres–specific powers (in this case: it searches postgres array).

Here’s what I’m trying to do:

iex(5)> query = from g in MyModel, where: "'sample_tag' = ANY(tags)", select: g    #Ecto.Query<from g in MyModel, where: "'sample_tag' = ANY(tags)", select: g>
iex(6)> Repo.all(query)                                                        [debug] SELECT g0."id", g0."name", g0."description", g0."image_file_name", g0."image_file_size", g0."image_updated_at", g0."image_content_type" FROM "my_model" AS g0 WHERE ('''sample_tag'' = ANY(tags)') [] (0.9ms)

unfortunaltely, it’s being escaped (so it should produce sth. like this: )

SELECT g0."id", g0."name", g0."description", g0."image_file_name", g0."image_file_size", g0."image_updated_at", g0."image_content_type" FROM "my_mode." AS g0 WHERE ('sample_tag' = ANY(tags))

How can I achieve that?

like image 640
user1105595 Avatar asked Apr 02 '15 15:04

user1105595


2 Answers

You can run sql via Ecto using

Ecto.Adapters.SQL.query(Repo, "sql here")

There is a third param, for prepared statements.

like image 98
MartinElvar Avatar answered Oct 21 '22 14:10

MartinElvar


You can use fragments to send expressions to the DB:

from g in MyModel,
  where: fragment("? = ANY(?)", "sample_tag", g.tags)
like image 36
José Valim Avatar answered Oct 21 '22 12:10

José Valim