0

I am trying to create a function in MySQL for my 'friendships' table where I have 4 columns:

friendship_id, status, user1_id and user2_id (last two are also foreign keys which point to another table 'users').

Function should receive one parameter which is users id and return how many friends that user has.

I created a function like this:

CREATE FUNCTION num_of_friends(id int) 
RETURNS int(11)
BEGIN

SET @friends_count = (SELECT COUNT(user2_id) FROM friendships WHERE user1_id = id);
RETURN @friends_count;
END

So this function returns number of friends for that specific user, if his id is in column user1_id.

But there are some users whose id is only in the column user2_id.

How could I check if the user's id which we passed as parameter exists in either of the columns and count friendships based on that?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
alex
  • 3
  • 1
  • Does this answer your question? [How to Check if value exists in a MySQL database](https://stackoverflow.com/questions/11292468/how-to-check-if-value-exists-in-a-mysql-database) – avocadoLambda Jun 21 '20 at 17:30

1 Answers1

1

You can compare the id value with both the columns with OR

CREATE FUNCTION num_of_friends(id int) 
RETURNS int(11)
BEGIN

SET @friends_count = (SELECT COUNT(*) from (select * from friendship  where user1_id=id
                      union
                      select user2_id,user1_id from friendship where user2_id=id)data);
RETURN @friends_count;
END

Check this SQL fiddle: http://sqlfiddle.com/#!9/c5928ca/8

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
  • Thanks for your reply! I've tried this one, but the issue in this case is if we have a mutual friendship which appears in 2 rows (same users in 2 rows but in the first one user1_id will be for example 1 and user2_id will be 2, and in the other one it will be the other way around ). In this case I would get 2 friendships if I pass 1 as parameter, instead of 1 friendship. Any way I could exclude this "double" friendships? – alex Jun 21 '20 at 21:47
  • Hey @alex : I've updated the answer. Check once and you can also verify the data in SQL Fiddle link. – Abhishek Ginani Jun 22 '20 at 05:06
  • thanks a lot @Abhishek ! Union was what I needed :) – alex Jun 22 '20 at 18:29