CODEWITHSUNDEEP
laraveleloquent
wiwa1978
wiwa1978

Reputation: 2707

Eloquent: check if date is in between start and stop date

I have an events table with 4 events all with different start and enddate.

mysql> select id, event_name, event_startdate, event_closedate from events;
+----+--------------+---------------------+---------------------------+
| id | event_name   | event_startdate     | event_closedate           |
+----+--------------+---------------------+---------------------------+
|  1 | Event 1      | 2016-05-01 00:00:00 | 2016-06-30 00:00:00       |
|  2 | Event 2      | 2016-06-01 00:00:00 | 2016-07-30 00:00:00       |
|  3 | Event 3      | 2016-07-01 00:00:00 | 2016-08-30 00:00:00       |
|  4 | Event 4      | 2016-09-01 00:00:00 | 2016-10-30 00:00:00       |
+----+--------------+---------------------+---------------------------+

I want to return the current events. In MySQL, the below query works:

mysql> select id, event_name, event_startdate, event_closedate from events where now() >= event_startdate and now() <= event_closedate;
+----+--------------+---------------------+---------------------------+
| id | event_name   | event_startdate     | event_closedate           |
+----+--------------+---------------------+---------------------------+
|  1 | Event 1      | 2016-05-01 00:00:00 | 2016-06-30 00:00:00       |
|  2 | Event 2      | 2016-06-01 00:00:00 | 2016-07-30 00:00:00       |
+----+--------------+---------------------+---------------------------+

Now, I want to create the Eloquent equivalent. I have created the following:

$current_events = DB::table('events')->select('id','event_name', 'event_startdate', 'event_closedate')->where(DB::raw('now()'), '>=', 'event_startdate')->where(DB::raw('now()'), '<=', 'event_closedate')->get();

When I do a 'dd' output in Laravel, I get:

"select `id`, `event_name`, `event_startdate`, `event_closedate` from `events` where now() >= ? and now() <= ?"

The above is pretty similar to the query in MySQL that returns the two current events, so I was expecting to see the two events in my view but nothing is being output so I suspect I'm doing something wrong

The view is: 

@foreach( $current_events as $event )
 <tr>
   <td align="center" class="hidden-xs">{{ $event->id }}</td>
   <td>{{ $event->event_name }}</td>
   <td>{{ $event->event_startdate }}</td>
   <td>{{ $event->event_closedate) }}</td>
 </tr>
@endforeach

Any reason why this does not return the two current events?

Upvotes: 2

Views: 2527

Answers (4)

Mike
Mike

Reputation: 521

Because Laravel uses Carbon dates.. this is a better solution. It also uses Eloquent models, rather than the DB class.

$current_event = Events::select( 'id','event_name', 'event_startdate', 'event_closedate' )
->where('event_startdate', '<=', Carbon::now())
->where('event_closedate', '>=', Carbon::now())->get();

Upvotes: 0

Rafael Berro
Rafael Berro

Reputation: 2601

It will be better if you use query scopes inside your eloquent model, scopes makes your code easier to understand and works better than raw queries in your case. For example, in your model you should do:

class Event extends Model
{

    ...

    public function scopeCurrent($query)
    {
        return $query->where('event_startdate', '<=', Carbon\Carbon::now())
            ->where('event_closedate', '>=', Carbon\Carbon::now());
    }

    ...

}

And anywhere in your controller:

class EventController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @param  \Illuminate\Http\Request $request
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $todayEvents = Event::current()->select('id', 'event_name',  'event_startdate', 'event_closedate')->get();

        ...
    }
}

If you're having issues with Carbon, check the docs.

Upvotes: 2

wiwa1978
wiwa1978

Reputation: 2707

The correct query to use is:

$current_events = DB::table('events')->select( 'id','event_name', 'event_startdate', 'event_closedate' )
    ->where( DB::raw('now()'), '>=', DB::raw('event_startdate') )
    ->where( DB::raw('now()'), '<=', DB::raw('event_closedate') )->get();

instead of

$current_events = DB::table('events')->select('id','event_name', 'event_startdate', 'event_closedate')
    ->where(DB::raw('now()'), '>=', 'event_startdate')
    ->where(DB::raw('now()'), '<=', 'event_closedate')->get();

Upvotes: 0

huuuk
huuuk

Reputation: 4795

Did you try

$current_events = DB::table('events')->select( 'id','event_name', 'event_startdate', 'event_closedate' )
    ->where( DB::raw('now()'), '>=', DB::raw('event_startdate') )
    ->where( DB::raw('now()'), '<=', DB::raw('event_closedate') )->get();

Upvotes: 2

Related Questions