feat: support query grouped endpoints (e.g. `#` of users by `type` field)

[lrstanley]

Add endpoints for grouping (with filtering). Could see much more than the below example, but would be a good start.

Example request, which is something very helpful for a UI which has a filter/selection dropdown for only showing results based of users with a specific type, and showing the number of that type:

/users/countBy<Field>?<filter-options>

Example response:

{
    "results": [
        {
            "foo": 123,
            "bar": 100,
            "baz": 51
        }
    ]
}

Would be used for something like:

[lrstanley]

some more example code:

r.Get("/count-types", func(w http.ResponseWriter, r *http.Request) {
	type counter struct {
		Typ   string `json:"type"`
		Count int    `json:"count"`
	}
	var v []counter
	// Can still .Order(ent.Desc(pet.FieldType)) if we want to, but not by count.
	err := db.Debug().Pet.Query().GroupBy(pet.FieldType).Aggregate(ent.As(ent.Count(), "count")).Scan(ctx, &v)
	if err != nil {
		srv.DefaultErrorHandler(w, r, rest.OperationRead, err)
		return
	}
	slices.SortFunc(v, func(a, b counter) int {
		if a.Count == b.Count {
			return 0
		}
		if a.Count > b.Count {
			return 1
		}
		return -1
	})
	slices.Reverse(v)
	rest.JSON(w, r, http.StatusOK, v)
})

r.Get("/avg-age-by-type", func(w http.ResponseWriter, r *http.Request) {
	var v []struct {
		Typ    string  `json:"type"`
		AgeSum float64 `json:"age_sum"`
		Count  int     `json:"count"`
	}
	err := db.Pet.Query().
		GroupBy(pet.FieldType).
		Aggregate(ent.As(ent.Mean(pet.FieldAge), "age_sum"), ent.Count()).
		Scan(ctx, &v)
	if err != nil {
		srv.DefaultErrorHandler(w, r, rest.OperationRead, err)
		return
	}
	rest.JSON(w, r, http.StatusOK, v)
})

Some additional thoughts:

• Should we still support filtering for these endpoints?
  • Will make the resulting spec much larger.
  • Could reuse the List<Type>Params structs.
• Sorting? Count & the field, and that's it?

[lrstanley]

Another form for this type of endpoint could be edge-based counts. An example used on https://liam.sh is:

type LabelsCountResponse struct {
	ent.Label
	PostCount             int `json:"post_count"`
	GithubRepositoryCount int `json:"githubrepository_count"`
	Total                 int `json:"total_count"`
}

func (h *handler) getLabelsCount(w http.ResponseWriter, r *http.Request) {
	v := []*LabelsCountResponse{}

	err := h.db.Label.Query().GroupBy(label.FieldID).
		Aggregate(
			func(s *sql.Selector) string {
				s.AppendSelect(label.Columns...)

				t1 := sql.Table(label.GithubRepositoriesTable)
				s.LeftJoin(t1).On(s.C(label.FieldID), t1.C(label.GithubRepositoriesPrimaryKey[0]))
				t1count := sql.Count(sql.Distinct(t1.C(label.GithubRepositoriesPrimaryKey[1])))

				t2 := sql.Table(label.PostsTable)
				s.LeftJoin(t2).On(s.C(label.FieldID), t2.C(label.PostsPrimaryKey[0]))
				t2count := sql.Count(sql.Distinct(t2.C(label.PostsPrimaryKey[1])))

				s.AppendSelectAs(t1count, "githubrepository_count")
				s.AppendSelectAs(t2count, "post_count")
				s.AppendSelectExprAs(sql.ColumnsOp(t1count, t2count, sql.OpAdd), "total_count")
				s.OrderBy(sql.Desc("total_count"))

				return ""
			},
		).Scan(context.Background(), &v)
	if err != nil {
		chix.Error(w, r, err)
		return
	}

	chix.JSON(w, r, http.StatusOK, v)
}

Where labels <-> github_repository and labels <-> post, and we're calculating the number of edges, and accumulating the total in one query/result. This massages the results into a wrapped *ent.<type>, and includes all fields. Not sure exactly how one would eager-load in this scenario, though.