If you're running an instance and have direct access to the database, you can run some of these SQL queries on your database:
Local users with most comments
select p.name,
p.display_name,
(select count(id)
from comment c
where c.creator_id = p.id) as comments_count
from person p
where local = true
order by comments_count desc
;
Local users with most posts
select p.name,
p.display_name,
(select count(id)
from post p2
where p2.creator_id = p.id) as posts_count
from person p
where local = true
order by posts_count desc
;
People who disliked a specific comment
If your SQL client doesn't support parametric queries, you have to replace the question mark with the comment ID manually.
select p.actor_id
from person p
inner join comment_like cl on cl.person_id = p.id
where cl.comment_id = ?
and cl.score = -1;
People who disliked specific post
If your SQL client doesn't support parametric queries, you have to replace the question mark with the post ID manually.
select p.actor_id
from person p
inner join post_like pl on p.id = pl.person_id
where pl.post_id = ?
and pl.score = -1;
Most disliked posts of a user
If your SQL client doesn't support parametric queries, you have to replace the question mark with the username in single quotes (for example 'rikudou' for mine). Note that this query fails if there are multiple users with same username but on different instances, in that case you should replace (select id from person where name = ?) with (select id from person where actor_id = ?) and instead of username for the question mark you need to use the link to their profile (for example 'https://lemmings.world/u/rikudou' for mine).
select p.ap_id, p.id, count(pl.id) as dislikes
from post p
inner join post_like pl on pl.post_id = p.id
where pl.score = -1
and p.creator_id = (select id from person where name = ?)
group by p.ap_id, p.id
order by dislikes desc
;
Most disliked comments of a user
Read the instructions for Most disliked posts of a user above.
select c.ap_id, c.id, count(cl.id) as dislikes
from comment c
inner join comment_like cl on cl.comment_id = c.id
where cl.score = -1
and c.creator_id = (select id from person where name = ?)
group by c.ap_id, c.id
order by dislikes desc
;
Blocked communities by user
Read instructions for Most disliked posts of a user.
select c.actor_id
from community c
inner join community_block cb on c.id = cb.community_id
where cb.person_id = (select id from person where name = ?)
;
Blocked users by user
Read instructions for Most disliked posts of a user.
select p.actor_id
from person p
inner join person_block pb on p.id = pb.target_id
where pb.person_id = (select id from person where name = ?)
;
Which comments by a specific user were disliked by another specific user
If your SQL client doesn't support parametric queries, you have to replace the :yourUsername with the username in single quotes (for example 'rikudou' for mine), same for :dislikerUsername. For additional instructions read instructions for Most disliked posts of a user.
select c.ap_id, c.id
from comment c
inner join comment_like cl on cl.comment_id = c.id
inner join person p on p.id = cl.person_id
where cl.score = -1
and c.creator_id = (select id from person where name = :yourUsername)
and p.name = :dislikerUsername;
Only local votes for a comment
If your SQL client doesn't support parametric queries, you have to replace the question mark with the comment ID manually.
select c.ap_id,
c.id,
count(case cl.score when -1 then 1 end) as dislikes,
count(case cl.score when 1 then 1 end) as likes,
sum(cl.score) as score
from comment_like cl
inner join person p on cl.person_id = p.id
inner join comment c on cl.comment_id = c.id
where p.local = true
and cl.comment_id = ?
group by c.ap_id, c.id
;
Only local votes for a post
If your SQL client doesn't support parametric queries, you have to replace the question mark with the post ID manually.
select p.ap_id,
p.id,
count(case pl.score when -1 then 1 end) as dislikes,
count(case pl.score when 1 then 1 end) as likes,
sum(pl.score) as score
from post_like pl
inner join person pe on pl.person_id = pe.id
inner join post p on pl.post_id = p.id
where pe.local = true
and pl.post_id = ?
group by p.ap_id, p.id
;
Let me know if you want any other SQL queries and I might take a look into it!
Edit: Added more queries, I'll probably add more without announcing I did an edit from now on.
