Querying Postgres Array types with fragment.

[Thomas Shelton]

I'm trying to query a table with an array type and have been unable to get it to work successfully.

The basic setup is a table named Posts with a field of Tags defined as array of :string.

If I hardcode the value I want in the query, it works.  If I pass the value and then interpolate, it does not work.

So this works 

query = from p in Post, where: fragment("'?' = ANY (?)", "ecto", p.tags)

but this does not:

value="ecto"

query = from p in Post, where: fragment("'?' = ANY (?)", ^value, p.tags)

table migration is below:

create table(:post) do

  add :tags, {:array, :string}

end

I have a small project on github with everything defined if I'm not missing something obvious.

Any thoughts?

Thanks,

Thomas

[Eric Meadows-Jönsson]

It’s not going to work because all interpolated values are translated to SQL query parameters, the fragment will be translated to the SQL '$1' = ANY (p0.tags) where '$1' is just a string literal - not a parameter.

The following, without the single quotes should work:

value="ecto"
query = from p in Post, where: fragment("? = ANY (?)", ^value, p.tags)

Ecto’s query syntax support this directly with: from p in Post, where: ^value in p.tags.

​

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/b060fe5e-5e7a-47e8-b24e-ad1bc6f44162%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Eric Meadows-Jönsson

[Thomas Shelton]

Thanks for the fast response Eric.  I tried that based on your ElixirConf EU talk and while Ecto appears to handle it correctly, my version of Postgres seems to not like the query.  This is what's sent to PostgreSQL and then I receive an error.

SELECT p0."id", p0."tags" FROM "posts" AS p0 WHERE (in($1, p0."tags")) ["elixir"]

I get the error "syntax error at or near "in"" ... anything I'm missing here?  I'm running PostgreSQL 9.4.1.

Thanks again,

Thomas

--
You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/71jID-QjEoo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAM_eapiMs8hnJJPFuDA8SJy_HPBj7JNGe7FRHxq%2BdSJ2uqDY%3DQ%40mail.gmail.com.

[Eric Meadows-Jönsson]

Looks like an ecto bug to me, looking at the code now I see that ecto only accepts literal lists or interpolated values as the right hand argument to in/2. I know model fields were supported before so I don't know why it changed. Maybe José knows?

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CALwVE0pB%2By4J3uaVrXF6XPZRMj9ikG9hqh1oVqa-fEWJB9CSew%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Eric Meadows-Jönsson

[José Valim]

It is not an ecto bug because the previous behaviour was PG specific. We may add it back in the future and make the other drivers fail but I would prefer if the "first-class" api is not DB specific as possible.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAM_eaphxUf9uOkisCh2b87o4-7zOGaDLpWnifz-N%2Bcau2gEHFA%40mail.gmail.com.

[Thomas Shelton]

Thanks for the quick feedback.  Any other ideas on how to make this type of query work?  I'm happy to try and fork and correct for my issue until we can decide on overall fix but need a little direction from one of you.

Thanks,

Thomas

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4JnkpgE8D8EfgYkAf48JJ3f43MpJdvBHxSgEG576kvEAQ%40mail.gmail.com.

[José Valim]

As Eric said, the following syntax should work:

value="ecto" query = from p in Post, where: fragment("? = ANY (?)", ^value, p.tags)

Note you can also use macros in queries:

defmacro in_any(left, right) do

  quote do

    fragment("? = ANY (?)", unquote(left), unquote(right))

  end

end

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CALwVE0oGPMxDQckodrDDopam5Y8M-%3D6LwoH5MFYj-erwRXoWLA%40mail.gmail.com.

[Thomas Shelton]

I thought I had tried that before but I was able to make it work this time.  It appears if you send the direct value you need the single quotes but if you use the interpolated value you don't.

so this works:

from p in Post, where: fragment(" '?' = ANY (?)", "ecto", p.tags)

and now this works as well:

val="ecto"

from p in Post, where: fragment(" ? = ANY (?)", ^val, p.tags)

If one of you could comment on this I'll attempt to update the fragment docs.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4KBjqRg%3DbfR0j6THBpkNmiBhF8CkwEfUDC5wS%3DCagPBAw%40mail.gmail.com.

[José Valim]

The last one should work for both cases. If it does not, it is certainly a bug.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CALwVE0qtW0J5wUsSfOGKL_jb87gX35HXYndgR3tGpq-uqqn4rw%40mail.gmail.com.

[José Valim]

Actually, Eric found out the bug. It should be:

from p in Post, where: fragment("? = ANY (?)", ^"ecto", p.tags)

Or:

val="ecto"

from p in Post, where: fragment(" ? = ANY (?)", ^val, p.tags)

Our mistake is in allowing a literal string as argument. I'll fix it in master.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

[Thomas Shelton]

awesome ... thanks for all the quick feedback.  And for these tools!

Thomas

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4KYfRW8GE%2B%3DBp2-2d%2BrQa99KnTh151vN13YFQG9y_W9Xw%40mail.gmail.com.

[José Valim]

Just a heads up, Ecto master now supports:

from p in Post, where: "ecto" in p.tags

Enjoy!

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CALwVE0p7S%3DGZhTAFronaor1aFNEpweAeLtAZ81P%2BKx4qWZVOfA%40mail.gmail.com.

[Jason Harrelson]

As an extension of this question, I am trying to use the `@>` and `&&` PostgreSQL array operators.  I have attempted to use the fragment function to accomplish the task and have failed.  The example being addressed in this conversation so far is 1 item contained by an array of items.  The use case I am referring to is an array of items, where ANY or ALL are in the compared array.

An example of the SQL queries:

- The ALL variant -

SELECT *

FROM products g

WHERE g.categories::text[] @> '{sports,clothing}'::text[];

- The ANY variant -

SELECT *

FROM products p

WHERE p.categories::text[] && '{sports,clothing}'::text[];

____________________________

Given a function def categorized_as_all( categories ), do: ...

I tried the following Ecto query implementations and they did work (hardcoded, not dynamic, but a good start to build off of):

from p in Product,

where: fragment("tags::text[] @> '{sports,clothing}'::text[]")

from p in Product,

where: fragment("tags::text[] @> ?::text[]", "{sports,clothing}")

____________________________

Next, I tried the following Ecto query implementations and they did not work (seemingly blocking my path to a non-hardcoded version of the ones that worked):

categories_as_str = categories |> Enum.join(",")

from p in Product,

where: fragment("tags::text[] @> ?::text[]", categories_as_str)

categories_as_str = categories |> Enum.join(",")

from p in Product,

where: fragment("tags::text[] @> ?::text[]", ^categories_as_str)

sql = "tags::text[] @> '{sports,clothing}'::text[]"

from p in Product,

where: fragment(sql)

sql = "tags::text[] @> '{sports,clothing}'::text[]"

from p in Product,

where: fragment(^sql)

categories_as_str = categories |> Enum.join(",")

from p in Product,

where: fragment("categories @> '{#{categories_as_str}}'::text[]")

categories_as_str = "{" <> categories |> Enum.join(",") <> "}"

from p in Product,

where: fragment("categories @> '#{categories_as_str}'::text[]")

categories_as_str = "{" <> categories |> Enum.join(",") <> "}"

from p in Product,

where: fragment(^"categories @> '#{categories_as_str}'::text[]")

Some of the examples that failed were due to problematic SQL generated, others failed to compile and others raised runtime errors.

Any guidance is appreciated.  Hopefully there is some way to bend the fragment function to make this work.  Thanks!

[José Valim]

If you interpolate a string in a fragment, Ecto will send it as a string, which won't match the array type in your fragment query. I believe your first example should work as long as you give it an array and not the result of Enum.join

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/5ae3c2cc-2d6d-41c8-80cd-fd86c9606f5d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

[Sebastian Bean]

Still beating my head against this issue. Asked in the #ecto slack chat. https://elixir-lang.slack.com/archives/ecto/p1471295032003629

Simplest: 

    works = Repo.all(from w in Sebbean.Work, 

      where: fragment("? @> '{?}'", w.tags, ^tag)

    )

error: parameters must be of length 0 for query

without interpolation it works fine (like previous message in this thread)

    works = Repo.all(from w in Sebbean.Work, 

      where: fragment("? @> '{iOS}'", w.tags)

    )

Remove the single quotes and it creates a bad query:

    works = Repo.all(from w in Sebbean.Work, 

      where: fragment("? @> {?}", w.tags, ^tag)

    )

error: ERROR (syntax_error): syntax error at or near "{"

Seems like a relatively common use case - not sure if it's been resolved since this thread last year. Not finding a lot on the web regarding these @> array extensions.

-sb

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/5ae3c2cc-2d6d-41c8-80cd-fd86c9606f5d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[thomas...@gmail.com]

Hey All, 

I know this is an old post, but I'm having the same issue.

I've posted it in stack overflow here:

https://stackoverflow.com/questions/53010291/database-fragment-query-error-parameters-must-be-of-length-0-for-query