Vielen Dank dafür!
Das benutzerdefinierte Benutzerprofil-Feld, das ich verwenden möchte, heißt ‘NZRAB number’.
Ich habe es versucht, mit dem unten stehenden Skript als Ergebnis, aber offensichtlich habe ich es falsch gemacht, da dies den Fehler ‘Undefined function’ ausgelöst hat.
Ich war mir nicht sicher, ob der Feldname Großbuchstaben oder Leerzeichen ausschließen soll – der Editor schien zumindest das Leerzeichen im Feldnamen nicht zu mögen, also habe ich es durch einen Unterstrich ersetzt.
Ich verwechsle möglicherweise auch den Feldnamen mit der Feldbeschriftung – aber wenn ja, weiß ich nicht, wie ich herausfinden kann, welcher Feldname zur Beschriftung ‘NZRAB number’ gehört.
-- coverage: 'week', 'all', or '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
resultierender Fehler:
PG::UndefinedFunction: ERROR: operator does not exist: integer == integer
LINE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and c…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
Wenn Sie aufgrund dessen zu dem Schluss gekommen sind, dass ich keine Ahnung habe, was ich tue, dann liegen Sie richtig!