Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

halotukozak's avatar

Laravel query with subqueries and foreach

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'));
    }
0 likes
0 replies

Please or to participate in this conversation.