URLs query
-
Continuing the discussion from Poll: Is it annoying to have a topic where people only reply with questions?:
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
-
username count
PJH 1438
boomzilla 601
faoileag 5283 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
-
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?
-
you forget about the Filed Under: #tags?
I thought about those, but please, including those would mean a new low even by Discurse standards.
-
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.
-
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"
-
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.
-
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.
-
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')
-
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 @matcheslink to raw: http://what.thedailywtf.com/raw/3364/17
-
-
[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.
-
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
sTHREE.
-
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]