Selecting fields in query with preloaded data

Phoenix Forum Questions / Help
,
1

How can I select fields and add custom fields in Repo.all query with preloaded data.

I have this query

    Repo.all from p in EmployeeProfile,
    join: c in assoc(p, :employee_checklists),
    preload: [employee_checklists: c],
    select: [:name, :id],
    select_merge: %{total_checklist_items: count(c)},
    group_by: [p.id, c.id]

When executed the results are below: (a)

[
  %{
    total_checklist_items: 1,
    employee_checklists: [
      %Prepply.Employees.EmployeeChecklist{
        __meta__: #Ecto.Schema.Metadata<:loaded, "employee_checklists">,
        checklist_item: #Ecto.Association.NotLoaded<association :checklist_item is not loaded>,
        checklist_item_id: 2,
        employee_profile: #Ecto.Association.NotLoaded<association :employee_profile is not loaded>,
        employee_profile_id: 3,
        file_path: nil,
        id: 2,
        inserted_at: ~N[2020-12-29 10:33:10],
        status: "completed",
        updated_at: ~N[2020-12-29 10:33:10]
      },
      %Prepply.Employees.EmployeeChecklist{
        __meta__: #Ecto.Schema.Metadata<:loaded, "employee_checklists">,
        checklist_item: #Ecto.Association.NotLoaded<association :checklist_item is not loaded>,
        checklist_item_id: 1,
        employee_profile: #Ecto.Association.NotLoaded<association :employee_profile is not loaded>,
        employee_profile_id: 3,
        file_path: nil,
        id: 1,
        inserted_at: ~N[2020-12-29 10:33:10],
        status: "",
        updated_at: ~N[2020-12-29 10:33:10]
      }
    ],
    id: 3,
    name: "..."
  }
]

but what I want to achieve is something like this: (b)

[
  %{
    total_checklist_items: 2,
    completed_checklists: 1 (if one of CheckListItem struct has status = "completed"
    id: 3,
    name: "..."
  }
]

how can I achieve something like (b)?

2

If I understand correctly you want to count all records from resulting joined table AND count completed items:
Only plain SQL I can come up with right now should be something like

    SELECT COUNT(*) AS total_checklist_items, 
           SUM(CASE WHEN c.status = "completed" THEN 1 ELSE 0 END) AS completed_checklists
           ep.id,
           ep.name
      FROM employee_profile ep 
INNER JOIN employee_checklists ec ON ec.emplowyee_profile_id = ep.id;

Note how we count completed items with case in sum… that part can be achieved in Ecto with fragments… something like:

Repo.all from p in EmployeeProfile,
  join: c in assoc(p, :employee_checklists),
  select: %{
    total_checklist_items: count(c), 
    completed_checklists: sum(fragment("CASE WHEN ? = ? THEN 1 ELSE 0 END", c.status, "completed")),
    id: p.id,
    name: p.name
  }