URLs query



  • Continuing the discussion from Poll: Is it annoying to have a topic where people only reply with questions?:

    @faoileag said:

    Wouldn't the necessary database query run for at least 5 minutes, this being Discoarse?

    Surprisingly, no.

    SELECT username, count(*) 
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('faoileag', 'PJH', 'boomzilla')
    GROUP BY username
    
    username	count
    PJH	1438
    boomzilla	601
    faoileag	528
    
    3 row(s)
    
    Total runtime: 37.369 ms
    
    


  • @PJH said:

    username count
    PJH 1438
    boomzilla 601
    faoileag 528

    3 row(s)

    Total runtime: 37.369 ms

    That's quite a lot of urls I have posted in the last months... 130 on average per month? Are you sure that number is correct?


  • SELECT post_id, tl.created_at, url
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('faoileag')
    ORDER BY tl.id DESC
    LIMIT 50
    
    "post_id"	"created_at"	"url"
    "33918"	"2014-09-18 20:54:35.701553"	"http://what.thedailywtf.com/t/can-we-please-nuke-the-fucking-bots/3213/108"
    "87190"	"2014-09-18 20:54:35.695869"	"http://what.thedailywtf.com/t/status-thread-post-your-current-status-here/1673"
    "84364"	"2014-09-17 14:51:51.798894"	"http://www.imdb.com/title/tt0080455/"
    "84280"	"2014-09-17 14:01:30.893944"	"http://thedailywtf.com/"
    "84259"	"2014-09-17 13:51:51.167794"	"http://www.sna.xxx"
    "84114"	"2014-09-17 11:22:03.036574"	"http://thedailywtf.com/Articles/Failure-to-Leap.aspx"
    "84020"	"2014-09-17 09:27:05.919112"	"http://www.emojione.com/"
    "84020"	"2014-09-17 09:27:05.910518"	"https://meta.discourse.org/t/emoji-love-thanks-discourse/20137"
    "78581"	"2014-09-17 08:32:26.910794"	"http://what.thedailywtf.com/t/the-official-likes-thread-dave-suspended-us-all-traitor-evad-taht-od-uoy-tel-tnac-i-yrros-mi/1000/19078"
    "83995"	"2014-09-17 08:32:26.903761"	"http://what.thedailywtf.com/t/poll-is-it-annoying-to-have-a-topic-where-people-only-reply-with-questions/3125"
    "83396"	"2014-09-16 18:47:20.366531"	"https://meta.discourse.org/t/solved-clean-slate-after-updating-to-discourse-1-0/19360"
    "82748"	"2014-09-16 12:45:32.175819"	"http://discourse.davidzych.com/"
    "78581"	"2014-09-16 11:42:43.718756"	"http://what.thedailywtf.com/t/the-forum-only-has-one-passable-poster/3241/18"
    "82643"	"2014-09-16 11:42:43.708103"	"http://what.thedailywtf.com/t/poll-is-it-annoying-to-have-a-topic-where-people-only-reply-with-questions/3125"
    "82510"	"2014-09-16 08:57:22.615484"	"http://www.amazon.co.uk/Hape-Glove-Puppet-Seagull-Multi/dp/B003CK0C9Y"
    "81410"	"2014-09-15 07:16:45.096751"	"http://what.thedailywtf.com/t/the-official-likes-thread-proudly-doing-it-wrong-since-june-2014-suggested-reply-method-a-class-mention-text-a/1000/18579"
    "81501"	"2014-09-15 07:16:45.082348"	"http://what.thedailywtf.com/t/a-million-billion-edits-of-the-order-of-the-editude-1/3218"
    "81293"	"2014-09-14 19:40:39.352005"	"http://www.existor.com/"
    "80972"	"2014-09-13 20:28:34.630532"	"http://www.discoursebugs.com"
    "80951"	"2014-09-13 19:59:38.138397"	"http://www.discoursebugs.com"
    "80501"	"2014-09-12 21:25:18.123884"	"https://meta.discourse.org/t/closing-a-topic-is-a-barrier-to-replying-to-a-reply/19999/2"
    "80451"	"2014-09-12 20:28:07.72325"	"https://meta.discourse.org/t/closing-a-topic-is-a-barrier-to-giving-a-reason-as-to-why-a-topic-has-been-created/20000"
    "80447"	"2014-09-12 20:18:22.279499"	"https://meta.discourse.org/t/closing-a-topic-is-a-barrier-to-replying-to-a-reply/19999"
    "80447"	"2014-09-12 20:18:22.274281"	"https://meta.discourse.org/t/add-link-to-commit-on-github-when-closing-a-fixed-bug-added-feature/19224/8"
    "66569"	"2014-09-12 18:55:34.278438"	"http://what.thedailywtf.com/t/the-new-official-unofficial-discourse-bug-tracker/3103/218"
    "80297"	"2014-09-12 18:55:34.26944"	"http://what.thedailywtf.com/t/discourse-1-0/2706/25"
    "80272"	"2014-09-12 18:36:28.455361"	"http://en.wikipedia.org/wiki/Turtles_all_the_way_down"
    "80122"	"2014-09-12 17:44:35.667614"	"http://what.thedailywtf.com/t/status-thread-post-your-current-status-here/1673/1818"
    "80206"	"2014-09-12 17:44:35.659719"	"http://what.thedailywtf.com/t/explicating-the-survival-of-the-shell-as-default-oss-ui/3098/190"
    "80162"	"2014-09-12 17:24:32.841894"	"https://meta.discourse.org/t/i-cant-login-https-twittercommunity-com/19935/16"
    "78581"	"2014-09-12 17:16:36.833948"	"http://what.thedailywtf.com/t/the-official-likes-thread-doing-it-wrong-since-june-2014/1000/18305"
    "80151"	"2014-09-12 17:16:36.826822"	"http://what.thedailywtf.com/t/poll-is-it-annoying-to-have-a-topic-where-people-only-reply-with-questions/3125"
    "79671"	"2014-09-12 09:54:24.259764"	"https://meta.discourse.org/t/add-link-to-commit-on-github-when-closing-a-fixed-bug-added-feature/19224/7"
    "79644"	"2014-09-12 08:17:46.276218"	"https://meta.discourse.org/t/awarded-badge-but-dont-see-it-on-the-badge-list/19931"
    "79634"	"2014-09-12 07:29:49.532534"	"http://www.discoursebugs.com/"
    "79630"	"2014-09-12 07:27:30.249349"	"http://www.discoursebugs.com/discoursebugs/issue/57/received-notification-for-event-that-didnt"
    "77876"	"2014-09-11 17:45:24.888335"	"http://what.thedailywtf.com/t/the-official-likes-thread-cleanish-slate-edition/1000/18152"
    "79080"	"2014-09-11 17:45:24.881212"	"http://what.thedailywtf.com/t/the-new-official-unofficial-discourse-bug-tracker/3103"
    "78073"	"2014-09-10 20:15:36.044091"	"https://meta.discourse.org/t/options-to-disable-hijack-of-cmd-f-ctrl-f-and-keys-for-search/16875/45"
    "77441"	"2014-09-10 13:05:07.08653"	"https://meta.discourse.org/t/click-on-reply-sometimes-without-effect/19860"
    "77359"	"2014-09-10 11:27:35.951613"	"https://meta.discourse.org/t/poll-heatmaps-in-the-topic-list-an-analysis/18877/8"
    "77359"	"2014-09-10 11:27:35.947246"	"https://meta.discourse.org/t/poll-heatmaps-in-the-topic-list-an-analysis/18877/7"
    "77245"	"2014-09-10 08:04:52.554132"	"https://meta.discourse.org/t/view-older-notifications-doesnt-show-you-the-same-stream/12271"
    "76832"	"2014-09-09 20:14:12.378508"	"https://meta.discourse.org/t/how-does-the-top-page-show-topics-with-lots-of-activity/18930/16/"
    "76832"	"2014-09-09 20:14:12.373318"	"https://meta.discourse.org/t/poll-heatmaps-in-the-topic-list-an-analysis/18877/6/"
    "76763"	"2014-09-09 19:27:26.288557"	"https://meta.discourse.org/t/poll-heatmaps-in-the-topic-list-an-analysis/18877/6"
    "76748"	"2014-09-09 19:17:28.900895"	"https://meta.discourse.org/t/view-older-notifications-doesnt-show-you-the-same-stream/12271"
    "76741"	"2014-09-09 19:12:06.985569"	"https://meta.discourse.org/t/how-does-the-top-page-show-topics-with-lots-of-activity/18930/12"
    "76158"	"2014-09-09 13:47:53.819227"	"http://what.thedailywtf.com/t/cipot-siht-ni-lru-eht-tuoba-woh/3057"
    "76224"	"2014-09-09 13:47:53.811738"	"http://what.thedailywtf.com/t/cipot-siht-ni-lru-eht-tuoba-woh/3052"
    


  • I think this one's probably more informative

    SELECT username, count(*) 
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('faoileag', 'PJH', 'boomzilla') AND
       internal=false
    GROUP BY username
    
    username	count
    PJH	632
    boomzilla	457
    faoileag	226
    
    3 row(s)
    
    Total runtime: 34.994 ms
    

  • :belt_onion:

    @faoileag said:

    That's quite a lot of urls I have posted in the last months... 130 on average per month? Are you sure that number is correct?

    you forget about the Filed Under: #tags?



  • @darkmatter said:

    you forget about the Filed Under: #tags?

    I thought about those, but please, including those would mean a new low even by Discurse standards.



  • @darkmatter said:

    you forget about the Filed Under: #tags?

    SELECT tl.url, tl.title 
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('faoileag') AND
       (url LIKE '%tag%' or tl.title LIKE '%tag%')
    
    "url"	"title"
    "http://what.thedailywtf.com/t/feature-request-cornify-tag/403/14"	"\N"
    "http://www.tagesspiegel.de/themen/s-bahn/"	"Verl?sslich Unzuverl?ssig: S-Bahn - Themen - Tagesspiegel"
    "http://what.thedailywtf.com/t/html-tag-abuse-thread/1269/113"	"\N"
    "http://what.thedailywtf.com/t/html-tag-abuse-thread/1269"	"\N"
    "http://what.thedailywtf.com/t/feature-request-cornify-tag/403/12"	"\N"
    "http://what.thedailywtf.com/t/html-tag-abuse-thread/1269/194"	"\N"
    "http://forums.thedailywtf.com/tags/klatyzarc/default.aspx"	"Browse by Tags - TDWTF Forums"
    "http://what.thedailywtf.com/t/html-tag-abuse-thread/1269/343"	"\N"
    "http://what.thedailywtf.com/t/html-tag-abuse-thread/1269"	"\N"
    

    They don't appear to be in that table.


  • Winner of the 2016 Presidential Election

    Hey, since we are at the topic and you seem to have the query already, how many times have I missed the #tag?

    Filed Under: Not this time, at least. | Bonusround: How many times did I actually miss it after using it for the first time since it was almost discoverable



  • This backup's a few days old, but

    SELECT count(*)
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('Kuro') AND
       (url LIKE '%tag%' or tl.title LIKE '%tag%')
    
    549
    
    SELECT count(*)
    FROM topic_links  tl
    JOIN users u on u.id = tl.user_id
    WHERE username in ('Kuro') AND
       (url ='#tag')
    
    548
    
    (Difference - one of them is '#tagf')
    

    Not quite sure how to get the ones that don't have #tag without including all the intra-site and external links...



  • If he's after all posts where he missed the tag, would this work?
    Substituting correct table & column names, obviously.

    select count(*) from posts p where p.id not in (     select post_id     from topic_links tl     join users u on u.id     where username in ('Kuro') and (url like '%tag%' or tl.title like '%tag%') ) and username in ('Kuro')

    Filed under: Still haven't figured out indenting in code blocks without using nbsp.



  • select count(*)
    from posts p
    join users u on u.id = p.user_id
    where p.id not in (
        select post_id
        from topic_links tl
        join users u on u.id = tl.user_id
        where username in ('Kuro') and (url like '%tag%' or tl.title like '%tag%')
    ) and username in ('Kuro')
    
    49
    
    "id"	"topic_id"	"post_number"
    "9510"	"827"	"17"
    "9516"	"827"	"19"
    "9728"	"848"	"1"
    "9736"	"838"	"11"
    "10042"	"848"	"3"
    "10045"	"848"	"4"
    "10064"	"834"	"44"
    "10065"	"869"	"1"
    "10412"	"761"	"140"
    "10415"	"879"	"2"
    "10438"	"879"	"4"
    "10452"	"881"	"2"
    "10571"	"887"	"5"
    "10576"	"889"	"2"
    "10577"	"887"	"7"
    "10579"	"887"	"9"
    "10584"	"889"	"5"
    "10588"	"890"	"3"
    "10594"	"889"	"8"
    "10614"	"889"	"15"
    "10637"	"894"	"2"
    "10640"	"893"	"3"
    "10641"	"893"	"4"
    "10642"	"895"	"1"
    "10699"	"893"	"20"
    "10703"	"896"	"6"
    "10706"	"889"	"30"
    "10715"	"893"	"26"
    "34193"	"1677"	"1"
    "34221"	"1677"	"3"
    "38043"	"1811"	"1"
    "38071"	"1812"	"1"
    "38078"	"1812"	"3"
    "38087"	"1812"	"5"
    "38130"	"1812"	"7"
    "42596"	"1873"	"4"
    "47718"	"1981"	"3"
    "48580"	"1673"	"666"
    "56411"	"2214"	"34"
    "69598"	"2836"	"11"
    "69626"	"2825"	"28"
    "70267"	"2843"	"62"
    "70710"	"2857"	"15"
    "71556"	"2887"	"3"
    "72017"	"2915"	"1"
    "72816"	"2914"	"140"
    "76626"	"3070"	"1"
    "81121"	"3207"	"1"
    "81190"	"3200"	"28"
    

  • mod

    Why do I get the feeling that this includes quotes?



  • Check for yourself if you're that interested - /p/<post_id> (first column.)

    I got bored by 81501, Up to there it was either genuine links, or stuff in the right gutter.



  • @abarker said:

    Why do I get the feeling that this includes quotes?

    Let's see...

    http://davidthompson.typepad.com/davidthompson/2014/09/our-intellectual-vanguard.html



  • My OnePost points to "No," links in quotes don't count.



  • @loopback0 said:

    Filed under: Still haven't figured out indenting in code blocks without using nbsp.

    select count(*)
    from posts p
    where p.id not in (
    	select post_id 
    	from topic_links tl
    	join users u on u.id
    	where username in ('Kuro') and (url like '%tag%' or tl.title like '%tag%')
    ) and username in ('Kuro')
    

  • :belt_onion:

    in other words, copy and paste these from your IDE:
    [code]
    s
    ddfadsff
    s
    [/code]

    Nice. Those 2 "s" should be indented the same amount....
    Tabs on the first line of a code block fail. Summoning @matches

    link to raw: http://what.thedailywtf.com/raw/3364/17



  • @darkmatter said:

    in other words, copy and paste these from your IDE:

    	s
    ddfadsff
    	s
    

  • :belt_onion:

    [code]

    	s
    ddfadsff
    	s
    

    [/code]

    so more bugged in that it works when you use backticks, because code blocks and backtick code blocks are processed differently?



  • [code] isn't defined in the markdown spec.


  • :belt_onion:

    one would still hope for the dicsourse spec to match in cases where they allow 2 different methods of starting the same type of output.



  • s
    ddfadsff
    s

    THREE.


  • :belt_onion:

    so spaces and backticks good, [code] bad.

    [code]probably explains this too[/code]
    ````probably explains this too````


  • anomaly:

    four backticks
    

    not anomaly:

    three backticks
    



  • s
    ddfadsff
    s

    Four?

    Though you could argue this isn't code, just indented text. And you'd be right.



  • 	s
    ddfadsff
    s

    Does this one count? I think it does. No highlighting, but semantically it's code. >_>

    <code><pre>



  • The inline element <code> can't contain the block element <pre>, so you probably meant <pre><code>.



  • [code]
    s
    ddfadsff
    s
    [/code]


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.