Firebase query for date range data

Question

My database data is as follows:

{ "lambeosaurus": { "vacationDates" : "2016-12-20 - 2016-12-25", "length" : 12.5, "weight": 5000 }, "stegosaurus": { "vacationDates" : "2016-12-10 - 2016-12-20", "length" : 9, "weight" : 2500 } } 

How can I request all dinosaurs who will be absent on vacation for 2016-12-20. (that is, he must return both the lambeosaurus and the stegosaurus)? Or should I store data differently? If so, how should I store data for optimal performance? Thanks.

Answer 1

Combining the two dates does not do much to facilitate the query of the database.

If you are looking for vacation dinosaurs on a specific date, they could go on vacation anytime before that date. (If there is no policy that provides for the maximum number of days on vacation.) Therefore, the end date is probably what you want to request:

 { "lambeosaurus": { "vacationStart" : "2016-12-20", "vacationEnd" : "2016-12-25", "length" : 12.5, "weight": 5000 }, "stegosaurus": { "vacationStart" : "2016-12-10", "vacationEnd" : "2016-12-20", "length" : 9, "weight" : 2500 } } 

Any dinosaurs with vacationEnd incl. or after 2016-12-20 will be on vacation for this date if vacationStart enabled or earlier 2016-12-20 :

 function getHolidayingDinosaurs(day) { return firebase.database() .ref("dinousaurs") .orderByChild("vacationEnd") .startAt(day) .once("value") .then((snapshot) => { let holidaying = []; snapshot.forEach((child) => { let val = child.val(); if (val.vacationStart <= day) { holidaying.push(val); } }); return holidaying; }); } getHolidayingDinosaurs("2016-12-20").then((holidaying) => console.log(holidaying)); 

There is no direct alternative for further filtering on the client, since you can only query Firebase using one property, and combined dates are not particularly useful, since the start and end can be any date or before / after the request date.

In any case, a query using vacationEnd will most likely do most of the filtering on the server — unless you have a lot of dinosaurs who plan their vacations well in advance.

If the above approach leads to the fact that too much information is extracted and filtered on the client, you can make additional efforts and you can save your own mapping of resting dinosaurs, saving some additional data, structured as follows:

 "holidays": { ... "2016-12-19": { "stegosaurus": true }, "2016-12-20": { "lambeosaurus": true, "stegosaurus": true }, "2016-12-21": { "lambeosaurus": true }, ... } 

Firebase multi-user updates can be used to simplify display persistence (there are several examples with multiple locations in this answer ):

 firebase.database().ref().update.({ "dinosaurs/lambeosaurus": { "vacationStart" : "2016-12-20", "vacationEnd" : "2016-12-25", "length" : 12.5, "weight": 5000 }, "holidays/2016-12-20/lambeosaurus": true, "holidays/2016-12-21/lambeosaurus": true, "holidays/2016-12-22/lambeosaurus": true, "holidays/2016-12-23/lambeosaurus": true, "holidays/2016-12-24/lambeosaurus": true, "holidays/2016-12-25/lambeosaurus": true }); 

Then, the request for holidays/2016-12-20 returns an object with the keys for each vacation dinosaur.

Answer 2

I recommend storing data in a different way. Use one attribute for "vacationStart" and one for "vacationEnd". And also put all the dinosaurs inside the dinosaurs of the node, not the root of the node.

So, to request all the dinosaurs that will be absent on vacation in 2016-12-20, you should use the query:

 var query = firebase.database().ref('dinousaurs').orderByChild('vacationStart').equalTo('2016-12-20');