Ich habe versucht, den Bericht „Anforderungen für Vertrauenslevel 3“, den Mitarbeiter auf der Admin-Profilseite eines Benutzers sehen können, als Data Explorer-Abfrage nachzubilden.
Der Hintergrund dafür ist, dass Benutzer mit TL2 dank der neuen Funktion, mit der eine Abfrage auf die ID des aktuellen Benutzers beschränkt werden kann ihren eigenen Fortschritt prüfen können, ohne Mitarbeiter fragen zu müssen.
Die Abfrage nutzt die in den Seiteneinstellungen konfigurierten Werte und vergleicht sie mit den Statistiken des Benutzers. Ich habe einige winzige Unterschiede zwischen der Admin-Ansicht und den Ergebnissen der Abfrage festgestellt, die wahrscheinlich auf Rundungsfehler zurückzuführen sind. Die Logik für die Beförderung auf TL3 ist ziemlich komplex, daher ist die SQL-Abfrage zwangsläufig etwas kompliziert, und es könnten noch Fehler enthalten sein. Ich würde mich sehr über Rückmeldungen freuen, damit ich etwaige Fehler korrigieren kann.
Hier ist die aktuelle Abfrage:
Derzeit ist diese Abfrage nicht auf current_user_id beschränkt, da es für Tests einfacher ist, einen Benutzer auswählen zu können, auf den die Abfrage angewendet wird. Um das zu ändern, ändern Sie die zweite Zeile in -- current_user_id :user_id
-- [params]
-- user_id :user_id
WITH settings AS (
SELECT
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_time_period'), 100) AS time_period,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_days_visited'), 50) AS min_days_visited,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) AS min_topics_replied_to,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) AS topics_viewed_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) AS topics_viewed_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) AS posts_read_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) AS posts_read_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_all_time'), 200) AS min_topics_viewed_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) AS min_posts_read_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_given'), 30) AS min_likes_given,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_received'), 20) AS min_likes_received,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) AS max_flagged
),
period AS (
SELECT
NOW() - (time_period || ' days')::interval AS since,
time_period
FROM settings
),
global_counts AS (
SELECT
(SELECT COUNT(*) FROM topics t, period p
WHERE t.created_at > p.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_recent,
(SELECT COUNT(*)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS posts_recent
),
user_stats AS (
SELECT
u.id AS user_id,
(SELECT COUNT(*) FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
AND uv.posts_read > 0
) AS days_visited,
(SELECT COUNT(DISTINCT p.topic_id)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.user_id = u.id
AND p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.user_id <> u.id
) AS topics_replied_to,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id, period pr
WHERE tv.user_id = u.id
AND tv.viewed_at > pr.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_recent,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
WHERE tv.user_id = u.id
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_all_time,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
) AS posts_read_recent,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv
WHERE uv.user_id = u.id
) AS posts_read_all_time,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 1
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_given,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received,
(SELECT COUNT(DISTINCT DATE(ua.created_at))
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_days,
(SELECT COUNT(DISTINCT ua.acting_user_id)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_users
FROM users u
WHERE u.id = :user_id
),
flags AS (
SELECT
COUNT(DISTINCT p.id) AS flagged_posts,
COUNT(DISTINCT pa.user_id) AS flagged_users
FROM posts p
JOIN post_actions pa ON pa.post_id = p.id
JOIN period pr ON true
WHERE p.user_id = :user_id
AND p.created_at > pr.since
AND pa.agreed_at IS NOT NULL
AND pa.user_id <> :user_id
AND (p.spam_count > 0 OR p.inappropriate_count > 0)
),
penalty_history AS (
SELECT
uh.target_user_id AS user_id,
SUM(
CASE
WHEN uh.action = 30 THEN 1
WHEN uh.action = 31 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS silenced_delta,
SUM(
CASE
WHEN uh.action = 10 THEN 1
WHEN uh.action = 11 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS suspended_delta
FROM user_histories uh
WHERE uh.created_at > NOW() - INTERVAL '6 months'
AND uh.target_user_id = :user_id
GROUP BY uh.target_user_id
),
penalties AS (
SELECT
u.id AS user_id,
COALESCE(ph.silenced_delta, 0)
+ CASE
WHEN u.silenced_till IS NOT NULL AND u.silenced_till > NOW()
THEN 1 ELSE 0
END AS silenced,
COALESCE(ph.suspended_delta, 0)
+ CASE
WHEN u.suspended_till IS NOT NULL AND u.suspended_till > NOW()
THEN 1 ELSE 0
END AS suspended
FROM users u
LEFT JOIN penalty_history ph ON ph.user_id = u.id
WHERE u.id = :user_id
)
SELECT
requirement,
required_value,
user_value,
fulfill
FROM (
SELECT
'Topics Replied To (last ' || s.time_period || ' days)' AS requirement,
s.min_topics_replied_to AS required_value,
us.topics_replied_to AS user_value,
CASE
WHEN us.topics_replied_to >= s.min_topics_replied_to
THEN '✔' ELSE '✘'
END AS fulfill
FROM user_stats us, settings s
UNION ALL
SELECT
'Topics Viewed (last ' || s.time_period || ' days)',
LEAST(CEIL(gc.topics_recent * s.topics_viewed_percent / 100.0), s.topics_viewed_cap)::int,
us.topics_viewed_recent,
CASE WHEN us.topics_viewed_recent >=
LEAST(gc.topics_recent * s.topics_viewed_percent / 100, s.topics_viewed_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Topics Viewed (all time)',
s.min_topics_viewed_all_time,
us.topics_viewed_all_time,
CASE WHEN us.topics_viewed_all_time >= s.min_topics_viewed_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (last ' || s.time_period || ' days)',
LEAST( CEIL(gc.posts_recent * s.posts_read_percent / 100.0), s.posts_read_cap)::int,
us.posts_read_recent,
CASE WHEN us.posts_read_recent >=
LEAST(gc.posts_recent * s.posts_read_percent / 100, s.posts_read_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Posts Read: unique days (last ' || s.time_period || ' days)',
s.min_days_visited,
us.days_visited,
CASE WHEN us.days_visited >= s.min_days_visited THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (all time)',
s.min_posts_read_all_time,
us.posts_read_all_time,
CASE WHEN us.posts_read_all_time >= s.min_posts_read_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Flagged Posts (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_posts,
CASE WHEN f.flagged_posts <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Users Who Flagged (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_users,
CASE WHEN f.flagged_users <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Likes Given (last ' || s.time_period || ' days)',
s.min_likes_given,
us.likes_given,
CASE WHEN us.likes_given >= s.min_likes_given THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received (last ' || s.time_period || ' days)',
s.min_likes_received,
us.likes_received,
CASE WHEN us.likes_received >= s.min_likes_received THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique days (last ' || s.time_period || ' days)',
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int,
us.likes_received_days,
CASE WHEN us.likes_received_days >=
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique users (last ' || s.time_period || ' days)',
CEIL(s.min_likes_received / 4.0)::int,
us.likes_received_users,
CASE WHEN us.likes_received_users >= CEIL(s.min_likes_received / 4.0)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Silenced (last 6 months)',
0,
p.silenced,
CASE WHEN p.silenced = 0 THEN '✔' ELSE '✘' END
FROM penalties p
UNION ALL
SELECT
'Suspended (last 6 months)',
0,
p.suspended,
CASE WHEN p.suspended = 0 THEN '✔' ELSE '✘' END
FROM penalties p
) x
Beispiel-Ergebnis:
