Ecto fragment for array of strings containig an array of strings

Questions / Help
, ,
1

I have an Ecto schema with a field that is an array of strings. The underlying database is PostgreSQL.

The field is an array of actually spoken languages, this is the migration definition:

    alter table(:users) do
      add(:spoken_languages, {:array, :string})
    end

and this the schema definition:

  schema "user" do
    field :email, :string
    field :first_name, :string
    field :last_name, :string
    field :spoken_languages, {:array, :string}
    timestamps()
  end

I am using JSON:API as a standard and have filters like the following:

http://my_api/users?filter[spoken_languages]=it,es

This postgresql query works:

 SELECT t0."id", t0."email", t0."first_name", t0."last_name", t0."spoken_languages", t0."inserted_at", t0."updated_at" FROM "users" AS t0 WHERE
 (TRUE AND t0."spoken_languages" @> string_to_array('it,es', ',')::varchar[])

but this Ecto query does not work:

  def list_tuners(params) do
    Tuner
    |> where(^filter_where(params))
    |> Repo.all()
  end

def filter_where(params) do
    Enum.reduce(params["filter"], dynamic(true), fn
      {"email", value}, dynamic ->
        dynamic([q], ^dynamic and q.email == ^value)

      {"spoken_languages", value}, dynamic ->
        dynamic(
          [q],
          ^dynamic and
            fragment(
              "? @> string_to_array(?, ',')::varchar[]",
              q.spoken_languages,
              ^value
            )
        )

      {_, _}, dynamic ->
        # Not a where parameter
        dynamic
    end)
  end

This is the generated query:

SELECT t0."id", t0."email", t0."first_name", t0."last_name", t0."spoken_languages", t0."inserted_at", t0."updated_at" FROM "users" AS t0 WHERE (TRUE AND t0."spoken_languages" @> string_to_array($1, ',')::varchar[]) ["it,es"]

It seems like params interpolation is failing because it returns an empty array.

The query seems to be fine and I really don’t have a clue of why it is not working.

Thanks for your help.

2

Turns out the query was fine and the problem was in the tests :smile: