Our marketing department wants some data. I can query discourse to get it. However, I am having some trouble with user_custom_fields: We have several custom fields and each one seems to be a row in the table per user. So I may have 6-7 rows per user. I’m working on a join but I start to get many rows per record so I need to figure out how to query properly.
Here is what I have so far:
SELECT u.id, u.username_lower AS "username", u.created_at, u.last_seen_at, u.ip_address, ue.email, (SELECT COUNT(*) FROM user_badges ub WHERE ub.user_id = u.id ) AS badge_count FROM users u LEFT OUTER JOIN user_emails ue on u.id = ue.user_id LEFT OUTER JOIN user_custom_fields ucf on u.id = ue.user_id WHERE u.active = true AND u.username_lower='slackmoehrle' ORDER BY u.id;
Here is what user_custom_fields looks like:
ideally, I want one record, per user, that shows the fields I want plus the values for the rows in user__custom__fields…
Any help figuring out the joins/syntax?