Create a Network chart of your forum - data visualization

Wiki Administrators
,
4

Bumping an old topic, as I’ve been experimenting with this a lot over the last few days… the network graphs have been very useful for identifying which members to look at in more detail, even just viewing user_id (though username would be far more valuable).

@riking I’ve tried your suggested change to the query in order include usernames (for example, using the first example query from this thread, looking at a specific user group):

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
    WHERE post_action_type_id = 2
)
SELECT u1.username_lower liker_username, u2.username_lower liked_username, count(*)
FROM pairs, group_users AS a, groups AS b
LEFT JOIN users u1 ON u1.id = liker
LEFT JOIN users u2 ON u2.id = liked
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs, group_users AS a, groups AS b
where ( liker = a.user_id
and a.group_id = b.id
and b.name ilike 'group1' )
and
liked in (select c.user_id from  group_users AS c, groups AS d
where
c.group_id = d.id
and d.name ilike 'group1' )
GROUP BY liked, liker
ORDER BY count DESC

That runs into this problem though:

PG::UndefinedColumn: ERROR: column “liker” does not exist
LINE 16: LEFT JOIN users u1 ON u1.id = liker
^
HINT: There is a column named “liker” in table “pairs”, but it cannot be referenced from this part of the query.

This is beyond my (extremely basic!) SQL knowledge to troubleshoot :frowning: Is there something obvious that I’m missing with this query?

3 Likes