tommasop

tommasop

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.

Showing Posts 1 to 1

tommasop

tommasop OP

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

— All posts loaded —

Where Next? Top

Trending in Questions Top

RSP87
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
New
nseaSeb
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New
ryanwinchester
apply_graft/2 doesn’t rewrite an add_many sub-workflow’s deps on an add step. Grafted jobs cancel with “upstream job was deleted” Version...
New

Other Trending Topics Top

JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
marciok
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
Dmk
Xamal is a deployment tool for Elixir apps that deploys native releases to bare metal servers over SSH. It’s a port of GitHub - basecamp/...
New

Latest on Elixir Forum

Elixir Forum

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews