How to do laravel eloquent table with joins, orwhere and pagination correctly?-CodePudding

This is my original code where i display all data for team users.

 $data = Teamuser::join('teams', 'teams.id', '=', 'team_user.team_id')
            ->join('users', 'users.id', '=', 'team_user.user_id')
            ->get(['users.name as username','teams.name','users.email','team_user.role','team_user.id','team_user.user_id','team_user.team_id']);

However, since im developing a search function for the table. I try adding orwhere to the function.

$data = Teamuser::join('teams', 'teams.id', '=', 'team_user.team_id')
            ->join('users', 'users.id', '=', 'team_user.user_id')
            ->get(['users.name as username','teams.name','users.email','team_user.role','team_user.id','team_user.user_id','team_user.team_id'])
            ->where('users.name', 'like', '%'.$request->search2.'%')
            ->orWhere('teams.name', 'like', '%'.$request->search2.'%')
            ->orWhere('team_user.role', 'like', '%'.$request->search2.'%')
            ->orWhere('users.email', 'like', '%'.$request->search2.'%')->paginate(5);

But the search function doesnt work, how do i format the syntax in a correct way?

CodePudding user response：

I'm not sure you are using get() correctly, but i purpose you an alternative syntax

$data = Teamuser::select(['users.name as username','teams.name','users.email','team_user.role','team_user.id','team_user.user_id','team_user.team_id'])
            ->join('teams', 'teams.id', '=', 'team_user.team_id')
            ->join('users', 'users.id', '=', 'team_user.user_id')
            ->where('users.name', 'like', '%'.$request->search2.'%')
            ->orWhere('teams.name', 'like', '%'.$request->search2.'%')
            ->orWhere('team_user.role', 'like', '%'.$request->search2.'%')
            ->orWhere('users.email', 'like', '%'.$request->search2.'%')
            ->paginate(5);

Check if your join is correct, maybe you could use leftJoin() instead of join()

CodePudding user response：

class TeamUserController extends Controller
{
    public function index()
    {
      $teamUsers = Teamuser::query()
        ->with('team', 'user')
        ->search(request('search2'))
        ->paginate(5);
...

    // App/Models/TeamUser

    public function team()
    {
        return $this->belongsTo(Team::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function scopeSearch($query, string $terms = null)
    {
        collect(explode(' ', $terms))->filter()->each(function ($term) use ($query) {
                $term = '%'.$term.'%';
                $query->where(function ($query) use ($term) {
                    $query->where('role', 'like', $term)
                        ->orWhereHas('team', function ($query) use ($term) {
                            $query->where('name', 'like', $term);
                        })
                        ->orWhereHas('user', function ($query) use ($term) {
                            $query->where('name', 'like', $term)
                                  ->where('email', 'like', $term);
                        });
                });
            });
        }