Hey I am trying to achieve the following:
I have a table with records that have a status such as failed or changed, and they have a project_id, one project can have both a failed and a changed status(different records, over time).
I want to get the latest changed and latest failed records or each project and have the list ordered by insert date.
My initial idea was this:
def project_limit(query) do
failed_query =
query
|> status_in("failed")
|> distinct_project_id()
changed_query =
query
|> status_in("changed")
|> distinct_project_id()
query = union_all(failed_query, ^changed_query)
subquery(query)
|> default_order()
end
def distinct_project_id(query) do
from(p in query,
distinct: p.project_id
)
end
def status_in(query, status) do
from(p in query,
where: p.status == ^status
)
end
def default_order(query) do
from(p in query,
order_by: [desc: p.inserted_at, desc: p.id]
)
end
The results are the following:
on ubuntu the result looks great, on mac not, and according to the test it is incorrect as well:
test "get_last_changed_failed_webmap_status_check_for_projects returns latest status check even if there are multiple" do
project = Factory.insert(:project)
webmap_status_check_fixture(
inserted_at: ~N[2025-01-01 00:00:02],
status: "failed",
acknowledged: false,
project: project
)
%WebmapStatusCheck{id: id} =
webmap_status_check_fixture(
inserted_at: ~N[2025-01-01 00:00:03],
status: "failed",
acknowledged: false,
project: project
)
webmap_status_check_fixture(
inserted_at: ~N[2025-01-01 00:00:01],
status: "failed",
acknowledged: false,
project: project
)
assert [%WebmapStatusCheck{id: ^id, inserted_at: ~N[2025-01-01 00:00:03]}] =
Webmaps.get_last_changed_failed_webmap_status_check_for_projects()
end
3 gets inserted and this:
inserted_at: ~N[2025-01-01 00:00:02]
get returned
Any thoughts, or a different approach maybe?
