Nicolas Decroos Nicolas Decroos - 4 months ago 29
MySQL Question

Laravel - Advanced Carbon Date Query

I'm trying to write a certain query but I'm failing with it and I'm looking for some help

Here's what I want to do


SELECT all items WHERE created_at is from before this month (July, June,...), and also select the first 3 which are created during this month


this is what I currently have. I've tried numerous times, but I can't figure out the right "WHERE" case

$offertes = DB::table('offertes')
->select('*')
->where('receiver_id', $user_id)
...
->orderby('seen')
->orderby('created_at','desc')
->get();

Answer

Something like this should work:

$time = new Carbon\Carbon('first day of this month'); // Get first day of the month.
$time->setTime(0, 0, 0); // Set time to 00:00:00.
....
->where('created_at', '<', $time)
->where(function($q) {
    $q->where('created_at', '>', $time)
      ->orderby('created_at','asc') 
      ->take(3)
})
->get;