Felix Maxime Felix Maxime - 3 months ago 8
MySQL Question

Reducing database query sizes in laravel

I'm looking to reduce the my query size in laravel.

My query looks something like this (I shortened it, it's about 10 times this amount of lines):

$users = User::where("interface_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("interface_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("web_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("web_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("illustration_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("illustration_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("brush_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("brush_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("typography_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("typography_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("identity_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("identity_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orWhere("vector_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', $unavailableCheck)
->orWhere("vector_art", '=', 1)->where('role', '=', 2)->where('commstatus', '=', 1)
->orderBy($orderByString, 'desc')
->paginate(1);


As you can see, it's a bit redundant.

For every art type, I'm looking to get users by the role of "2", if their commstatus is equal to "1" or "$unavailable".

At first, I tried to shorten it by not adding "role" or "commstatus" at the end of each "where" clause, and at the bottom writing another $users = $users::where("role", "=", "2"), for example, but I can't seem to be able to find the right syntax that.

Is there any way to shorten this query?

Answer

You certainly shouldn't need to duplicate where('role', '=', 2)->where('commstatus', '=', $unavailableCheck) for every single type of art, as they're effectively ANDed conditions; and consider whereIn('commstatus', [$unavailableCheck, 1]) rather than having two equality checks.

Something like:

$users = User::where('role', '=', 2)
    ->whereIn('commstatus', [$unavailableCheck, 1])
    ->where("interface_art", '=', 1)
    ->orWhere("web_art", '=', 1)
    ->orWhere("illustration_art", '=', 1)
    ->orWhere("brush_art", '=', 1)
    ->orWhere("typography_art", '=', 1)
    ->orWhere("identity_art", '=', 1)
    ->orWhere("vector_art", '=', 1)
    ->orderBy($orderByString, 'desc')
    ->paginate(1);
Comments