2^n queries for those interested



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

    @accalia said:

    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.


  • kills Dumbledore

    What does the

       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
    )````
    bit do?

  • SockDev

    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


  • SockDev

    removes all topics with less than 4 posts from consideration....

    i think.

    my SQL is a little rusty



  • @PJH said:

    1000, 1673, 3125

    Wow...I have a lot of posts in those.

    @jaloopa said:

    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.


  • kills Dumbledore

    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



  • @jaloopa said:

    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"
    


  • @Matches said:

    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.



  • @boomzilla said:

    leveling up when the bug category's restrictions were removed.

    Ponder



  • @jaloopa said:

    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.

    @boomzilla said:

    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.

    @boomzilla said:

    I suspect it's a view.

    Already discussed on here:

    http://what.thedailywtf.com/t/can-we-reveal-the-bug-category-to-all-since-its-post-v1-now/2871/12?u=pjh



  • @PJH said:

    less than 4 TOPICS in them

    FTFY

    BTW, that query should always return null.


  • SockDev

    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)



  • @Matches said:

    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.



  • @PJH said:

    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

    Should 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 ~]$ 
    

  • SockDev

    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!



  • @Matches said:

    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) <...
    


  • @Matches said:

    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



  • @Matches said:

    Replace 'post_id' with the appropriate field name!

    It is in the original query - topic_id.

    @Matches said:

    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.



  • @Matches said:

    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.


  • :belt_onion:

    @Matches said:

    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


  • :belt_onion:

    @Matches said:

    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:smile:
    [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:smile:
    [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.


  • :belt_onion:

    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.:smile:



  • Test:

    nope, no bug.


Log in to reply
 

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