Created a script but receiving an error message. Would love some feedback/advice

Community Building Data & reporting

AI-generated summary

Drew-ART shared a SQL script intended to pull user stats like user_id, username, email, trust_level, and activity metrics, but encountered an error: relation "pr" does not exist. Falco pointed out that none of the referenced tables (e.g., pr, tv, pc, etc.) exist in Discourse’s schema, suggesting possible confusion with tables like posts or topics. JammyDodger noted the script appears adapted from a meta.discourse.org thread on user participation and advised using the full original query rather than piecing together fragments. Drew-ART thanked both for the feedback and acknowledged skipping critical parts, promising to revisit the full query. The discussion highlights common pitfalls when adapting SQL queries without fully understanding the underlying schema or context.

1

This should probably be a comment in a thread but i couldn’t find the correct one. The cool scripts one seems to be locked now.

The script is designed to pull user_id, username, name, email, custom_field1, custom_field2, trust_level, visits and a few more stats.

Here’s the code

SELECT
    pr.user_id,
    username,
    name,
    email,
    uf.custom_field1,
    uf.custom_field2,
    tl.trust_level,
    visits,
    COALESCE(topics_viewed, 0) AS topics_viewed,
    COALESCE(posts_read, 0) AS posts_read,
    COALESCE(posts_created, 0) AS posts_created,
    COALESCE(topics_created, 0) AS topics_created,
    COALESCE(topics_with_replies, 0) AS topics_with_replies,
    COALESCE(likes_given, 0) AS likes_given,
    COALESCE(likes_received, 0) AS likes_received
FROM pr
LEFT JOIN tv USING (user_id)
LEFT JOIN pc USING (user_id)
LEFT JOIN tc USING (user_id)
LEFT JOIN twr USING (user_id)
LEFT JOIN lg USING (user_id)
LEFT JOIN e USING (user_id)
LEFT JOIN uf USING (user_id)
LEFT JOIN tl USING (user_id)

and here’s the error message i’m getting:

PG::UndefinedTable: ERROR:  relation "pr" does not exist
LINE 23: FROM pr
              ^

Can anyone see what i’ve missed as i just can’t find it. I’m not skilled in SQL so its been a bit of a struggle getting this far.

Please and thank you

2

The problem is that none of those tables exist. FROM pr means to select rows from the table pr but there is no table pr in Discourse.

Maybe you meant posts or topics ? Ditto for every other join, none of those tables exist.

3

I think it’s been adapted from this post:

But you would need to use the full query @Drew-ART, rather than cherry-picking bits without adapting it to compensate for the missing pieces.

4

Thank you @Falco and @JammyDodger. Looks like i skipped a big chunk of this. I’ll have another go :slight_smile: