Series API endpoint generates multiple queries for each Series object to fetch all related objects. Add relevant relations to Series model and load them with Relations method of SelectQuery. This makes the endpoint generate a single query that fetches all relevant data.
Signed-off-by: Franciszek Stachura <[email protected]> --- pkg/api/series.go | 22 +++++++++++---- pkg/api/util.go | 72 +++++++++++------------------------------------ pkg/db/db.go | 7 +++++ pkg/db/models.go | 34 ++++++++++++---------- 4 files changed, 58 insertions(+), 77 deletions(-) diff --git a/pkg/api/series.go b/pkg/api/series.go index 971f4a3..a880e1e 100644 --- a/pkg/api/series.go +++ b/pkg/api/series.go @@ -71,8 +71,7 @@ func (h *handler) ListSeries( offset, perPage := input.offsetPage(h.cfg) var series []db.Series - if err := sq.Model(&series). - Relation("Submitter").Relation("Project"). + if err := defaultSeriesRelations(sq.Model(&series)). OrderExpr("series.id DESC").Offset(offset).Limit(perPage).Scan(ctx); err != nil { log.Errorf("list series: %v", err) return nil, huma.Error500InternalServerError("Internal error.") @@ -106,8 +105,7 @@ func (h *handler) LetSeries( q := db.GetQueries(ctx) var s db.Series - if err := q.Select(&s). - Relation("Submitter").Relation("Project"). + if err := defaultSeriesRelations(q.Select(&s)). Where("series.id = ?", input.ID).Scan(ctx); err != nil { return nil, huma.Error404NotFound("Not found.") } @@ -171,8 +169,7 @@ func (h *handler) UpdateSeries( } // Re-fetch with relations after update. - if err := q.Select(&s). - Relation("Submitter").Relation("Project"). + if err := defaultSeriesRelations(q.Select(&s)). Where("series.id = ?", input.ID).Scan(ctx); err != nil { return nil, huma.Error404NotFound("Not found.") } @@ -299,3 +296,16 @@ func coverToEmbedded(c *db.Cover, project *db.Project, base string) CoverEmbedde } return e } + +func defaultSeriesRelations(q *bun.SelectQuery) *bun.SelectQuery { + return q. + Relation("Submitter"). + Relation("Project"). + Relation("SeriesMetadata"). + Relation("SeriesDependencies"). + Relation("SeriesDependants"). + Relation("NextSeries"). + Relation("CoverLetter", db.WithColumns("id", "msgid", "date", "name")). + Relation("Patches", db.WithColumns("id", "msgid", "date", "name", "series_id")). + Relation("Patches", db.WithColumns("id", "msgid", "date", "name", "series_id")) +} diff --git a/pkg/api/util.go b/pkg/api/util.go index 961a554..f71d40b 100644 --- a/pkg/api/util.go +++ b/pkg/api/util.go @@ -45,64 +45,28 @@ func personToEmbedded(p *db.Person, base string) PersonEmbedded { } } +// Fills JSON-related fields of Series. Requires relevant relation fields to be loaded +// (Patches, SeriesMetadata, SeriesDependencies, SeriesDependents, NextSeries). func loadSeriesDetail(ctx context.Context, q *db.Queries, base string, series []db.Series) { for i := range series { s := &series[i] - count, err := q.Select((*db.Patch)(nil)). - Where("series_id = ?", s.ID). - Count(ctx) - if err != nil { - log.Errorf("count series patches: %v", err) - } - s.ReceivedTotal = int(count) - s.ReceivedAll = count >= int64(s.Total) - - if s.CoverLetterID != nil { - var cover db.Cover - if err := q.Select(&cover). - Where("id = ?", *s.CoverLetterID). - Scan(ctx); err == nil { - s.CoverLetter = &cover - } - } - - patches, err := q.ListSeriesPatches(s.ID) - if err != nil { - log.Errorf("load series patches: %v", err) - } - if patches == nil { - patches = []db.Patch{} - } - s.Patches = patches + s.ReceivedTotal = int(len(s.Patches)) + s.ReceivedAll = len(s.Patches) >= s.Total - s.Metadata, err = q.GetSeriesMetadata(s.ID) - if err != nil { - log.Errorf("load series metadata: %v", err) + s.Metadata = make(map[string]string) + for _, r := range s.SeriesMetadata { + s.Metadata[r.Key] = r.Value } - var depIDs []int - if err := q.Select((*db.SeriesDependencies)(nil)). - Column("to_series_id"). - Where("from_series_id = ?", s.ID). - Scan(ctx, &depIDs); err != nil { - log.Errorf("load series dependencies: %v", err) - } - s.Dependencies = make([]string, len(depIDs)) - for j, id := range depIDs { - s.Dependencies[j] = fmt.Sprintf("%s/series/%d", base, id) + s.Dependencies = make([]string, len(s.SeriesDependencies)) + for j, d := range s.SeriesDependencies { + s.Dependencies[j] = fmt.Sprintf("%s/series/%d", base, d.ID) } - var revIDs []int - if err := q.Select((*db.SeriesDependencies)(nil)). - Column("from_series_id"). - Where("to_series_id = ?", s.ID). - Scan(ctx, &revIDs); err != nil { - log.Errorf("load series dependents: %v", err) - } - s.Dependents = make([]string, len(revIDs)) - for j, id := range revIDs { - s.Dependents[j] = fmt.Sprintf("%s/series/%d", base, id) + s.Dependents = make([]string, len(s.SeriesDependants)) + for j, d := range s.SeriesDependants { + s.Dependents[j] = fmt.Sprintf("%s/series/%d", base, d.ID) } if s.PreviousSeriesID != nil { @@ -110,13 +74,9 @@ func loadSeriesDetail(ctx context.Context, q *db.Queries, base string, series [] s.PreviousSeries = &u } - nextSeriesList, err := q.ListNextSeries(s.ID) - if err != nil { - log.Errorf("load next series: %v", err) - } - s.NextSeries = make([]string, len(nextSeriesList)) - for j, ns := range nextSeriesList { - s.NextSeries[j] = fmt.Sprintf("%s/series/%d", base, ns.ID) + s.NextSeriesLinks = make([]string, len(s.NextSeries)) + for j, ns := range s.NextSeries { + s.NextSeriesLinks[j] = fmt.Sprintf("%s/series/%d", base, ns.ID) } } } diff --git a/pkg/db/db.go b/pkg/db/db.go index 4617e31..26362a4 100644 --- a/pkg/db/db.go +++ b/pkg/db/db.go @@ -316,3 +316,10 @@ func IsUniqueViolation(err error) bool { strings.Contains(msg, "duplicate key") || strings.Contains(msg, "duplicate entry") } + +// Helper to pass list of columns to bun.SelectQuery.Relation +func WithColumns(columns ...string) func(q *bun.SelectQuery) *bun.SelectQuery { + return func(q *bun.SelectQuery) *bun.SelectQuery { + return q.Column(columns...) + } +} diff --git a/pkg/db/models.go b/pkg/db/models.go index cefd923..6c9ba00 100644 --- a/pkg/db/models.go +++ b/pkg/db/models.go @@ -151,21 +151,25 @@ type Series struct { Version int `bun:"version,notnull" json:"version"` Total int `bun:"total,notnull" json:"total"` - Submitter *Person `bun:"rel:belongs-to,join:submitter_id=id" json:"submitter,omitempty"` - Project *Project `bun:"rel:belongs-to,join:project_id=id" json:"project,omitempty"` - - URL string `bun:"-" json:"url,omitempty"` - WebURL string `bun:"-" json:"web_url,omitempty"` - MboxURL string `bun:"-" json:"mbox,omitempty"` - ReceivedTotal int `bun:"-" json:"received_total"` - ReceivedAll bool `bun:"-" json:"received_all"` - CoverLetter *Cover `bun:"-" json:"cover_letter"` - Patches []Patch `bun:"-" json:"patches"` - Metadata map[string]string `bun:"-" json:"metadata"` - Dependencies []string `bun:"-" json:"dependencies"` - Dependents []string `bun:"-" json:"dependents"` - PreviousSeries *string `bun:"-" json:"previous_series"` - NextSeries []string `bun:"-" json:"next_series"` + Submitter *Person `bun:"rel:belongs-to,join:submitter_id=id" json:"submitter,omitempty"` + Project *Project `bun:"rel:belongs-to,join:project_id=id" json:"project,omitempty"` + Patches []Patch `bun:"rel:has-many,join:id=series_id" json:"patches"` + SeriesMetadata []SeriesMetadata `bun:"rel:has-many,join:id=series_id" json:"-"` + SeriesDependencies []SeriesDependencies `bun:"rel:has-many,join:id=from_series_id" json:"-"` + SeriesDependants []SeriesDependencies `bun:"rel:has-many,join:id=to_series_id" json:"-"` + NextSeries []Series `bun:"rel:has-many,join:id=previous_series_id" json:"-"` + CoverLetter *Cover `bun:"rel:has-one,join:cover_letter_id=id" json:"cover_letter"` + + URL string `bun:"-" json:"url,omitempty"` + WebURL string `bun:"-" json:"web_url,omitempty"` + MboxURL string `bun:"-" json:"mbox,omitempty"` + ReceivedTotal int `bun:"-" json:"received_total"` + ReceivedAll bool `bun:"-" json:"received_all"` + Metadata map[string]string `bun:"-" json:"metadata"` + Dependencies []string `bun:"-" json:"dependencies"` + Dependents []string `bun:"-" json:"dependents"` + PreviousSeries *string `bun:"-" json:"previous_series"` + NextSeriesLinks []string `bun:"-" json:"next_series"` } type SeriesMetadata struct { -- 2.56.0 _______________________________________________ Patchwork mailing list [email protected] https://lists.ozlabs.org/listinfo/patchwork
