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()
