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

data & reporting
1

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 thread)

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
elite-aj.jpg1343×747 156 KB

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 with our graphic designers - there’s a big difference between the two :slight_smile:

12 Likes
2

Here’s a modified version that does that:

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
10 Likes
3

Thanks @mcwumbly - that is perfect :slight_smile:

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.

1 Like