Suche nach guten Abzeichen im Zusammenhang mit Voting

Gemeinschaft Daten & Berichterstattung
, ,
1

Ich habe ein paar Ideen für Abzeichen, bin mir aber unsicher, wie ich die Abfragen schreiben soll (ich kenne die Tabellenstruktur nicht gut genug). Die Zahlen dienen nur als Basis. Ich werde sie für zusätzliche Abzeichen erweitern.

  1. Jemand hat 10 Stimmen für ein einzelnes Thema erhalten, das er geschrieben hat.
  2. Jemand hat insgesamt 20 Stimmen für alle von ihm geschriebenen Themen erhalten.
  3. Jemand hat 20 Mal abgestimmt.

Hat jemand bereits solche Abzeichen und möchte sie teilen? Wenn nicht, möchte jemand versuchen, die Abfragen zu schreiben?

2

The second query in this topic might do the trick: Badge ideas for Topic Voting. The query grants the badge to users who have cast a single vote, but there is a suggestion at the bottom of the query for how to require more votes to grant the badge.

3

Thanks @simon. I actually read over that post already but the query didn’t return the results I was expecting. It was the Limit 1 at the end messing me up. After removing that it works perfect for the “Someone voted x times”.

Any idea how to see how many votes a person got on a topic they wrote? Or how many likes they got total?

4

Vielleicht findest du einige Data-Explorer-Abfragen, die du anpassen kannst, um Abfragen für Abzeichen zu erstellen. Schaue dir die Data-Explorer-Abfragen für Themenabstimmungen sowie andere Themen mit den Tags sql-query und sql-triggered-badge an.

5

Aren’t these rather similar to badges that already exist in Discourse?

6

That got me quite a ways towards my target:

select    
    t.user_id,
    ucf.value,
    count(*)
from
    topics t
    join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes'
group by ucf.value, t.user_id
order by t.user_id, count(*) desc

That returns:

image
image.png901×172 10.3 KB

The two things I’m trying to get are total votes someone got and the topic that has the most votes for each person. You can see mmontecalvo has 2 topics showing. I’m fairly certain I need to use row_number() to get just the highest post but I don’t know how.

@codinghorror These may be similar to a default badge but none of the defaults are built off topics getting votes.

7

I think I got it! This will give the topic with the most votes per person that has at least 5 votes on it:

WITH query as (
select    
    t.user_id,
    ucf.value,
    count(*) as num,
    t.created_at,
    row_number() OVER (PARTITION BY t.user_id order by count(*) desc) as rank
from
    topics t
    join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes'
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at 
from query where rank = 1

I ended up going with this instead so we could give a badge for each topic someone puts in that gets at least X votes in the correct category:

WITH query as (
select    
    t.user_id,
    ucf.value,
    count(*) as num,
    t.created_at
from
    topics t
    join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes' and category_id = (
  SELECT id FROM categories WHERE name ilike 'UI Experience Requests'
)
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at 
from query