how to make if statement in the laravel query builder

Question

I have the following SQL query

$query
    ->join('cities','tickets.city_id','=','cities.id')
    ->select(
        'tickets.id',
        'tickets.biker_id',
        'tickets.picked_up',
        'tickets.delivered',
        'tickets.service_charge',
        'tickets.amount',
        'tickets.cancelled',
        'tickets.pre_order',
        'tickets.created_by',
        'tickets.created_at'
    )
    ->whereDay('tickets.created_at',Date('d'))                      
    ->orderBy('tickets.created_at','desc')
    ->get();

my aim is to set the

whereday('tickets.created_at', Date('d'))

to

whereday('tickets.created_at', Date('d', strtotime("-1 day")))

whenever the tickets.pre_order = 1

but when tickets.pre_order = 0 i will stick to the

whereday('tickets.created_at',Date('d'))

is it possible using if statement or is there any better way to solve this?

Answer 1

make it like this

  ->where(function ($query){
    $query->where('tickets.created_at', Carbon::now()->subDays()->format('d')) 
        ->where('tickets.pre_order',1); 
   })->orWhere(function ($query){ $query->where('tickets.created_at', 
       Carbon::now()->format('d')) ->where('tickets.pre_order',0); })
  ->get();

Answer 2

To subtract a day and format it, use Carbon library for DateTime in PHP (as given in comments by @spartyboy )

$query
->join('cities','tickets.city_id','=','cities.id')
->select(
  'tickets.id',
  'tickets.biker_id',
  'tickets.picked_up',
  'tickets.delivered',
  'tickets.service_charge',
  'tickets.amount',
  'tickets.cancelled',
  'tickets.pre_order',
  'tickets.created_by',
  'tickets.created_at'
)
->where(function ($query) { 
    $query
    ->where('tickets.pre_order', 0)
    ->whereDay('tickets.created_at', Date('d'));
})
->orWhere(function ($query) { 
    $query
    ->where('tickets.pre_order', 1)
    ->whereDay('tickets.created_at', Carbon::yesterday()->format('d'));
})
->orderBy('tickets.created_at','desc')
->get();

or if you want to subtract multiple days then
instead of yesterday() use now()->subDays($days_count)