Data Explorer Query - add additional parameter in export

Community Building Data & reporting

AI-generated summary

Data Explorer Query - Add Additional Parameter in Export

jord8on wants to modify a Data Explorer query to include the user’s name, in addition to their username, in the list of “Users_Who_Replied_To_A_Topic”. The original query only returns the username.

pfaffman suggests adding u.name to the SELECT line, which jord8on implements. However, this results in a PG::GroupingError because u.name is not included in the GROUP BY clause. To resolve this, jord8on adds u.name to the GROUP BY line, successfully modifying the query.

The corrected query includes the following changes:

  • Added u.name to the SELECT line
  • Added u.name to the GROUP BY line

pfaffman and jord8on exchange thanks and appreciation for the assistance.

1

I want to have my list of “Users_Who_Replied_To_A_Topic” Include the user’s NAME.

I tried to follow a convention from another thread, which makes these Data Explorer queries include additional information but it didn’t work when I applied the same syntax.

Here’s the query I’m running right now, which only returns they “username” of those who replied to a topic…

-- https://meta.discourse.org/t/68756/8?u=sidv

-- [params]
-- topic_id :topic_id = 536

SELECT u.username
FROM badge_posts p
JOIN topics t ON p.topic_id = t.id 
JOIN users u ON p.user_id  = u.id
WHERE t.id = :topic_id
GROUP BY p.user_id,u.username
2

Add

 , u.name

After u.username in the first line.

3

Thanks for the quick help on this, Jay! I updated to the following, per your suggestion:

SELECT u.username, u.name
FROM badge_posts p
JOIN topics t ON p.topic_id = t.id 
JOIN users u ON p.user_id  = u.id
WHERE t.id = :topic_id
GROUP BY p.user_id,u.username

… then got this error…

PG::GroupingError: ERROR:  column "u.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 12: SELECT u.username, u.name

So I changed the query to the following and it worked like a charm! :tada:

SELECT u.username, u.name
FROM badge_posts p
JOIN topics t ON p.topic_id = t.id 
JOIN users u ON p.user_id  = u.id
WHERE t.id = :topic_id
GROUP BY p.user_id,u.username,u.name

So from my original snippet, I added the following:

To the “SELECT” line:
, u.name

To the “GROUP BY” line:
,u.name

Thanks again, @pfaffman for helping me with this syntax!

4

That PhD in education is finally paying off!

Nice work!

5

:squinting_face_with_tongue: Yes indeed! Thanks again :wink: