Jack Jack - 1 month ago 5
MySQL Question

MYSQL Query - Checking result is true/false and count

I am trying to check if the current user is already following the selected user, and I am doing this like so:

(I know it's not the best way, but as I am new to MYSQL this is as much as I have been able to come up with)

SELECT EXISTS (SELECT 1 FROM Activity WHERE IdOtherUser = 86 AND id = 145)


I am '145' and the user I selected is '86'.

Now that return '0' If I am not following and '1' If I am following that person.

Seems to be working already but it definetly needs improving!

Now what I would like to do is count the followers in the same query.

So count the people I am following and the people following me.

'Activity' is the table where I store the followers and I save them like this:

'id' = me

'idOtherUser' = other user I followed

'type' = type of action "follow"

I have done count's before when calculating the like counts, but I just cannot get my head around this!!

If anyone could spare some time to help me it is much appreciated!

I am sorry if the question is not the best, but I am still learning and trying my best to format them as clear as possible to understand.

Thanks in advance!!

Answer

If you trying to count the followers from specific id from table Activity you might do this way:

SELECT COUNT(idOtherUser) AS "I Follow",
       (SELECT COUNT(idOtherUser) FROM Activity WHERE idOtherUser = 145 AND type = "follow"
) AS "FOLLOW ME",
       (SELECT COALESCE(id,0) FROM Activity WHERE IdOtherUser = 86 AND id = 145 AND type = "follow")
FROM Activity WHERE id = 145 AND type = "follow"