How to order by number of items with sectioning through relationships #556

Hi @tiendq, I'm not sure I understand how this grouping is supposed to work. You want to group reminders by their title and then order those sections by how many reminders are in each section? So something like this:

Personal
---------
Reminder1
Reminder2
Reminder3
Reminder4

Business
---------
Reminder5
Reminder6
Reminder7

Urgent
---------
Reminder8

?

Write a custom FetchKeyRequest that orders by a count subquery in SQL and folds the rows into sections in Swift. The order stays in SQLite, and nothing is sorted in the view.

struct RemindersByListSize: FetchKeyRequest {
  struct Section: Identifiable, Sendable {
    var list: RemindersList
    var reminders: [Reminder]
    var id: RemindersList.ID { list.id }
  }

  func fetch(_ db: Database) throws -> [Section] {
    let rows = try Reminder
      .join(RemindersList.all) { $0.remindersListID.eq($1.id) }
      .order { reminder, list in
        (
          Reminder.where { $0.remindersListID.eq(list.id) }.count().desc(),
          list.title.asc(),
          list.id.asc(),
          reminder.title.asc()
        )
      }
      .select { ($1, $0) }
      .fetchAll(db)

    // Rows arrive grouped by list, so consecutive rows with the same list are one section.
    var sections: [Section] = []
    for (list, reminder) in rows {
      if sections.last?.list.id == list.id {
        sections[sections.count - 1].reminders.append(reminder)
      } else {
        sections.append(Section(list: list, reminders: [reminder]))
      }
    }
    return sections
  }
}

Use it with @Fetch:

@Fetch(RemindersByListSize()) var sections = []

List {
  ForEach(sections) { section in
    Section(section.list.title) {
      ForEach(section.reminders) { reminder in
        Text(reminder.title)
      }
    }
  }
}

The generated query ends with:

ORDER BY (SELECT count(*) FROM "reminders" WHERE (("reminders"."remindersListID") = ("remindersLists"."id"))) DESC,
  "remindersLists"."title" ASC, "remindersLists"."id" ASC, "reminders"."title" ASC

With Personal (4), Business (3) and Urgent (1) reminders it returns Personal, Business, Urgent. After inserting 3 more Business reminders it re-sorts to Business (6), Personal (4), Urgent (1) with no manual reload, because @Fetch observes the query like @FetchAll does. Lists with the same count are ordered by title, then by id. The id term matters: without it, two lists with the same title and count interleave their reminders and come back as duplicate sections.

sectionBy: can't do this because it takes one expression, and that expression is both the section's name and the first ORDER BY term (_Sectioning, docs). Ordering by count makes the count the name, which is what happened with your counts table.

The subquery counts every reminder in the list, so a where on the outer query doesn't change the order. Lists with no reminders aren't returned, same as your inner join.

Tested with SQLiteData 1.12.1 and swift-structured-queries 0.39.2 on Swift 6.3.3 (macOS).