6

I am trying to get a simple list of who in chatter commented on which authors post and I am struggling to get a correct SOQL. I want something like this

Bob -> Dave
Dave -> Martha
Bruce -> Martha

...

meaning that Bruce commented a post by Martha.

I expected this SOQL to do the job but miserably failed ;)

select CreatedBy.Name, FeedItem.CreatedBy.Name from FeedComment

I know that it works the other way around using

SELECT CreatedBy.Name, LikeCount, (SELECT CreatedBy.Name FROM FeedComments) 
FROM FeedItem
ORDER BY LikeCount DESC

but I am having a hard time translating that into a flat csv-like format.

3

Have you tried a little post processing? Haven't tried this, but should work with small fixes.

FeedItem[] items = [SELECT CreatedBy.Name, LikeCount, (SELECT CreatedBy.Name FROM FeedComments) 
FROM FeedItem
ORDER BY LikeCount DESC];

// Map to store values
Map<String, List<String>> commentersMap = new Map<String, List<String>>();

// map the keys to a empty list to avoid nulls later
for (FeedItem item:items) {
    commentersMap.put(item.CreatedBy.Name, new List<String>());
}


for (FeedItem item:items) {
    for (FeedComment comment : item.FeedComments) {
        commentersMap.get(item.CreatedBy.Name).add(comment.CreatedBy.Name);
    }
}

Then you can use the map to retrieve what you wan't:

for (String author: commentersMap.keySet()) {
    for (String commenter : commentersMap.get(author)) {
        System.debug(commenter + ' -> ' + author);
    }
}
1
  • 1
    From what I have seen and tried, this is really the only way to do it. There is no flat, single SOQL statement you can run to get this information currently. – Jesse Altman Mar 18 '13 at 18:22
1

You can get the author's ID (with checksum on the end) through SOQL with the query

SELECT CreatedById FROM FeedComment

You can then use this as a sub-query to get user information:

SELECT Name FROM User WHERE ID in (SELECT CreatedById FROM FeedComment)

In practice, I find that removing the last character of the CreatedById is necessary to get a working URL to the author's Chatter profile.

Update

There doesn't seem to be a link to travel up the relationship tree so I think you'll need to execute two queries and merge the results in code.

4
  • I am not sure if I understand how this would answer my question. I want all information with a single (non-aggregation) query. BTW: you could do your query in ONE statement "SELECT CreatedBy.Name FROM FeedComment". – Robert Sösemann Mar 18 '13 at 13:47
  • Ah, I misunderstood. The column FeedComment.FeedItemId points to the original post; I'm having trouble traversing the relationship in Workbench (if there is one). – Mike Chale Mar 18 '13 at 13:58
  • That is exactely the problem I am trying to solve with this question ;) – Robert Sösemann Mar 18 '13 at 14:02
  • @RobertS Have you tried using the Query Builder in Ajax Tools?? Its helped me create some really challenging and obscure queries at times. Would seem to be worth a try. – crmprogdev Mar 18 '13 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.