# Help modifying a query to return usernames, instead of user ID numbers

**URL:** https://meta.discourse.org/t/help-modifying-a-query-to-return-usernames-instead-of-user-id-numbers/275025
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [24.Январь.2018 05:21:31 UTC](https://meta.discourse.org/t/help-modifying-a-query-to-return-usernames-instead-of-user-id-numbers/275025 "2018-01-24T05:21:31Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![BenLeong](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/benleong/32/60951_2.png) [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [24.Январь.2018 05:21:31 UTC](https://meta.discourse.org/t/help-modifying-a-query-to-return-usernames-instead-of-user-id-numbers/275025/1 "2018-01-24T05:21:31Z")

</div>

Hi! I’m looking for a bit of help modifying a query to return **usernames** , instead of user ID numbers.

This is what I’ve been using so far (modified from @DavidGNavas’ excellent [Network Chart](https://meta.discourse.org/t/network-chart-of-your-forum-data-visualization/37426) thread)

```plaintext
WITH pairs AS (
    SELECT p.user_id liked, pa.user_id liker
    FROM post_actions pa
    LEFT JOIN posts p ON p.id = pa.post_id
    LEFT JOIN topics t ON t.id = p.topic_id
    LEFT JOIN categories c ON c.id = t.category_id
    WHERE post_action_type_id = 2
    AND c.id = 47
)
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs
GROUP BY liked, liker
ORDER BY count DESC

```

Which I’ve then used a very clunky vlookup to match user names with ID numbers…

Once I have a list of pairs (likers & liked) expressed as usernames, it lets me follow the method used in the Network Chart thread to generate these sorts of charts - showing activity within specific categories, as we have very distinct sub-communities on different parts of our forums:

 ![elite-aj](https://global.discourse-cdn.com/meta/original/3X/5/9/593b2a9467a2c667ff1df48bfb0c562743a718a3.jpg)

The live charts are a valuable way of exploring our different sub-communities, quickly identifying influential members and clusters of people.

For example, compare our [musician community](https://goo.gl/czBrzH) with our [graphic designers](https://goo.gl/RyNGX4) - there’s a big difference between the two 🙂

---

<div class="post-metadata">

### Author: ![mcwumbly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mcwumbly/32/103861_2.png) [@mcwumbly](https://meta.discourse.org/u/mcwumbly)
#### Post date: [24.Январь.2018 11:32:25 UTC](https://meta.discourse.org/t/help-modifying-a-query-to-return-usernames-instead-of-user-id-numbers/275025/2 "2018-01-24T11:32:25Z")

</div>

> [@BenLeong](#):
>
> Hi! I’m looking for a bit of help modifying a query to return usernames, instead of user ID numbers.

Here’s a modified version that does that:

```plaintext
WITH pairs AS (
    SELECT p.user_id liked_id, pa.user_id liker_id
    FROM post_actions pa
    LEFT JOIN posts p ON p.id = pa.post_id
    LEFT JOIN topics t ON t.id = p.topic_id
    LEFT JOIN categories c ON c.id = t.category_id
    WHERE post_action_type_id = 2
    AND c.id = 1
)
SELECT 
  liker.username as liker,
  liked.username as liked,
  count(*)
FROM pairs
LEFT JOIN users liker
ON liker_id = liker.id
LEFT JOIN users liked
ON liked_id = liked.id
GROUP BY liked, liker
ORDER BY count DESC

```

---

<div class="post-metadata">

### Author: ![BenLeong](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/benleong/32/60951_2.png) [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [25.Январь.2018 04:53:04 UTC](https://meta.discourse.org/t/help-modifying-a-query-to-return-usernames-instead-of-user-id-numbers/275025/3 "2018-01-25T04:53:04Z")

</div>

Thanks @mcwumbly - that is perfect 🙂

I’ll start putting together network charts for all the other bits of our forums, now the process doesn’t require a workaround to get hold of usernames.