Skip to content
Advertisement

Laravel (8.x) Is there a better Eloquent query for this many-to-many filtering problem?

What I have

movies many-to-many stars

movies many-to-many tags

studio one-to-many movies

I pass studio name, star name and/or tag name as optional query strings

What I need

  • Select all movies with tag and star and studio if all 3 are passed as params
  • Select all movies with tag and star if those 2 are passed as params
  • Select all movies with star and studio if those 2 are passed as params
  • Select all movies with tag and studio if those 2 are passed as params
  • Select all movies with tag or star or studio if any one is passed as param
  • Basically, Simplify this query

My Possibly Stuipd code

public function index(Request $request)
    {

        $movies = Movie::with('stars')->with('tags')->with('studio');

        // all matches
        // if all params are present
        if ($request->star && $request->tag && $request->studio)
            $movies->whereHas('stars', function ($query) use ($request) {
                $query->where('name', $request->star);
            })->whereHas('tags', function ($query) use ($request) {
                $query->where('name', $request->tag);
            })->whereHas('studio', function ($query) use ($request) {
                $query->where('name', $request->studio);
            });

        // star and tag with no studio
        if ($request->star && $request->tag && !$request->studio)
            $movies->whereHas('stars', function ($query) use ($request) {
                $query->where('name', $request->star);
            })->whereHas('tags', function ($query) use ($request) {
                $query->where('name', $request->tag);
            });

        // star and studio with no tag
        if ($request->star && !$request->tag && $request->studio)
            $movies->whereHas('stars', function ($query) use ($request) {
                $query->where('name', $request->star);
            })->whereHas('studio', function ($query) use ($request) {
                $query->where('name', $request->studio);
            });

        // studio and tag with no star
        if (!$request->star && $request->tag && $request->studio)
            $movies->whereHas('tags', function ($query) use ($request) {
                $query->where('name', $request->tag);
            })->whereHas('studio', function ($query) use ($request) {
                $query->where('name', $request->studio);
            });

        // any one matches
        if ($request->star || $request->tag || $request->studio)
            $movies->whereHas('stars', function ($query) use ($request) {
                $query->where('name', $request->star);
            })->orWhereHas('tags', function ($query) use ($request) {
                $query->where('name', $request->tag);
            })->orWhereHas('studio', function ($query) use ($request) {
                $query->where('name', $request->studio);
            });

        return response($movies->paginate(20));
    }

Advertisement

Answer

I think what you need is this:

public function index(Request $request)
{

    $movies = Movie::with('stars')->with('tags')->with('studio');

    // all matches
    // tag
    if ($request->tag)
        $movies->whereHas('tags', function ($query) use ($request) {
            $query->where('name', $request->tag);
        });

    // star
    if ($request->star)
        $movies->whereHas('stars', function ($query) use ($request) {
            $query->where('name', $request->star);
        });
    // studio
    if ($request->studio)
        $movies->whereHas('studio', function ($query) use ($request) {
            $query->where('name', $request->studio);
        });

    return response($movies->paginate(20));
}

This way you only filter your relation if its key is provided.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement