Loading events to FullCalendar

Question

So far I've been doing an synchronous ajax call to fetch events from database and then initialize the calendar afterwards using the variable returned from the ajax (Solution 1).

I have read that doing synchronous calls are usually considered bad though since it freezes the browser so I have tried another option where put the ajax call directly inside the events array of FullCalendar calendar initialization (Solution 2). This gives a nice experience upon first load since the browser isn't locked up due to the asynchronous ajax and you can see the calendar as it builds up.

This has a downside however, every time you change view, it re renders the events giving the user a less smooth experience compared to the first one. Here are the code the two solutions that I tried so far:

Solution 1:

$(document).ready(function(){
     $.ajax({
         url: 'script.php',
         type: 'POST',
         async: false,
         success: function(response){
              json_events = response;

         }
    });
  $('#calendar').fullCalendar({
       events: JSON.parse(JSON.stringify(json_events)),
  });

});

Solution 2:

 $(document).ready(function(){
       $('#calendar').fullCalendar({

            events: {
                    url: 'script.php',
                    type: 'POST',
                    success : function(response){
                    }
           }
       });

  });

Are there any other solutions to this "problem"? Right not I like Solution 1 more since you don't have to deal with the re rendering of events as you use the calendar but it would be nice to not have the initial freeze upon loading the page.

Edit: script.php

$events = array();
$query = mysqli_query($link, "SELECT * FROM calendar");
while($fetch = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
   $e = array();
   $e['id'] = $fetch['id'];
   $e['start'] = $fetch['startdate'];
   $e['end'] = $fetch['enddate'];
   array_push($events, $e);
}
echo json_encode($events);

Would this work? (for aDyson in comments)

  events: {
        url: 'script2.php',
        type: 'POST',
        data : {
            calendar_id : calendarId
        },
        success : function(response){
          }
   }

script2.php

$calend_id = $_POST['calendar_id'];
$start = $_POST['start'];
$end = $_POST['end'];
$events = array();
$query = mysqli_query($link, "SELECT startdate,enddate,id FROM calendar WHERE calendar_id = '$calend_id' AND startdate >= '$start' AND enddate <= '$end'");
while($fetch = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
   $e = array();
   $e['id'] = $fetch['id'];
   $e['start'] = $fetch['startdate'];
   $e['end'] = $fetch['enddate'];
   array_push($events, $e);
}
echo json_encode($events);

Answer 1

FullCalendar really expects that you only load the events that are required for the current view. This means that you don't have to load all your events upfront. Think about it, once your software has been running for a few years, "all events" could be quite a large amount, and slow down the loading of the calendar. Also, you have to consider how likely it is that a user will suddenly want to view the calendar from 3 years ago? It's probably an edge case, so you don't need those events immediately.

FullCalendar provides a very neat way to do this automagically. See this link https://fullcalendar.io/docs/event_data/events_json_feed/

Basically you simply specify your PHP endpoint as the "events" URL, and as long as you comply with the structure specified in that link it will automatically download the right events when the user changes the date range and/or view being displayed.

So, in your calendar config:

events: "script.php"

As simple as that! FullCalendar will automatically pass two fields - "start" and "end" as the dates to fetch events for. So in your PHP, you'd need something like:

$events = array();
$start = $_GET["start"];
$end = $_GET["end"];
//code to build a query with a WHERE clause specifying the start/end dates
//...
//and finally echo the resulting events
echo json_encode($events);

Unless network latency is a massive issue for you, then downloading the events in small batches, only when required, is preferred the way to go. Also, by default there is a calendar option set: lazyFetching: true which tries to minimise the number of ajax calls required - see https://fullcalendar.io/docs/event_data/lazyFetching/ for more details of exactly how it works.

If you're worried about user experience while the events are loading, you can handle the "loading" callback, so you can add something like a "loading" spinner or something else to indicate to the user that they just need to wait a couple of seconds for the events to appear. See https://fullcalendar.io/docs/event_data/loading/ for more details again.

Answer 2

There are many things to optimize here so let's do it step-by-step.

First in your php script you're issuing Select * from calendar but you only use three columns to generate the json feed so why not Select id,startdate,enddate from calendar ? this(depending on calendar schema) will fetch less from the database and if you've done your indexing right, it will give a better chance for those indexes to be used.

Secondly you can do some kind of pagination, I'm pretty sure you don't want your users to see all things at once, in that case you use SQL's LIMIT and OFFSET or better a Where clause that only fetches events between two dates or both.

Now we have to differentiate between two cases :

Case 1 : we don't mind the user seeing a stale version of the events

So maybe you don't mind your users looking at a deleted event,updated event and most importantly you don't mind your users missing new events.

First thing to do is to pass cache: true inside events: {...} you're passing to fullCalendar, this will activate the browser's cache, you then need to update script.php to check for Last-modified and probably Etag headers, check the updates your table had since then and if no updates(no Insert/Delete/Update) you just send a 304 response(not modified), you can use whatever history mechanism to know what updates your table had between dates.

Second thing is to use some kind of caching mechanism on the server side that caches the database response, you can either use Redis(or memcached or any other) and build a background job which on every time interval you choose syncs the cache with the database.

Or you can make a materialized view in the database ,have it refresh on every time interval and select from that view instead, unfortunately mysql doesn't support it natively but there is Flexviews , see here : https://mariadb.com/kb/en/mariadb/flexviews/

Third thing to do is to find a way to only send those changes instead of sending all events, this is going to be hard, basically you pass events as a function instead of as a json feed to fullCalendar, then you use whatever history mechanism that supports mysql to know the updates, you send them and on the client side you find a way to interpret those changes and update some in-memory object.

If you have concern that your javascript will consume a lot of memory or simply that the events won't fit you can use IndexedDB or you can use pouchdb as it's simpler and will give you support for old browsers that don't support IndexedDB yet.

Case 2 : The User must see the events update in realtime

For mysql you need to monitor changes to calendar table someway(could be triggers for example), you then use Websockets(or any technology built upon it) to push the changes whenever they happen to the client, on the client side the onmessage callback needs to tell fullCalendar about the updates somehow, one way is to update a global array or a client-side database and pass events as a function, another way is to use fullCalendar's updateEvent(s) and removeEvent(s)(apparently no insertEvent(s) though :3 ).

If you feel adventurous you can use RethinkDB which is a realtime database specifically made for your need, or you can store your data in an event database like this one : https://geteventstore.com/

P.S. : you maybe have a mix of both cases, for example you don't care about Insert but you need delete/update to be notified about, you then mix approaches I think :) .