How to query database to see if a record's feild is a part of a big list

Hello all,
I have to delete an object- Billing from my sql database this Billing’s schema has a field of :name_ids with type {:array, Ecto.UUID} => field(:name_ids, {:array, Ecto.UUID}) now I want to query Billing on the basis if every element in name_ids is a part of another list(lets presume that this list name is list1) <= this makes name_ids a subarray of list1. How do I do this? Currently I have been doing this to query -

# i am first pulling all the billing to a list then I am filtering this list on the basis of above logic^
billing_ids_to_delete =
  Billing
  |> Repo.all()
  |> Enum.filter(fn %Billing{campaign_ids: campaign_ids} = billing ->
    check_if_list2_is_sublist_of_list1(list1, name_ids)
  end)
  |> Enum.map(fn billing -> billing.id end)
  
  Billing.Query.build_query([
    {:restaurant_id, billing_ids_to_delete}
  ])
  |> Repo.delete_all()

# build_query function - 
def do_where(query, [{field, value} | other_filters]) when is_list(value) do
  query
  |> where([t], field(t, ^field) in ^value)
  |> do_where(other_filters)
end

def build_query(filters) do
  Billing
  |> do_where(filters)
end

I want to optimize this and move the logic for filtering Billing to a SQL query in place of making a list of all billings and then filtering them. How do I do this?

This depends on database you are using. By default and also in most projects the PostgreSQL database is used, so my answer would be based on it, but keep in mind that it may work also in other databases with a small SQL change …

In PostgreSQL we have 2 operators @> and <@ (depending of order) that can do the job in where clause. To call SQL within Ecto.Query.API you can use fragment macro.

Here is a complete example script:

Mix.install([:ecto_sql, :postgrex])

defmodule Migration do
  use Ecto.Migration

  def change do
    create table("tests") do
      add(:array, {:array, :integer})
    end
  end
end

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :my_app
end

defmodule Test do
  use Ecto.Schema

  schema "tests" do
    field(:array, {:array, :integer})
  end
end

defmodule Example do
  alias Ecto.Query
  require Query

  def cleanup do
    Repo.stop()
  end

  def prepare do
    Application.put_env(:my_app, Repo,
      database: "example",
      password: "postgres",
      username: "postgres"
    )

    _ = Repo.__adapter__().storage_down(Repo.config())
    :ok = Repo.__adapter__().storage_up(Repo.config())
    {:ok, _} = Supervisor.start_link([Repo], strategy: :one_for_one)
    Ecto.Migrator.up(Repo, 0, Migration)
  end

  def sample do
    list = [3, 4]

    Test
    |> Query.from(as: :test)
    |> Query.where([test: test], fragment("? @> ?", test.array, ^list))
    |> Repo.delete_all()

    Test |> Repo.all() |> IO.inspect()
  end

  def seed do
    Repo.insert(%Test{array: [1, 2, 3]})
    Repo.insert(%Test{array: [3, 4, 5]})
    Repo.insert(%Test{array: [6, 7, 8]})
  end
end

Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()

An output preview after calling above script:
obraz

Helpful resources:

  1. Ecto.Query.API
  2. Ecto.Query.API.fragment/1
  3. Array Functions and Operators | PostgreSQL current (14)
1 Like

thanks @Eiji, also the links you shared really helped me out.