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.

2 Answers 2

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. Mar 18, 2013 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". Mar 18, 2013 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, 2013 at 13:58
  • That is exactely the problem I am trying to solve with this question ;) Mar 18, 2013 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, 2013 at 16:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .