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.