I have two schemas which look similar to the example below:
defmodule MyApp.Delivery do
use Ecto.Schema
schema "deliveries" do
# Define your fields here
has_many :delivery_statuses, MyApp.DeliveryStatus
timestamps()
end
end
defmodule MyApp.DeliveryStatus do
use Ecto.Schema
schema "delivery_status" do
field :insert_date, :naive_datetime
field :status, :string
field :some_attribute, :string
belongs_to :delivery, MyApp.Delivery
timestamps()
end
end
I’m attempting to write a queries:
- Fetch a delivery by id and preload the latest status
- Fetch a list of deliveries where the latest status matches a status parameter
- Fetch a list of deliveries where the latest status is in a list or not in a list (give me all deliveries where status is A, B or C)
The first one I have working I believe:
# Define the subquery to get the latest status for each delivery
latest_statuses = from ds in MyApp.DeliveryStatus,
select: %{delivery_id: ds.delivery_id, latest_date: max(ds.insert_date)},
group_by: ds.delivery_id
# Define the main query to join with deliveries and filter based on the status of the latest status
query = from d in MyApp.Delivery,
join: ds_max in subquery(latest_statuses), on: d.id == ds_max.delivery_id,
join: ds in MyApp.DeliveryStatus, on: ds.delivery_id == ds_max.delivery_id and ds.insert_date == ds_max.latest_date,
preload: [delivery_statuses: ds],
select: {d, ds}
# Execute the query
result = MyApp.Repo.all(query)
However trying to filter by ds.status
doesn’t work:
query = from d in MyApp.Delivery,
join: ds_max in subquery(latest_statuses), on: d.id == ds_max.delivery_id,
join: ds in MyApp.DeliveryStatus, on: ds.delivery_id == ds_max.delivery_id and ds.insert_date == ds_max.latest_date,
where: ds.status == "desired_status",
preload: [delivery_statuses: ds],
select: {d, ds}
Rather than returning no results it returns the delivery with the DeliveryStatus that matches a parameter which is not always the latest one.
To summarize I am attempting to select objects based upon conditions in both the parent and child record but wish to return nothing if both conditions are not met.