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

Reply via email to