Query Chatter Feed, Topics, and Groups

Question

First off, my end goal is lofty and time-consuming, but we need metrics and this is the best way to do it (that I can think of). Originally, a chatter post was created on the quote, case, or where making a request and the end user marked it with a topic. An answer would be given and then done. Now, we've moved to a group. So my question: What is the best/most efficient way to query FeedItem, FeedComment, Topic, and CollaborationGroup? I would like to see all posts with a certain topic and their comments. Then I'd like to see all posts from the group with their comments. I'll then use the createddate to gather metrics. I've been able to do individual queries in the developer console, but I'd like to send these all to a .csv (I have the apex code to do that) and possibly insert into a local SQL db. I just can't find the relationships between feeditem and topic, feedcomment and topic. Here are my queries so far:

ChatterActivity
0ca
select Id, CommentCount, CommentReceivedCount, InfluenceRawRank, LikeReceivedCount, ParentId, PostCount from ChatterActivity limit 100

FeedComment
0D7
SELECT Id, CommentBody, CreatedDate, CreatedById, FeedItemId, ParentId FROM FeedComment

FeedItem
0D5
select Id, Body, CommentCount, CreatedDate, LikeCount, LinkUrl, ParentId, RelatedRecordId, Title, Type from FeedItem WHERE Type = 'TextPost'

Topic
0TO
select Id, Name, CreatedById, CreatedDate, Description, SystemModstamp from Topic

CollaborationGroup
0F9
select Id, Name, Description, InformationBody, InformationTitle from CollaborationGroup

CollaborationGroupFeed
0D5
select Id, Body, CommentCount, CreatedById, CreatedDate, LikeCount, LinkUrl, ParentId, RelatedRecordId, Title, Type from CollaborationGroupFeed

Update: TopicAssignment Query

select id, topicid, entityid, entitykeyprefix, entitytype, createddate, createdbyid from TopicAssignment

Update: My Solution

Here is the beginning framework for my solution, and how to get it to display in debug. Obviously record count will get too high, but that's the nth step down the road.

List<FeedItem> chatFeed = [SELECT  Id, Body, CommentCount, CreatedDate, LikeCount, LinkUrl, ParentId, RelatedRecordId, Title, Type,
    (SELECT Id, CommentBody, CreatedDate, CreatedById, FeedItemId, ParentId FROM FeedComments)
FROM FeedItem 
WHERE Id IN (
    SELECT EntityId FROM TopicAssignment
    WHERE EntityType = 'FeedItem'
    AND TopicId = '<TOPIC_ID_HERE>'
) LIMIT 100
];

For(FeedItem fi : chatFeed) {
    System.debug(fi);
    For (FeedComment fc : fi.FeedComments) {
        System.debug(fc);
    }
}

Answer 1

Trusty old ChildRelationship to the rescue:

for (ChildRelationship relation : SObjectType.Topic.getChildRelationships())
    system.debug(relation);

Relevant relationships and properties:

[getChildSObject=FeedComment;getField=ParentId;getRelationshipName=null;]
[getChildSObject=FeedItem;getField=ParentId;getRelationshipName=null;]

You should just be able to do:

SELECT Body, BestCommentId, CommentCount, LikeCount, Type,
    (SELECT CommentBody FROM FeedComments)
FROM FeedItem WHERE ParentId = '<topic_id_here>'

If you want to filter on multiple Topic parents, you could change your WHERE clause to:

ParentId IN ('<topic1_id>', '<topic2_id>', 'etc.')

You can also do a Left Inner Join to see which records have a relevant TopicAssignment:

SELECT ... FROM FeedItem WHERE Id IN (
    SELECT EntityId FROM TopicAssignment
    WHERE EntityType = 'FeedItem'
    AND TopicId IN ('<id1>', 'etc.')
)

Answer 2

Following up on Adrian's answer, if you want to use this in SOQL you need the API Name of the relation. Here is a generalized implementation of his solution:

DescribeSObjectResult type = SobjectType.Account;
String filter = 'Feed';
  for (ChildRelationship relation : type.getChildRelationships()) {
  if (String.valueOf(relation).contains(filter))
    system.debug('* ' + relation.getChildSObject() + ' = ' + relation.getRelationshipName());
}

Results:

12:49:03.23 (77283325)|USER_DEBUG|[5]|DEBUG|* AccountFeed = Feeds
12:49:03.23 (90082901)|USER_DEBUG|[5]|DEBUG|* EntitySubscription = FeedSubscriptionsForEntity
12:49:03.23 (91856038)|USER_DEBUG|[5]|DEBUG|* FeedComment = null
12:49:03.23 (92184455)|USER_DEBUG|[5]|DEBUG|* FeedItem = null
12:49:03.23 (116801566)|USER_DEBUG|[5]|DEBUG|* WorkFeedbackRequest = RelatedObjects