1

My table schema is a id field, name field and a friends id field.

Every user must have a maximum of 2 friends. When a new user is created and event is fired and a listener which listens to the created user event then adds a random friend to a newly created user.

 $randomfriend =  DB::table('users')->select('id')
                                   ->groupBy('friends_id')
                                   ->havingRAW('COUNT(*) < 2')
                                   ->inRandomOrder()->first();

it still returns users with maximum number of friends. Can someone help me with this?

Troyer
  • 6,765
  • 3
  • 34
  • 62
Patrick Obafemi
  • 908
  • 2
  • 20
  • 42
  • remove groupby? – Ess Kay Sep 08 '17 at 13:58
  • 1
    pls run `dd(DB::table('users')->select('id') ->groupBy('friends_id') ->havingRAW('COUNT(*) < 2') ->inRandomOrder()->toSql());` and print the output so we can see the query – cre8 Sep 08 '17 at 13:58
  • 1
    How are you storing the friends relationship? – Rwd Sep 08 '17 at 14:20
  • @Ross Wilson am not using eloquent as that's part of the requirements. – Patrick Obafemi Sep 08 '17 at 14:44
  • 1
    Sorry, poor choice of words. How do you tell if a user has friends i.e. is it through a pivot table, column(s) on the users table etc? I've noticed you have a `friends_id` column but it's not clear how you're using it as you've said a user can have 2 friends. – Rwd Sep 08 '17 at 14:48
  • Am just using one table that is user's table. I have this the DB query inside a function. If a there's a user that his ID appears twice under the friend's ID column then that user can not be returned as a result of that query – Patrick Obafemi Sep 08 '17 at 15:30

1 Answers1

2

first of all create another table for friends relations. for selecting a random user this post can help you: enter link description here

don't forget to put this code in while loop for checks.

Amin.Qarabaqi
  • 661
  • 7
  • 19