2

This is basically a follow up to this popular thread, Can I search my comments using some keyword(s)?, with a similar reasoning behind it: I am sure I have seen an question already to a given problem and I vaguely remember the title, but somehow it won't pop up on google or in searching Stackoverflow.

I would want to narrow it down to questions that I have interacted with, usually an upvote.

small update user rene has kindly pointed out that votes are private. So with interaction I would mean, I could have answered or commented on it, or bookmarked it.

Unfortunately I am a complete SQL beginner and I also don't know what the fields are called that should potentially be queried... This thread seems important, but I don't know how to implement the post-type into the query (Database schema documentation for the public data dump and SEDE).

The query from the mentioned thread is as follows: (it looks in all the user's comments).

SELECT Id AS [Comment Link], Score, Text 
  FROM Comments 
 WHERE UPPER(Text) LIKE UPPER('%##CommentText##%')
   AND UserId = '##UserId##'
 ORDER BY Score DESC
3
  • your votes (nor those of anyone else) are not stored in SEDE with your userid as voting is anonymous.
    – rene
    Commented Jun 23, 2020 at 9:20
  • @rene fair enough, good point.
    – tjebo
    Commented Jun 23, 2020 at 9:20
  • I have updated the question to make it more general about any interaction of mine, i.e. comments, bookmarks, answers
    – tjebo
    Commented Jun 23, 2020 at 9:23

1 Answer 1

5

You can't know from SEDE what you have upvoted as Voting is anonymous. For that reason the UserId field in the Votes table is null for most VoteTypeIds.

The following Union query might be the closest to achieve what you want. It checks the few tables that does carry a userid and the postid the record is for. I've used PostHistory, Comments, suggestededits and Votes (for favorites(bookmarks)/bounties).

select postid [Post Link]
     , creationdate
     , 'H' [type]
from posthistory 
where userid = ##userid##
union
select postid
     , creationdate
     , 'C'
from comments
where userid = ##userid##
union
select postid
     , creationdate
     , 'E'
from suggestededits
where owneruserid = ##userid##
union
select postid
     , creationdate
     , 'V'
from votes -- but not the up and down votes, voting is anonymous
where userid = ##userid##
order by creationdate desc

Feel free to fork it and add the text search criteria for each table where applicable

When run today this is what your result looks like:

sede query results showing postlink, creationdate and Type (H, C, V)

Keep in mind SEDE is updated once a week on Sunday.
Never forget that Monica Cellio created the awesome SEDE Tutorial.
Say "Hi" in SEDE chat.

0

You must log in to answer this question.

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