2^n queries for those interested
-
Continuing the discussion from Poll: Is it annoying to have a topic where people only reply with questions?:
because i don't know what the queries are?
2^7:
SELECT user_id, 0 post_id, current_timestamp granted_at FROM badge_posts WHERE topic_id NOT IN ( SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4 ) AND topic_id NOT IN ( 1000, 1673, 3125 ) AND ( :backfill OR user_id IN ( SELECT trigger_post.user_id FROM posts trigger_post WHERE trigger_post.id IN (:post_ids) ) ) GROUP BY user_id HAVING count(*) >= 128
Others differ only by the
128
in that query.
-
What does the
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4 )```` bit do?
-
so the topics to exclude are manually specified.
good to know, also not a good idea to try and game the system using that knowledge.
Thanks for the info @pjh
-
removes all topics with less than 4 posts from consideration....
i think.
my SQL is a little rusty
-
1000, 1673, 3125
Wow...I have a lot of posts in those.
What does the topic_id NOT IN ( SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4)bit do?
I believe that's how he excludes categories that are not public. I suppose it's relying on some discourse implementation detail.
-
oh,
badge_posts
is just posts. I was thinking it was some special table purely to do with posts about badges or something.Sounds WTFey, but this is Discourse
-
I was thinking it was some special table purely to do with posts about badges or something.
I suspect it's a view.
-
Unless i'm wildly wrong about what a topic id is, it's excluding categories with less than 4 topics in them.
-
Yep:
execute "CREATE VIEW badge_posts AS 6 SELECT p.* 7 FROM posts p 8 JOIN topics t ON t.id = p.topic_id … 13 NOT c.read_restricted AND 14 t.visible"
-
Unless i'm wildly wrong about what a topic id is, it's excluding categories with less than 4 topics in them.
That's not why you're wrong. ;-)
Ow...the wink looks more like a black eye.
-
So it's excluding invisible topics in general.
Discosauce.
-
Yeah. I remember @PJH talking about that in the beginning. And you may recall a lot of people leveling up when the bug category's restrictions were removed.
-
-
topic_id NOT IN ( SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4)
Just to confirm what's already been said, it ignores topics with less than 4 posts in them.
I believe that's how he excludes categories that are not public.
That's done by using the
badge_posts
table which only contains public posts.I suspect it's a view.
Already discussed on here:
-
-
no... i'm pretty sure that's not what that line does given the setup of the badge_posts view (linked somewhere in this topic)
-
FTFY
No. You broke it.
Any topic with fewer than 4 posts in them aren't counted. I was thinking of the majority of stuff in One Post when I included that clause.
-
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE topic_id NOT IN (
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (topic_id) <4
) AND topic_id NOT IN (
1000, 1673, 3125
) AND (
:backfill OR
user_id IN (
SELECT trigger_post.user_id FROM posts trigger_post WHERE trigger_post.id IN (:post_ids)
)
) GROUP BY user_id HAVING count(*) >= 128Should be count(post_id)
-
Ah.Edit - Strike that. The query is correct:
[postgres@sofa ~]$ psql -c ' SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (topic_id) <4 LIMIT 25 ' topic_id ---------- 2 31 37 235 236 339 391 433 441 445 525 548 560 562 601 605 614 624 628 649 651 697 704 749 774 (25 rows) [postgres@sofa ~]$
-
This post is deleted!
-
What's the alternate return? Because something smells.
-
"Alternate return"?
-
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (post_id) <4
-
Also, for the love of god, fix the 503's every time i want to use the script tags or sql queries!
-
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (post_id) <4
SQL error: ERROR: column "post_id" does not exist LINE 1: ... FROM badge_posts GROUP BY topic_id HAVING count (post_id) <...
-
Also, for the love of god, fix the 503's every time i want to use the script tags or sql queries!
I've not had a 503 for ages - certainly not for my SQL stuff....
-
Replace 'post_id' with the appropriate field name!
Above it's referenced as :post_ids
-
Replace 'post_id' with the appropriate field name!
It is in the original query - topic_id.
Above it's referenced as :post_ids
That's a variable that DC pokes in when it runs the queries.
-
100% repro for me on new posts
I can get around it by [quote]text[quote] placeholder text, then ninja editing in the text.
-
100% repro for me on new posts
Is it that unescaped <?
Edit - probably not - that went through..
-
It's exactly what the raw of my post is above.
-
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (post_id) <4
-
No repro...
-
No edits allowed
-
No edits made. Just copied your raw, pasted, posted and it was there.
-
-
Anyway - the sort of data behind that query:
=# SELECT * FROM badge_posts LIMIT 2; -[ RECORD 1 ]-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------- id | 19550 user_id | 643 topic_id | 1258 post_number | 1 raw | Continuing the discussion from [Anagram Est Omen](http://what.thedailywtf.com/t/anagram-est-omen/1237/67): | | @RTapeLoadingError <a href="/t/via-quote/1237/67">said</a>:<blockquote>| Well, we had [this][1] which would stretch the definition of porno somewhat. | [1]: http://www.gamefaqs.com/sinclair/959850-samantha-fox-strip-poker/images |</blockquote> | Just manipulated the "followed" counter next to the link in that post up to 120 by simply right-clicking the link without doing anything else. | | Edit: ok, not *really* a bug - the click count is not transmitted to the backend. If you leave the topic and come back, count is normal again. cooked | <p>Continuing the discussion from <a href="//what.thedailywtf.com/t/anagram-est-omen/1237/67">Anagram Est Omen</a>:</p> | | <aside class="quote" data-post="67" data-topic="1237"><div class="title"> | <div class="quote-controls"></div> | <img width="20" height="20" src="/user_avatar/what.thedailywtf.com/rtapeloadingerror/40/158.png" class="avatar">RTapeLoadingError said:</div> | <blockquote><p>Well, we had <a href="http://www.gamefaqs.com/sinclair/959850-samantha-fox-strip-poker/images" rel="nofollow">this</a> which would stretch the definiti on of porno somewhat.<br> </p></blockquote></aside> | | <p>Just manipulated the "followed" counter next to the link in that post up to 120 by simply right-clicking the link without doing anything else.</p> | | <p>Edit: ok, not <em>really</em> a bug - the click count is not transmitted to the backend. If you leave the topic and come back, count is normal again.</p> created_at | 2014-06-27 10:08:43.502912 updated_at | 2014-06-27 10:11:09.593289 reply_to_post_number | reply_count | 0 quote_count | 0 deleted_at | off_topic_count | 0 like_count | 0 incoming_link_count | 0 bookmark_count | 0 avg_time | 25 score | 8.85 reads | 41 post_type | 1 vote_count | 0 sort_order | 1 last_editor_id | 643 hidden | f hidden_reason_id | notify_moderators_count | 0 spam_count | 0 illegal_count | 0 inappropriate_count | 0 last_version_at | 2014-06-27 10:11:09.560899 user_deleted | f reply_to_user_id | percent_rank | 0.25 notify_user_count | 0 like_score | 0 deleted_by_id | edit_reason | word_count | 101 version | 2 cook_method | 1 wiki | f baked_at | 2014-07-16 08:47:58.757142 baked_version | 1 hidden_at | self_edits | 1 reply_quoted | f -[ RECORD 2 ]-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------- id | 19551 user_id | 20 topic_id | 1258 post_number | 2 raw | And if you refresh the page you'll find out it's only incrementing locally, and doesn't update the count on the server. cooked | <p>And if you refresh the page you'll find out it's only incrementing locally, and doesn't update the count on the server.</p> created_at | 2014-06-27 10:10:59.083089 updated_at | 2014-06-27 10:10:59.083089 reply_to_post_number | reply_count | 1 quote_count | 0 deleted_at | off_topic_count | 0 like_count | 1 incoming_link_count | 0 bookmark_count | 0 avg_time | 28 score | 29 reads | 41 post_type | 1 vote_count | 0 sort_order | 2 last_editor_id | 20 hidden | f hidden_reason_id | notify_moderators_count | 0 spam_count | 0 illegal_count | 0 inappropriate_count | 0 last_version_at | 2014-06-27 10:10:59.0883 user_deleted | f reply_to_user_id | percent_rank | 0 notify_user_count | 0 like_score | 1 deleted_by_id | edit_reason | word_count | 24 version | 1 cook_method | 1 wiki | f baked_at | 2014-07-16 08:47:58.822805 baked_version | 1 hidden_at | self_edits | 0 reply_quoted | f
-
You're thinking of count(distinct topic_id).
count() is weird. If I remember correctly, count(topic_id) actually means:
SUM(case topic_id when null then 0 else 1 end)
-
No, I'm not, and no it doesn't.
Postgres may be different than sql server, but I don't think that query is doing what you think it's doing.
I don't really care though, so you can leave it broken if you want.
-
Mine is simply counting the number of rows a particular topic appears in. Each row represents a post within a topic. If there's 3 rows or less, I ignore that topic.
-
No, I'm not, and no it doesn't.
Postgres may be different than sql server, but I don't think that query is doing what you think it's doing.
deleting this post so i can explain better in another
-
No, I'm not, and no it doesn't.
Postgres may be different than sql server, but I don't think that query is doing what you think it's doing.
I don't really care though, so you can leave it broken if you want.
[code]SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count (topic_id) <4[/code]
is equivalent to
[code]SELECT topic_id
FROM (
SELECT topic_id, count(topic_id) as posts FROM badge_posts GROUP BY topic_id
) topicposts
where posts < 4
[/code]and unless you have a view that is outer-joining the topics to posts in some kind of uber-retarded manner that results in null topic_ids for posts, the above query results in the exact same thing as what @matches basically wants
[code]SELECT topic_id
FROM (
SELECT topic_id, count(post_number) as posts FROM badge_posts GROUP BY topic_id
) topicposts
where posts < 4
[/code]you can go ahead and test it on your own tables if you want, but the query does exactly what @PJH wants.
-
also, that newline-after-a-colon-equals-fcked-emoji bug may be the most #$*&@ annoying text-entry bug dicsourse has had yet.
-
I'm gonna miss it when it's gone, though.
-
Test:
nope, no bug.