Laravel query with subqueries and foreach

Question

I wanna to show common users verified, not out of date and unbanned:

a) all songs b) songs by title or text c ) songs by tag

Two additions:

The user, which is an admin, can see unverified, banned and out of date songs and the user, which is an artist, can see unverified or banned songs too, but only his own ones.

It has been exhausting for several days, 'where in where, which is in loop...' is torture xD

Could you help my with scopeByUser funtion?

Song Model:

class Song extends Model
{
    use HasFactory, Likable;

    public function artist()
    {
        return $this->belongsTo(Artist::class, 'artist_id');
    }

    public function tags()
    {
        return $this->belongsToMany(Tag::class)->withTimestamps();
    }

    public function scopeByUser()
    {
        $user = current_user();

        if ($user->hasRole('admin')) {
            dd("x");
            return $this;
        } elseif (isset($user->artist)) {

            return $this->where([
                'isVerified' => true,
                'isOutOfDate' => false,
                'isBanned' => false
            ])->orWhere(function () use ($user) {
                foreach ($user->artist as $artist) {
                    $this->where([
                        'artist_id', $artist->id,
                        'isOutOfDate' => false,
                        'isBanned' => false
                    ]);
                }
            });

        } else
            return $this->where([
                'isVerified' => true,
                'isOutOfDate' => false,
                'isBanned' => false
            ]);

    }
}

SongController:

public function index(Request $request)
    {

        if (request('tag')) {
            $songs = Song::query()
                ->whereHas('tags', function ($query) {
                    $tag = request('tag');
                    $query->where('name', $tag);
                })
                ->ByUser()
                ->withLikes()
                ->get();
        } elseif ($request) {
            $search = $request->input('search');

            $songs = Song::query()
                ->where('title', 'LIKE', "%{$search}%")->orWhere('text', 'LIKE', "%{$search}%")
                ->ByUser()
                ->withLikes()
                ->get();
        } else {
            $songs = Song::latest()
                ->ByUser()
                ->withLikes()
                ->get();

        }

        return view('welcome', compact('songs'));
    }

user_5543999 · Answer

One thing that I love about Laravel Eloquent is the when() method.

Laravel Collection #when()

I know the link is in the Collection, but it work with queries. So, let review your queries and set it as one that you can make logical test and change it.

There is a non tested code in the only purpose of showing you what you could achieve.

    $user = User::with('roles')->with('artists')->find(Auth::user()->id);

    $songs = Song::when($request('tag'), function($query) use($request) {
                        $query->whereHas('tags', function($query2) use($request('tag'))  {
                            $query2->where('name', $request('tag'));
                        });
                    })->when($request('search'), function($query) use($request) {
                        $query->where('title', 'LIKE', "%{$request('search)}%")->orWhere('text', 'LIKE', "%{$request('search)}%");
                    })->when(!isset($request('search')) && !isset($request('tags')), function($query) {
                        $query->latest();
                    })->when(true, function($query) use($user) {//setting when to true let you do more complexe logical test
                        if ($user->hasRole('admin')) {
                            //Nothing to do...
                        } else if (isset($user->artist)) {
                            $query->where([
                                        'isVerified' => true,
                                        'isOutOfDate' => false,
                                        'isBanned' => false
                                    ])->orWhere(function ($query2) use ($user) {
                                        foreach ($user->artist as $artist) {
                                            $query2->where([//probably change this to orWhere. You can also get the array of artist id and use whereIn('id', $arrayOfId)->where(['isOutOfDate' => false, 'isBanned' => false]);
                                                'artist_id', $artist->id,
                                                'isOutOfDate' => false,
                                                'isBanned' => false
                                            ]);
                                        }
                                    });
                        } else {
                            $query->where([
                                        'isVerified' => true,
                                        'isOutOfDate' => false,
                                        'isBanned' => false
                                    ]);
                        }
                    })->ByUser()
                    ->withLikes()
                    ->get();

So, get back to what you really asked... using scope... I discourage using Scope when you are doing complexe logical test. WHY??? Simply because it look like your scope is retrieving the user and I'm assuming that this method make at least one request to DataBase... so, this lead to a N+1 problem.

Scope are good for simple task. One DB request in a scope will do the request for every single models. Avoid this.

If well used, when() method will help you to build complexe query that will result on a single DataBase query.

Laravel query with subqueries and foreach

1 Answers