-2

I am trying to find one piece of comments by someone else on my post("that I continue to learn and grow", does this expression sound natural?), following another meta post.

with userid = 109190, Keyword = encounter group,

enter image description here

I got "0 rows returned", and the "SQL" did change as well.

SELECT CONCAT('http://stackoverflow.com/questions/', PostID), Score, Text, CreationDate FROM comments WHERE UserId = ##UserID## AND Text Like '%##Keyword##%' COLLATE Latin1_General_100_CI_AI_SC ORDER BY CreationDate DESC

Actually, the comment does exist on the post I linked at the beginning.

What am I missing?

by SQL

I also tried another way just now, directly by SQL query. still get none.

enter image description here

enter image description here

SELECT CONCAT('https://ell.stackexchange.com/', '239160', '|', posts.title)
     , c.Score
     , Text
     , c.CreationDate 
FROM comments c
inner join posts on posts.id = c.PostId
WHERE posts.OwnerUserId = 239160
AND Text Like '%encounter group%'  COLLATE Latin1_General_100_CI_AI_SC 
ORDER BY CreationDate DESC
12
  • What is the query you're running?
    – scohe001
    Mar 1 '20 at 2:46
  • @scohe001 If "query" refers to a "SQL query", I didn't run that directly. I put 3 keywords in the text boxes, shown in my OP and then I clicked the "Run" button in that page.
    – WXJ96163
    Mar 1 '20 at 2:56
  • Yes, but there should probably be some SQL above "Enter Parameters," no? Can you include a screenshot of the full SEDE page?
    – scohe001
    Mar 1 '20 at 3:00
  • @scohe001 Thanks for your reminder. I've updated my OP.
    – WXJ96163
    Mar 1 '20 at 3:21
  • Can you either give us a link to the query or copy and past the line beginning with SELECT CONCAT( into your question? That's the SQL code behind the query (and I'll bet you anything your "Keyword" isn't exactly what it's looking for).
    – scohe001
    Mar 1 '20 at 3:37
  • 5
    Doesn’t look like you actually switched to ELL? It usually replaces the MSO logo with the site logo if you did it correctly? Also, your question you linked to is only 20 hours old and SEDE only runs once a week on Sunday... so if it's within the last week, it's not on SEDE yet.
    – Catija StaffMod
    Mar 1 '20 at 3:44
  • @Catija I switched the site and ran the query again, new screenshot updated. I guess "within the last week" might be the reason.
    – WXJ96163
    Mar 1 '20 at 3:55
  • @scohe001 Yes, you're right. The SQL didn't change a bit at all.
    – WXJ96163
    Mar 1 '20 at 3:56
  • Catija is correct that it usually runs on Sunday, I don't know if it's different because it's a Leap year but the main page says: "Data updated 1 min ago"; so you might try again.
    – Rob
    Mar 1 '20 at 4:05
  • @Rob The update's likely in process. Every time I refresh the page, it changes... and it's not going up. :P
    – Catija StaffMod
    Mar 1 '20 at 4:08
  • 1
    @Catija, I noticed that too, and was investigating if perhaps the text only applied to that one page we were looking at, sometimes it said a few seconds, but I suspect your answer (comment above) is a better explanation. In any event it probably should be finished in less than 20 hours, which is before Sunday (UTC). WXJ96163, maybe check the main page in a few hours and see if it says any time longer than a few minutes.
    – Rob
    Mar 1 '20 at 4:15
  • It now says: "Data updated 3 hours ago", and Brent Spiner (aka rene) has provided a complete explanation. 👏
    – Rob
    Mar 1 '20 at 11:02
7

The Stack Exchange Data Explorer is normally updated once a week, on Sunday, starting at 03:00 UTC. The process itself takes up to 6 hours (due to the Stack Overflow database) but SEDE is never taken off-line in that process, so during the refresh you'll end-up with databases that are up-to-date and still waiting to be processed. Look at the sys.databases table if you want to check if your data is still stale.

So, if you're querying for posts, comments, reviews or votes, to name a few, you have to take into account that you need to be looking for data that is at least a week old. Use the Stack API if you need "live" data.

This last week was a bit special as SE faced an major outage in their Colorado datacenter, making SEDE unreachable from the outside (I'm sure it was happily running and waiting for connections that never came). Luckily Taryn made a new instance available in the NY datacentre and that instance was refreshed with data from the Friday backup. So in this small period we had data that was at most 2 days old, instead of the common 7 days.

With that out of the way, let's look at the query.

SELECT CONCAT('site://questions/', PostID)
     , Score
     , Text
     , CreationDate 
FROM comments 
WHERE UserId = ##UserID## 
AND Text Like '%##Keyword##%'  COLLATE Latin1_General_100_CI_AI_SC 
ORDER BY CreationDate DESC

Based on your screenshot, you're using the wrong UserId. You have never said anything close to "encounter group" but a user with Userid 56820 did. When I adapt for that, I do get results today.

If you meant to search for phrases in comments on your posts, you need an extra Join with the Posts table, like I've done in this query

SELECT CONCAT('site://questions/', PostID, '|', posts.title)
     , c.Score
     , Text
     , c.CreationDate 
FROM comments c
inner join posts on posts.id = c.PostId
WHERE posts.OwnerUserId = ##UserID## 
AND Text Like '%##Keyword##%'  COLLATE Latin1_General_100_CI_AI_SC 
ORDER BY CreationDate DESC

With the above query you do get results, given your input parameters.

2
  • Thank you. I tried with SQL just now, still got none. would you please take a look at my update?
    – WXJ96163
    Mar 1 '20 at 11:24
  • 2
    @WXJ96163 You are still mixing up Ids. The Id of the Post is 239160, That post is owned by you, so Post.OwnerUserId = 109190 because that is the id found in your userprofile: ell.stackexchange.com/users/109190. The comment is made by a user with id 56820 so in the comments table the UserId will have 56820 and the PostId will have 239160.
    – rene
    Mar 1 '20 at 11:58

You must log in to answer this question.

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