Postgres: Fix IC5 - only return Posts, exclude Comments
@@ -7,7 +7,7 @@ SELECT
count(MessageId) AS postCount
FROM
(
- SELECT title, Forum.id AS forumid, friend.Person2Id
+ SELECT title, Forum.id AS ForumId, friend.Person2Id
FROM Forum, Forum_hasMember_Person,
(
SELECT Person2Id
@@ -15,9 +15,10 @@ FROM
WHERE Person1Id = :personId
UNION
SELECT k2.Person2Id
- FROM Person_knows_Person k1, Person_knows_Person k2
+ FROM Person_knows_Person k1
+ JOIN Person_knows_Person k2
+ ON k1.Person2Id = k2.Person1Id
WHERE k1.Person1Id = :personId
- AND k1.Person2Id = k2.Person1Id
AND k2.Person2Id <> :personId
) friend
WHERE Forum.id = Forum_hasMember_Person.ForumId
@@ -25,9 +26,10 @@ FROM
AND Forum_hasMember_Person.creationDate >= :minDate
) tmp
LEFT JOIN Message
- ON tmp.forumid = Message.ContainerForumId
+ ON tmp.ForumId = Message.ContainerForumId
AND CreatorPersonId = tmp.Person2Id
-GROUP BY forumid, title
-ORDER BY postCount DESC, forumid ASC
+ AND Message.ParentMessageId IS NULL
+GROUP BY ForumId, title
+ORDER BY postCount DESC, ForumId ASC
LIMIT 20
;
