CODEWITHSUNDEEP
phpsqllaravel
Mohammed Riyadh
Mohammed Riyadh

Reputation: 1019

Laravel orWhere query is not working properly

I have the following query which is working fine and get the required results

SELECT * FROM `general_ledger` where from_province = 0 and created_at BETWEEN '2020-06-01' and '2020-08-01' or to_province = 0

Which I need to select rows where the columns from_province and to_province match the query parameter province

In Laravel i used the following :

public function ProvinceOrdersSummery($province,$from_date,$to_date){

        $orders = DB::table('general_ledger as g')
                  ->join('regions as f','g.from_province','=','f.province')
                  ->join('regions as t','g.to_province','=','t.province')
                  ->where('g.from_province',$province)
                  ->whereBetween('g.created_at',[$from_date,$to_date])
                  ->orwhere('g.to_province',$province)
                  ->select('g.order_bar','g.price','g.delivery_cost','g.type','f.ar_name as from_province','t.ar_name as to_province','visual_status','g.created_at')
                  ->get();
        return response()->json(['status_code'=>2000,'data'=>$orders , 'message'=>''],200);

    }

But its returns only the rows when the to_province match the province parameter

What is wrong here ?

Any help will be much appreciated

Upvotes: 1

Views: 330

Answers (2)

Diogo Gomes
Diogo Gomes

Reputation: 2265

You can see the actual query that is going to be run with the toSql() method.

DB::table('general_ledger as g')
        ->join('regions as f','g.from_province','=','f.province')
        ->join('regions as t','g.to_province','=','t.province')
        ->where('g.from_province',$province)
        ->whereBetween('g.created_at',[$from_date,$to_date])
        ->orwhere('g.to_province',$province)
        ->select('g.order_bar','g.price','g.delivery_cost','g.type','f.ar_name as from_province','t.ar_name as to_province','visual_status','g.created_at')
        ->toSql();

I think you want your query to be like this:

SELECT *
FROM `general_ledger`
WHERE created_at BETWEEN '2020-06-01' and '2020-08-01'
AND( from_province = 0 or to_province = 0)

So in Laravel you can group the AND query:

DB::table('general_ledger as g')
        ->join('regions as f','g.from_province','=','f.province')
        ->join('regions as t','g.to_province','=','t.province')
        ->whereBetween('g.created_at',[$from_date,$to_date])
        ->where(function(\Illuminate\Database\Query\Builder $q) use ($province){
            $q->where('g.from_province',$province);
            $q->orWhere('g.to_province',$province);
        })
        ->select('g.order_bar','g.price','g.delivery_cost','g.type','f.ar_name as from_province','t.ar_name as to_province','visual_status','g.created_at')
        ->get()

Upvotes: 3

Akhtar Munir
Akhtar Munir

Reputation: 1769

Try this way, it will give priority to the query which will execute at precedence ()

$orders = DB::table('general_ledger as g')
          ->join('regions as f','g.from_province','=','f.province')
          ->join('regions as t','g.to_province','=','t.province')
          ->where(function($query) use ($province) {
               $query->where('g.from_province',$province)->orWhere('g.to_province',$province);
          })
          ->whereBetween('g.created_at',[$from_date,$to_date])
          ->select('g.order_bar','g.price','g.delivery_cost','g.type','f.ar_name as from_province','t.ar_name as to_province','visual_status','g.created_at')
          ->get();
    return response()->json(['status_code'=>2000,'data'=>$orders , 'message'=>''],200);

Upvotes: 0

Related Questions