Complex MySQL query?

Question

PHP

Hi all!
I apologize in advance for such an impudent, I would say question)))
On the site, users add each other as friends!
There is a table where all this is fixed, but I can’t figure out how to correctly compose a mysql query to the database.
The friends table

The bottom line is that you need to display three blocks where it will be shown:
1. FRIENDS (these are those who added each other as friends)
2. SIGNED (these are those whom I added as friends)
3. SUBSCRIBERS (these are those who added me to friends)
I can't make the first request! I think ... It is necessary to select all the users whom I added as friends, and then check if they added me as friends, here I just don’t understand how to make a request!
Please tell me who is well versed in this or good)) how to make a request?
Thanks in advance!
Maybe someone will throw a link where it says something like that)
Thanks too!

Answer 1

From the point of view of performance with large amounts of data, the most correct thing for the first request is to keep a separate plate like yours, but add data there only if the friendship is mutual.
Update it accordingly when adding/removing friendships.

Answer 2

SELECT `t1`.`friendid`
    FROM `friends` AS `t1`
    JOIN `friends` AS `t2` ON `t2`.`userid` = `t1`.`friendid`
        AND `t2`.`friendid` = `t1`.`userid`
    WHERE `t1`.`userid` = :myId

And the `id` field in this table is unnecessary, the pair (`userid`, `friendid`) should itself form the primary key.