¡Muchas gracias por eso!
El campo de perfil de usuario personalizado que estoy intentando usar es ‘NZRAB number’
Intenté hacerlo con el siguiente script, pero claramente lo hice mal, ya que esto dio un error de ‘Función indefinida’.
No estaba seguro si el nombre del campo debe excluir letras mayúsculas o espacios; al menos al Editor no le gustó el espacio en el nombre del campo, así que lo cambié por un guion bajo.
También podría estar confundiendo el nombre del campo con la etiqueta del campo, pero si es así, no sé cómo averiguar qué nombre de campo corresponde a la etiqueta ‘NZRAB number’.
-- coverage: 'week', 'all', o '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
Error resultante:
PG::UndefinedFunction: ERROR: operador no existe: integer == integer
LINE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and c…
^
HINT: No hay ningún operador que coincida con el nombre y los tipos de argumentos dados. Es posible que necesite agregar conversiones de tipo explícitas.
Si dedujiste de esto que no tengo ni idea de lo que estoy haciendo, tendrías razón.