Merci beaucoup pour cela !
Le champ de profil utilisateur personnalisé que j’essaie d’utiliser est « NZRAB number »
J’ai essayé, avec le script résultant ci-dessous, mais j’ai évidemment fait une erreur, car cela a généré une erreur « Fonction indéfinie ».
Je ne savais pas si le nom du champ devait exclure les lettres majuscules ou les espaces — l’éditeur n’aimait visiblement pas l’espace dans le nom du champ, du moins, alors j’ai remplacé l’espace par un underscore.
Je confonds peut-être aussi le nom du champ avec l’étiquette du champ — mais dans ce cas, je ne sais pas comment déterminer quel nom de champ correspond à l’étiquette « NZRAB number ».
-- coverage: 'week', 'all', ou 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week
WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
( :start_date::timestamp
, :end_date::timestamp
, '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as NZRAB_number
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = 'NZRAB number')
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
Erreur résultante :
PG::UndefinedFunction: ERROR: opérateur n’existe pas : integer == integer
LIGNE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id et c…
^
INDICE: Aucun opérateur ne correspond au nom et aux types d’arguments donnés. Vous devrez peut-être ajouter des cast de type explicites.
Si vous en avez conclu que je ne sais absolument pas ce que je fais, vous auriez raison !