Ad

Laravel 5.2 Eloquent. Query Is Not Working Well

- 1 answer

Please help. I have query where I need to search with price - currency ($cur) in DB I have daily_price, hourly_price and currency (GEL, USD). I have written this Eloquent query:

if($request->input('currency') == 'USD'){
        $query->where(function ($qu) use ($request) {
            $qu->where('currency', 'USD')->where(function($q) use ($request){
                $q->whereBetween('daily_price', [$request->input('priceFrom'), $request->input('priceTo')]);
                $q->orWhereBetween('hourly_price', [$request->input('priceFrom'), $request->input('priceTo')]);
            });
        })->orWhere(function ($qu) use ($request, $cur) {
            $qu->where('currency', 'GEL')->where(function($q) use ($request, $cur){
                $q->whereBetween('daily_price', [((int)$request->input('priceFrom'))*$cur, ((int)$request->input('priceTo'))*$cur]);
                $q->orWhereBetween('hourly_price', [((int)$request->input('priceFrom'))*$cur, ((int)$request->input('priceTo'))*$cur]);
            });
        });
    }else{
        $query->where(function ($qu) use ($request) {
            $qu->where('currency', 'GEL')->where(function($q) use ($request){
                $q->whereBetween('daily_price', [$request->input('priceFrom'), $request->input('priceTo')]);
                $q->orWhereBetween('hourly_price', [$request->input('priceFrom'), $request->input('priceTo')]);
            });
        })->orWhere(function ($qu) use ($request, $cur) {
            $qu->where('currency', 'USD')->where(function($q) use ($request, $cur){
                $q->whereBetween('daily_price', [((int)$request->input('priceFrom'))/$cur, ((int)$request->input('priceTo'))/$cur]);
                $q->orWhereBetween('hourly_price', [((int)$request->input('priceFrom'))/$cur, ((int)$request->input('priceTo'))/$cur]);
            });
        });
    }

the code starts with this query: $query->whereIn('city_id', $arr);

I don't have anything on city_id = 5 in my DB, but when I add my currency query it gets me some (I think random) result of rows... Please help.

Ad

Answer

I have found solution.

if($request->input('currency') == 'USD'){
        $query->where(function ($que) use ($request, $cur) {
            $que->where('currency', 'USD')->where(function($q) use ($request){
                $q->whereBetween('daily_price', [$request->input('priceFrom'), $request->input('priceTo')])
                    ->where('daily_price', '>', 0);
                $q->orWhereBetween('hourly_price', [$request->input('priceFrom'), $request->input('priceTo')])
                    ->where('hourly_price', '>', 0);
            });

            $que->orWhere('currency', 'GEL')->where('daily_price', '>', 0)->where('hourly_price', '>', 0)->where(function($q) use ($request, $cur){
                $q->whereBetween('daily_price', [((int)$request->input('priceFrom'))*$cur, ((int)$request->input('priceTo'))*$cur])
                    ->where('daily_price', '>', 0);;
                $q->orWhereBetween('hourly_price', [((int)$request->input('priceFrom'))*$cur, ((int)$request->input('priceTo'))*$cur])
                    ->where('hourly_price', '>', 0);
            });
        });
    }else{
        $query->where(function ($quer) use ($request, $cur) {
            $quer->where(function ($que) use ($request, $cur){

                $que->where('currency', 'GEL')->where(function($q) use ($request){
                    $q->whereBetween('daily_price', [$request->input('priceFrom'), $request->input('priceTo')])
                        ->where('daily_price', '>', 0);
                    $q->orWhereBetween('hourly_price', [$request->input('priceFrom'), $request->input('priceTo')])
                        ->where('hourly_price', '>', 0);

                });

                $que->orWhere('currency', 'USD')->where(function($q) use ($request, $cur){
                    $q->whereBetween('daily_price', [((int)$request->input('priceFrom'))/$cur, ((int)$request->input('priceTo'))/$cur])
                        ->where('daily_price', '>', 0);
                    $q->orWhereBetween('hourly_price', [((int)$request->input('priceFrom'))/$cur, ((int)$request->input('priceTo'))/$cur])
                        ->where('hourly_price', '>', 0);
                });

            });
        });
    }
Ad
source: stackoverflow.com
Ad