Getting Json Events - learnlaravel

This Calendar uses the json format to list events. If we want it to display tennisClub bookings then we our system to be able to produce events in the json format. The calendar also needs to have specific field names for our json "events" - these include id, title, start, end, and venue. The start and end fields need to use the UTC date time format.

To get our database to produce data in this format we need to create a database view. A database view is like a permanent query on the database that acts like a virtual table. You can query the view as if it were a table. Once we have this "event" view we can tie it to a model class and generate Event objects from an Event.php model class. This will make it relatively easy to manipulate in our system.

To create the database view, execute the following SQL script against the tennisClub database in HeidiSQL or your preferred database client

drop view if exists event;
create view event as
select concat(member.firstname, " ", member.surname) as title, 
Concat(DATE_FORMAT(BookingDate,'%Y-%m-%d'), "T", StartTime) as start, 
Concat(DATE_FORMAT(BookingDate,'%Y-%m-%d'), "T", EndTime) as end, 
concat("Court: " ,court.id) as venue,booking.id as id
from member, court, booking
where member.ID = booking.MemberID and court.id=booking.CourtID;

Now that you have created this database view, you can query it - execute the following SQL script

select * from event;

You should see result like the following

We now have all the fields that Fullcalendar requires and we are displaying the dates and times in the UTC time format. Now that this view has been created we need to create a model for the Event. Save the following code for the Event Model class into the /app/models folder - call it event.php

<?php 
namespace App\Models; 
use Illuminate\Database\Eloquent\Model as Model; 
class event extends Model 
{ 
    public $table = 'event'; 
    public $timestamps = false; 
    protected $casts = [ 
        'id' => 'integer', 
        'title' => 'string', 
        'start' => 'string', 
        'end' => 'string', 
        'venue' => 'string' 
    ]; 
}

To be able to use this Event class in any of our Controllers we need to add a use statement for it at the top of the Controller class. Add the following line to the top of the file app\Http\Controllers\calendarController.php along with the other use statements

use \App\Models\Event as Event;

Next add the following function to the calendarController

public function json()
{
    //$this->view->disable();
    $content = Event::all()->toJson();
    //$content=$json_encode($events);
    return response($content)->withHeaders([
            'Content-Type' => 'application/json',
            'charset' => 'UTF-8'
        ]);
}

This function will produce a list of tennisClub bookings in the Json format required by Fullcalendar. To do this it retrieves an array of Event objects as an Eloquent collection. The collection class has methods such as toJson() which allow it to return the data in a json format. Next we return a response to the User's browser but, in order for the browser to correctly recognise the data as json we must set the appropriate response headers.

To make this function work, we need an appropriate route. This route will need a name so that we can call it easily from blade views. Add the following line to routes/web.php

Route::get('/calendar/json','App\Http\Controllers\CalendarController@json')->name('calendar.json');

Now, to see this in operation, visit http://localhost:8000/calendar/json. You should see a bunch of json formatted events as follows

Finally to make our Calendar display these events modify the view /resources/calendar/display.blade.php as follows

Replace the hard-coded events listed with a call to the named route calenar.json. This will get the calendar to pull-in our events from the tennisClub database. Because all our events are in 2017, we need to set the default date on the calendar appropriately so we can see them.

Leave a Reply Cancel reply