# 投稿承認に関するレポートは取得可能ですか？
**URL:** https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101
**Category:** Feature
**Created:** [2019 年 2 月 1 日午前 8:28 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101 "2019-02-01T08:28:49Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 2 月 1 日午前 8:28 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/1 "2019-02-01T08:28:49Z")
We’ve configured our forum to require approval for posts from new users. We approve posts pretty fast, I think, but I wanted to check the data to see how we’re doing.
Is there an admin report on how many posts we’ve approved, how long posts have to wait in the queue, which moderators approve/reject the most posts, etc.?
I couldn’t find anything obvious on the Dashboard.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [2019 年 2 月 1 日午前 11:17 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/2 "2019-02-01T11:17:57Z")
> [@dfabulich](#):
>
> We’ve configured our forum to require approval for posts from new users
Interesting, why has it come to this?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [2019 年 2 月 1 日午後 12:10 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/3 "2019-02-01T12:10:02Z")
I think you’ll need to use the [data explorer](https://meta.discourse.org/t/32566?silent=true) plugin.
---
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 2 月 1 日午後 7:09 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/4 "2019-02-01T19:09:25Z")
Well, it started when we had a troll circumventing a ban. (I think he literally posted something like, “Hah, suckers, you can’t ban me, I can just create a new account.”) He switched IPs and everything.
So we turned on new-post approval for a while, thinking it would be temporary. But it turned out to be not that onerous, and it gave us an opportunity to fix problems before they start, so we just left it on.
It came in especially handy when the moderator of another forum offered karma points to anyone who successfully trolled us. 🙃
---
### Author:  [@rizka](https://meta.discourse.org/u/rizka)
#### Post date: [2019 年 2 月 1 日午後 7:58 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/5 "2019-02-01T19:58:27Z")
> [@dfabulich](#):
>
> Well, it started when we had a troll circumventing a ban. (I think he literally posted something like, “Hah, suckers, you can’t ban me, I can just create a new account.”) He switched IPs and everything.
>
> So we turned on new-post approval for a while, thinking it would be temporary. But it turned out to be not that onerous, and it gave us an opportunity to fix problems before they start, so we just left it on.
I could have written this. We have set so that all posts by TL0 users are approved by staff.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [2019 年 2 月 1 日午後 9:01 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/6 "2019-02-01T21:01:22Z")
These are good things for @eviltrout to read as he is working on a big project centralizing all approval and flag handling paths.
---
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 2 月 1 日午後 10:08 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/7 "2019-02-01T22:08:23Z")
For the record, here are the queries I’ve come up with using the [data explorer](https://meta.discourse.org/t/32566?silent=true) plugin. (I’d appreciate code review on these.)
Approvals and rejections by moderator in the last 30 days:
```plaintext
SELECT count(q.id), username, state
FROM queued_posts q
JOIN users u on q.approved_by_id = u.id
OR q.rejected_by_id = u.id
WHERE queue='default'
AND q.created_at > now() - interval '30 days'
GROUP BY username, state
ORDER BY username
```
Average time to approval:
```plaintext
SELECT sum(approved_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 2
AND created_at > now() - interval '30 days'
```
Average time to rejection:
```plaintext
SELECT sum(rejected_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 3
AND created_at > now() - interval '30 days'
```
For us, we had a 37 minute time to approval and a 56 minute time to rejection in the last 30 days. (I suspect that rejection time may be longer because sometimes when moderators see a questionable post, they may decide to pass the buck and leave it in the queue for another moderator.)
---
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 2 月 4 日午後 5:25 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/8 "2019-02-04T17:25:10Z")
> [@eviltrout](#):
>
> The [new schema](https://github.com/discourse/discourse/blob/reviewable/app/models/reviewable.rb#L215-L232) doesn’t have rejected/approved/resolved. Instead you have a `status` and a separate table, `reviewable_history` which tracks all the status changes.
@eviltrout How would I construct queries like this in the new schema?
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [2019 年 2 月 4 日午後 10:05 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/9 "2019-02-04T22:05:31Z")
I’d prefer not to type up how to do it until the new schema is merged, but it is possible to do all these reports.
---
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 4 月 9 日午前 1:06 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/10 "2019-04-09T01:06:31Z")
Looks like the new schema is merged! Can you suggest how to generate reports like these in the new review queue?
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [2019 年 4 月 9 日午後 12:38 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/11 "2019-04-09T12:38:49Z")
It looks like you are comfortable with SQL. All reviewable items are in the `reviewables` table, with a `type` column that differentiates which type of thing it is. The `status` column indicates the current state (pending / approved / rejected / ignored / deleted.)
If you want to know when something happened to a reviewable, that information is in the `reviewable_histories` table.
---
### Author:  [@dfabulich](https://meta.discourse.org/u/dfabulich)
#### Post date: [2019 年 4 月 9 日午後 7:32 UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-report-on-post-approvals/108101/12 "2019-04-09T19:32:06Z")
Approvals and rejections by moderator in the last 30 days:
```plaintext
SELECT username, r.status, count(r.id)
FROM reviewables r
JOIN (
SELECT max(h.id) as history_id, reviewable_id
FROM reviewable_histories h
GROUP BY reviewable_id
) latest
ON r.id = latest.reviewable_id
JOIN reviewable_histories h
ON h.id = latest.history_id
JOIN users u
ON h.created_by_id = u.id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY username, r.status
ORDER BY username, r.status
```
Average time to approval/rejection:
```plaintext
SELECT sum(h.created_at - r.created_at)/count(r.id), r.status
FROM reviewables r
JOIN (
SELECT max(h.id) as history_id, reviewable_id
FROM reviewable_histories h
GROUP BY reviewable_id
) latest
ON r.id = latest.reviewable_id
JOIN reviewable_histories h
ON h.id = latest.history_id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY r.status
ORDER BY r.status
```