Badges SQL problem


(PJH) #1

As requested by @Sam, and as pointed out by @codinghorror:

Continuing the discussion from Posts sometimes randomly not displaying:

Finally figured out amongst all that error text that I presumed that post_id wasn’t required in the results, when it is.

Can I presume that changing

SELECT user_id, current_timestamp granted_at FROM badge_posts where topic_id not in (

to

SELECT user_id, 0 post_id, current_timestamp granted_at FROM badge_posts where topic_id not in (

As the first line of my SQL will fix this problem?


What cool badge queries have you come up with?
(Régis Hanol) #2

I would suggest using NULL post_id instead of 0 post_id.


(PJH) #3

Ok - done :smile:

But have I identified what the problem is? /logs seems to be a bit quieter now…


(PJH) #4

Ah - spoke too soon - it doesn’t seem to like NULL:

Job exception: PG::Error: ERROR:  failed to find conversion function from unknown to integer
: INSERT INTO user_badges(badge_id, user_id, granted_at, granted_by_id, post_id)
            SELECT 139, q.user_id, q.granted_at, -1, NULL
            FROM ( SELECT user_id, null 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

) GROUP BY user_id HAVING count(*) >= 512 ) q
            LEFT JOIN user_badges ub ON
              ub.badge_id = 139 AND ub.user_id = q.user_id
              
            WHERE (ub.badge_id IS NULL AND q.user_id <> -1) AND (q.post_id in (66007,65931))
            RETURNING id, user_id, granted_at

(Sam Saffron) #5

Can you have a careful read of:

And see if you can adjust.

Note you can not control when daily badges run as of yet, we would need to build something to allow for that.


(PJH) #6

So what do you suggest doing for badges that are time sensitive then (like the attendance badges I’ve mentioned previously) without making the query overly complicated? (Or using one of the more frequent options available.)

Would it be easier to add options for

  • daily
  • twice daily
  • thrice daily

for example?


(Kane York) #7

Couldn’t you do something like “you need a 14-day streak of visit records in the last 15 days”? That way it’ll catch all the possible offsets.


(Sam Saffron) #8

I am not really against adding a twice daily thing if we want. Controlling the time it runs though is a royal pain of a change.