Hi,
I’m new to ecto and working with an old and large codebase. I am trying to modify this function that queries the db and results in a count of active truckloads:
def query_active_truck_load_counts do
from(
well in Well,
where: is_nil(well.deleted_at),
where: well.closed == false,
left_join: truck_load in TruckLoad,
on: well.id == truck_load.well_id and truck_load.status != "trailer_dropped",
left_join: fulfillment_authorization in assoc(truck_load, :fulfillment_authorization),
left_join: dispatch_event in TruckLoadEvent,
on:
truck_load.id == dispatch_event.truck_load_id and is_nil(dispatch_event.deleted_at) and
dispatch_event.status == ^TruckLoadEventStatuses.dispatch(),
left_join: unloaded_event in TruckLoadEvent,
on:
truck_load.id == unloaded_event.truck_load_id and is_nil(unloaded_event.deleted_at) and
unloaded_event.status == ^TruckLoadEventStatuses.unloaded(),
left_join: rerouted_event in TruckLoadEvent,
on:
truck_load.id == rerouted_event.truck_load_id and is_nil(rerouted_event.deleted_at) and
rerouted_event.status == ^TruckLoadEventStatuses.rerouted(),
where: not (is_nil(fulfillment_authorization.accepted_at) and is_nil(dispatch_event.id)),
where: is_nil(unloaded_event.id),
where: is_nil(rerouted_event.id),
where: is_nil(truck_load.deleted_at),
group_by: well.id,
select: %{well_id: well.id, active_truck_loads: count(truck_load.id)}
)
end
I need to modify this function so that when status=“off_duty” in the driver_cycle_statuses table, the count does not increase. I know I have to do a join to account for this table, but where can I add it?