---
models/base_url.go | 14 ++++++++++----
models/org_link.go | 9 ++++++---
2 files changed, 16 insertions(+), 7 deletions(-)
diff --git a/models/base_url.go b/models/base_url.go
index 926a75d..cde810f 100644
--- a/models/base_url.go
+++ b/models/base_url.go
@@ -59,19 +59,25 @@ func GetBaseURLs(ctx context.Context, opts *database.FilterOptions) ([]*BaseURL,
opts = &database.FilterOptions{}
}
tz := timezone.ForContext(ctx)
- tagOrder := TagRelationOrderString(GetDefaultTagOrder(ctx))
urls := make([]*BaseURL, 0)
if err := database.WithTx(ctx, database.TxOptionsRO, func(tx *sql.Tx) error {
q := opts.GetBuilder(nil)
rows, err := q.
Columns("b.id", "b.url", "b.title", "b.counter", "b.data", "b.public_ready", "b.hash",
"b.parse_attempts", "b.last_parse_attempt", "b.created_on", "b.updated_on", "b.visibility",
- fmt.Sprintf("json_agg(CASE WHEN t.id IS NOT NULL THEN json_build_object('id', t.id, 'name', tl.name, 'slug', t.slug, 'createdOn', t.created_on) END ORDER BY %s)::jsonb", tagOrder)).
+ `COALESCE((
+ SELECT json_agg(t ORDER BY LOWER(t.name) ASC) FROM (
+ SELECT t.id, MIN(tl.name) AS name, t.slug, t.created_on AS "createdOn"
+ FROM tag_links tl
+ JOIN org_links _ol ON _ol.id = tl.org_link_id
+ JOIN tags t ON t.id = tl.tag_id
+ WHERE _ol.base_url_id = b.id
+ GROUP BY t.id, t.slug, t.created_on
+ ) t
+ ), '[]'::json)::jsonb`).
From("base_urls b").
LeftJoin("org_links ol ON ol.base_url_id = b.id").
LeftJoin("organizations o ON o.id = ol.org_id").
- LeftJoin("tag_links tl ON tl.org_link_id = ol.id").
- LeftJoin("tags t ON t.id = tl.tag_id").
GroupBy("b.id").
Distinct().
PlaceholderFormat(database.GetPlaceholderFormat()).
diff --git a/models/org_link.go b/models/org_link.go
index 19a0a1e..0b83a3c 100644
--- a/models/org_link.go
+++ b/models/org_link.go
@@ -37,14 +37,17 @@ func GetOrgLinks(ctx context.Context, opts *database.FilterOptions) ([]*OrgLink,
rows, err := q.
Columns("ol.id", "ol.title", "ol.url", "ol.description", "ol.base_url_id", "ol.org_id", "ol.user_id",
"ol.visibility", "ol.unread", "ol.starred", "ol.archive_url", "ol.type", "ol.hash",
- "ol.created_on", "ol.updated_on", "o.slug", "u.full_name", fmt.Sprintf("json_agg(CASE WHEN t.id IS NOT NULL THEN json_build_object('id', t.id, 'name', tl.name, 'slug', t.slug, 'createdOn', t.created_on) END ORDER BY %s)::jsonb", tagOrder), "b.data", "b.counter", "b.hash").
+ "ol.created_on", "ol.updated_on", "o.slug", "u.full_name", fmt.Sprintf(`COALESCE((
+ SELECT json_agg(json_build_object('id', t.id, 'name', tl.name, 'slug', t.slug, 'createdOn', t.created_on) ORDER BY %s)
+ FROM tag_links tl
+ JOIN tags t ON t.id = tl.tag_id
+ WHERE tl.org_link_id = ol.id
+ ), '[]'::json)::jsonb`, tagOrder), "b.data", "b.counter", "b.hash").
From("org_links ol").
Join("organizations o ON o.id = ol.org_id").
Join("users u ON ol.user_id = u.id").
Join("base_urls b ON b.id = ol.base_url_id").
LeftJoin("organization_users ou ON ou.org_id = ol.org_id").
- LeftJoin("tag_links tl ON tl.org_link_id = ol.id").
- LeftJoin("tags t ON t.id = tl.tag_id").
LeftJoin("followers f ON f.org_id = ol.org_id").
GroupBy("ol.id", "o.slug", "u.full_name", "b.data", "b.counter", "b.hash").
Distinct().
--
2.54.0