Selecting heirarchical data in MySQL

Question

I have this table (followers) in my database.

follower | following
---------|----------
1        |2
1        |4
1        |5
1        |6
1        |7
1        |8
2        |4
4        |6

This documents all of the followings and followers for my users. I have this query, which is not working.

SELECT following 
FROM followers 
WHERE follower 
IN (SELECT following 
    FROM followers 
    WHERE follower = 2) 
OR following IN (SELECT follower 
                 FROM followers 
                 WHERE following = 2)

What I am trying to achieve is a list of users followed by the users that the user "2" follows, merged with a list of users that follow the user "2". Excluding everyone already being followed by user "2". And arranged by how closely related to the user "2" they are.

Ideally, my data output would be this (but not automatically that order):

suggested_users
---------------
6
1
5
7
8

I am actually at a loss as to how to ask this question, but how do I get these results?

As MySQL does not support recursive queries, the only way to do this is to write a stored procedure. — , Jun 15 '13 at 21:51
You can always upgrade to Postgres where this is a piece of cake (with a single statement) — , Jun 15 '13 at 21:54
What is the statement with Postgres? I might switch, MySQL seems a little soft :P — Forest, Jun 15 '13 at 21:55

Denis de Bernardy · Accepted Answer · 2013-06-15T22:00:32.410

In case it's an option for you to dump mysql, some sql variants let you get the tree using a recursive query:

http://www.postgresql.org/docs/current/static/queries-with.html

If not, there are a variety of solutions; typically nested sets or nested intervals:

http://en.wikipedia.org/wiki/Nested_set_model

http://en.wikipedia.org/wiki/Nested_intervals

(There are many questions on SO related to both.)

What is the statement with Postgres? I might switch, MySQL seems a little soft :P

Untested, but it's something like:

with recursive tree (root, node) as (
select following as root, follower as node
  from followers
union all
select tree.root, followers.follower
  from followers
  join tree on tree.node = followers.following
)
select * from tree where root = 2;

And the optimized version would be like:

with recursive tree (root, node) as (
select following as root, follower as node
  from followers
 where following = 2
union all
select tree.root, followers.follower
  from followers
  join tree on tree.node = followers.following
)
select * from tree;

Aiias · Answer 2 · 2013-06-15T23:17:20.437

This can be done by joining the followers table on itself.

Try this:

SELECT f2.following suggested_users
FROM followers f1
INNER JOIN followers f2 ON f2.follower = f1.following
WHERE f1.follower = 2
GROUP BY f2.following

UNION

SELECT f3.follower suggested_users
FROM followers f3
WHERE f3.following = 2
GROUP BY f3.follower

The general idea of this query is that:

f1.follower follows f1.following and f1.following = f2.follower
f2.follower follows f2.following

combined with

f3.follower follows f3.following and f3.following = 2

The table joins will look like this:

First sub-query

f1.follower | f1.following = f2.follower | f2.following | suggested_users |
------------|----------------------------|--------------|-----------------|
2           |4                           |6             | 6               |

Second sub-query

f3.follower | f3.following | suggested_users |
------------|--------------|-----------------|
1           |2             |1                |

Single quotes are for string literals not for identifiers. – Jun 15 '13 at 22:13 — , Jun 15 '13 at 22:13

Selecting heirarchical data in MySQL

2 Answers2