2

How can I modify this Data Explorer query for the case when user of the comment is same as the poster, then the query should look for the tagged user in the comment and print the tagged user reputation along with the commenter or poster's reputation.

For example, in this post for comment ID = 1262076, The query should print:

 Commenter's reputation | Adressee's reputation
 4828                   | 147757

Further I would also like to know if there is a way I could run the query for multiple values of comment IDs at once? or do I need to run the query separately for each comment ID?

Improve this question
7
  • A Gentle bounce !! Appreciate thoughts on this .
    – user2256866
    Apr 7, 2014 at 20:18
  • 2
    1 Difficult; there's no NotifiedUserId in the schema, so this would involve reproducing the parsing rules - which would be a nightmare in terms of scanning all previous comments, and still wouldn't work if the notified user changed usernames afterwards. 2 That would involve splitting a string in SQL; not the easiest thing in the world.
    – michaelb958--GoFundMonica
    Apr 8, 2014 at 2:18
  • @michaelb958 : assume that the multiple values of the comment IDs is given as the column of a table not as a string. Example = {5159911 3160978 5020258 8364708 6788788 6569959 5789421 3590334 3189483 1262076 838153 6231547 1306349 8274895 4609973 3772693 6289345 }
    – user2256866
    Apr 8, 2014 at 2:40
  • ...a table column would be crammed into a parameter input box how?
    – michaelb958--GoFundMonica
    Apr 8, 2014 at 2:41
  • @michaelb958: Thanks for the directions, I will try doing it.
    – user2256866
    Apr 8, 2014 at 2:45
  • Why are you trying to do this exactly? There may be an easier way.
    – jmac
    Apr 8, 2014 at 4:04
  • @jmac could you help me with just the part two of the question. I believe the part one of the question is pretty ambiguous in cases where the user name might have changed. Please suggest the way I could iterate this query for different values of comment ID's (commennt ID table = 234567 678678 898978 677856 453434 564738 978664 453624 122312 342312 452312)
    – user2256866
    Apr 8, 2014 at 12:38

1 Answer 1

Reset to default
4

It requires some trickery to do so and it only succeeds if the displayname of the user is unchanged between the time the comment was posted and now. If more names match (Multiple users with the dispayname Tim commented) will also lead to multiple rows here. I did read How do comment @replies work to finalize the logic.

The logic to get the user from a comment text is:

  • find the first @
  • find the the next space following the @
  • take the characters between the @ and the space
  • remove any spaces from the userdisplayname
  • match the taken characters against the cleaned userdisplayname

The query looks like this:

select distinct c.id as [Comment Link]
, c.postid as [Post Link]
, u.id as [User Link]
, u.reputation as [Commenter Rep]
, owner.id as [User Link]
, owner.reputation as [Post Owner Rep]
, cu.id as [User Link]
, cu.reputation as [Reply-er Rep]
from comments c 
inner join users u on u.id = c.userid
inner join posts p on p.id = c.postid
inner join users owner on owner.id = p.owneruserid
inner join comments creply on creply.postid = p.id and creply.id <> c.id
inner join users cu on cu.id = creply.userid
where p.id = ##PostId:int##
and charindex('@', c.[text]) > 0
and replace(cu.displayname,' ', '' ) like  substring(c.[text], 
    charindex('@', c.[text])+1, 
    charindex(' ', c.[text], charindex('@', c.[text])) - (charindex('@', c.[text])+1)) +'%'

and produces this result per June, 19th 2014

commenter owner and replier rep

Improve this answer

You must log in to answer this question.

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