fetch data for all users in same group

Question

My code works. But I'm not sure is it a best solution. I need option to display user players and / or players from a group user belong to. Thank you for you time.


$user = $request->user();
$userGroups = $user->groups;
$friendsPlayers = [];

foreach ($userGroups as $group) {

    $groupUsers = $group->users;

    foreach ($groupUsers as $groupUser) {

        if ($groupUser->id !== $user->id) {

            $userPlayer = $groupUser->players;

            foreach ($userPlayer as $player) {

                if (!in_array($player, $friendsPlayers)) {

                    $friendsPlayers[] = $player;
                }
            }
        }
    }
}

1.Schema/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 Schema::create('groups', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('info');
        $table->timestamps();
    });

 Schema::create('players', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned()->index();
        $table->string('name');
        $table->string('url');
        $table->string('type');
        $table->integer('wins');
        $table->integer('lost');
        $table->integer('draws');
        $table->timestamps();
    });

   Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
 Schema::create('group_user', function (Blueprint $table) {

        $table->integer('group_id')->unsigned()->nullable();
        $table->foreign('group_id')->references('id')
              ->on('groups')->onDelete('cascade');

        $table->integer('user_id')->unsigned()->nullable();
        $table->foreign('user_id')->references('id')
              ->on('users')->onDelete('cascade');
        $table->timestamps();
    });

2.Models/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 class Group extends Model
 {
 protected $fillable = [
    'name', 'info',
 ];
 public function users() {
    return $this->belongsToMany(User::class);
 }
 }


 class Player extends Model
 {   
 protected $guarded = [];


public function user() {
        return $this->belongsTo(User::class);
}
public function leagues() {
    return $this->belongsToMany(League::class)->withPivot('win', 'lost', 
'draw')->withTimestamps();
}


class User extends Authenticatable
{
use Notifiable;


protected $fillable = [
    'name', 'email', 'password',
];


protected $hidden = [
    'password', 'remember_token',
];
public function players() {
    return $this->hasMany(Player::class);
}
public function leagues() {
    return $this->hasMany(League::class);
}
 public function scoreboards() {
    return $this->hasMany(Scoreboard::class);
 }
 public function groups() {
    return $this->belongsToMany(Group::class);
 }
 }

Difficult to help you if you don't provide the tables' structure. — koalaok, May 10 '19 at 06:44

Kevin Bui · Answer 1 · 2019-05-24T02:51:33.197

0

This could be a perfect use case to use Laravel Collection.

$result = $user->load('groups.groupUsers.players')
    ->groups
    ->map->groupUsers
    ->collapse()
    ->filter(function ($groupUser) use ($user) {
        return $groupUser->isNot($user);
    })
    ->unique('id')
    ->map->players
    ->collapse()
    ->unique('id');

A different approach is using a query to get the result.

First, lets create a sub query to get all the groups the user joined.

$groupsJoinedByUser = Group::whereHas('users', function ($query) use ($user) {
    $query->whereKey($user->id);
});

We can also create that sub-query this way:

$groupsJoinedByUser = Group::select('groups.*')
    ->join('group_user', 'groups.id', '=', 'group_user.group_id')
    ->where('group_user.user_id', $user->id);

Now we can create the query to get the players:

$players = Player::select('players.*')
    ->join('users', 'players.user_id', '=', 'users.id')
    ->join('group_user', 'users.id', '=', 'group_user.user_id')
    ->joinSub($groupsJoinsByUser, 'groups_joined_by_user', function($join) {
        $join->on('group_user.group_id', '=', 'groups_joined_by_user.id')
    })
    ->where('users.id', '!=', $user->id);
    ->distinct()
    ->get();

edited May 24 '19 at 02:51

answered May 10 '19 at 07:14

Kevin Bui

2,754
1
14
15

could you explain bit more about $user->load('groups.groupUsers.players') part? – Danbass07 May 10 '19 at 10:47
I found the answer here https://stackoverflow.com/questions/26005994/laravel-with-method-versus-load-method – Danbass07 May 10 '19 at 10:49
I have updated my answer according to the database schema. – Kevin Bui May 13 '19 at 06:08
Thank you very much for answer. I didn't expect to gain that much from just one question. I'm exploring both metod's. So many possibilities :) :) – Danbass07 May 13 '19 at 11:53
still got a problem with ->join($groupsJoinsByUser, 'groups_joined_by_user', function($join) { $join->on('group_user.group_id', '=', 'groups_joined_by_user.id') }) i made some changes ->join($groupsJoinedByUser, 'groups_joined_by_user', function($join) { $join->on('group_user.group_id', '=', 'groups_joined_by_user.id'); }) but still its not working. Got an error expects parameter 1 to be string, object given – Danbass07 May 22 '19 at 09:36
I am sorry, I have just updated my answer. It has to be joinSub, not join, to join the sub query. – Kevin Bui May 24 '19 at 02:52

fetch data for all users in same group

1 Answers1