jeudi 29 septembre 2016

Laravel complex query

I'm re-working an already coded "crafting" system that has is based off a set of database "recipes" which has up to five items and quantities (ex: item1 and item1qty). Now previously it was important to order the items in the recipe in the right order, which made validation easy as it was just looking at what the user put as item1 and item1qty etc and matching them straight across.

We'd like to now have it accept ingredients in any order. I know this can be done with query functions, but I fear I'm off base as the solution I came up with marks it as a valid recipe if the user has even part of a recipe (say a recipe contains 3 ingredients, if they enter only one of those ingredients it will validate even without the other 2 proper ingredients and amounts). My solution is also quite long, is there a way to streamline it a bit?

$recipe = DataRecipe::where(function ($query) use($request) {
        $query->where(function ($q) use($request) {
            $q->where(function($q2) use ($request) {
                $q2->where('item1', $request->item1)->where('item1qty', $request->item1qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item2', $request->item1)->where('item2qty', $request->item1qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item3', $request->item1)->where('item3qty', $request->item1qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item4', $request->item1)->where('item4qty', $request->item1qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item5', $request->item1)->where('item5qty', $request->item1qty);
            });
        })->where(function ($q) use($request) {
            $q->where(function($q2) use ($request) {
                $q2->where('item1', $request->item2)->where('item1qty', $request->item2qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item2', $request->item2)->where('item2qty', $request->item2qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item3', $request->item2)->where('item3qty', $request->item2qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item4', $request->item2)->where('item4qty', $request->item2qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item5', $request->item2)->where('item5qty', $request->item2qty);
            });
        })->where(function ($q) use($request) {
            $q->where(function($q2) use ($request) {
                $q2->where('item1', $request->item3)->where('item1qty', $request->item3qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item2', $request->item3)->where('item2qty', $request->item3qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item3', $request->item3)->where('item3qty', $request->item3qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item4', $request->item3)->where('item4qty', $request->item3qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item5', $request->item3)->where('item5qty', $request->item3qty);
            });
        })->where(function ($q) use($request) {
            $q->where(function($q2) use ($request) {
                $q2->where('item1', $request->item4)->where('item1qty', $request->item4qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item2', $request->item4)->where('item2qty', $request->item4qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item3', $request->item4)->where('item3qty', $request->item4qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item4', $request->item4)->where('item4qty', $request->item4qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item5', $request->item4)->where('item5qty', $request->item4qty);
            });
        })->where(function ($q) use($request) {
            $q->where(function($q2) use ($request) {
                $q2->where('item1', $request->item5)->where('item1qty', $request->item5qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item2', $request->item5)->where('item2qty', $request->item5qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item3', $request->item5)->where('item3qty', $request->item5qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item4', $request->item5)->where('item4qty', $request->item5qty);
            })->orWhere(function($q2) use ($request) {
                $q2->where('item5', $request->item5)->where('item5qty', $request->item5qty);
            });
        });
    })->where('tool', $request->tool)->first();



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire