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

Data & reporting
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 Likes
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 Likes
4

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

3 Likes
5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.