changes to catalog db queries

2 files changed, 8 insertions, 37 deletions

diff --git a/Database.go b/Database.go
index b9adda3..e33e71f 100644
--- a/Database.go
+++ b/Database.go
@@ -406,7 +406,7 @@ func GetObjectFromDBPage(db *sql.DB, id string, page int) Collection {
 	var nColl Collection
 	var result []ObjectBase
 
-	query := `select count (x.id) over(), x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor from (select * from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select * from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select * from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc limit 8 offset $2`
+	query := `select count (x.id) over(), x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc limit 8 offset $2`
 
 	rows, err := db.Query(query, id, page * 8)	
 
@@ -496,7 +496,7 @@ func GetObjectFromDBCatalog(db *sql.DB, id string) Collection {
 	var nColl Collection
 	var result []ObjectBase
 
-	query := `select id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' order by updated asc`
+	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc`	
 
 	rows, err := db.Query(query, id)	
 
@@ -847,7 +847,7 @@ func GetObjectRepliesCount(db *sql.DB, parent ObjectBase) (int, int) {
 	var countId int
 	var countImg int
 
-	query := `select count(id) from replies where inreplyto=$1 and id in (select id from activitystream where type='Note' union select id from cacheactivitystream where type='Note')`
+	query := `select count(x.id) over(), sum(case when RTRIM(x.attachment) = '' then 0 else 1 end) over() from (select id, attachment from activitystream where id in (select id from replies where inreplyto=$1) and type='Note' union select id, attachment from cacheactivitystream where id in (select id from replies where inreplyto=$1) and type='Note') as x`
 	
 	rows, err := db.Query(query, parent.Id)	
 	
@@ -855,17 +855,7 @@ func GetObjectRepliesCount(db *sql.DB, parent ObjectBase) (int, int) {
 
 	defer rows.Close()
 	rows.Next()
-	rows.Scan(&countId)
-
-	query = `select count(attach) from (select attachment from activitystream where id in (select id from replies where inreplyto=$1) and attachment != '' union select attachment from cacheactivitystream where id in (select id from replies where inreplyto=$1) and attachment != '') as attach`
-	
-	rows, err = db.Query(query,  parent.Id)	
-
-	CheckError(err, "error with select attachment count db query")
-	
-	defer rows.Close()	
-	rows.Next()
-	rows.Scan(&countImg)
+	rows.Scan(&countId, &countImg)
 
 	return countId, countImg
 }
diff --git a/client.go b/client.go
index 6625888..11a187f 100644
--- a/client.go
+++ b/client.go
@@ -150,28 +150,6 @@ func CatalogGet(w http.ResponseWriter, r *http.Request, db *sql.DB, collection C
 	
 	actor := collection.Actor
 
-	var mergeCollection Collection
-
-
-
-	mergeCollection.OrderedItems = collection.OrderedItems
-
-	domainURL := GetDomainURL(*actor)
-
-	if domainURL == Domain {
-		followCol := GetObjectsFromFollow(db, *actor)	
-		for _, e := range followCol {
-			if e.Type != "Tombstone" {			
-				mergeCollection.OrderedItems = append(mergeCollection.OrderedItems, e)
-			}
-		}
-	}
-
-	DeleteRemovedPosts(db, &mergeCollection)
-	DeleteTombstonePosts(&mergeCollection)
-
-	sort.Sort(ObjectBaseSortDesc(mergeCollection.OrderedItems))
-	
 	var returnData PageData
 	returnData.Board.Name = actor.Name
 	returnData.Board.PrefName = actor.PreferredUsername
@@ -193,7 +171,10 @@ func CatalogGet(w http.ResponseWriter, r *http.Request, db *sql.DB, collection C
 
 	returnData.Boards = Boards
 
-	returnData.Posts = mergeCollection.OrderedItems
+	DeleteRemovedPosts(db, &collection)
+	DeleteTombstonePosts(&collection)
+
+	returnData.Posts = collection.OrderedItems
 
 	t.ExecuteTemplate(w, "layout",  returnData)
 }