1

Consider I have an Events collection where it has startTimestamp and endTimestamp indicating when the event starts, ends respectively.

How to query in firestore to find out if the Event is live/finished/upcoming?

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
Ayyappa
  • 1,876
  • 1
  • 21
  • 41
  • You can only query for one of those statuses at a time - you can't get all three from a single query. What you're asking for isn't very complex, and I suggest you make an attempt, then post back here with the code that doesn't work the way you expect. – Doug Stevenson Oct 17 '19 at 15:54
  • I'm looking for status one at a time. I tried different combinations and mainly i'm limited by inequality operator on different fields. If i need LIVE status I need to query >startTimestamp and – Ayyappa Oct 17 '19 at 16:23
  • There's a pretty good answer but the question needs clarity. When you are defining an 'upcoming' event, what does that mean? Do you want to know about every single event from now until the end of time? Or just next weeks everts? Next months? Just the next event? Also, when you are asking about live - live according to what? So the user presses a button and shows all events where the current time is after the starting time and before the ending time? Instead of them pushing a button, do you want some other mechanism? – Jay Oct 17 '19 at 16:30
  • Upcoming status means the Event is about to start in sometime. Live means the event is currently happening. Finished means the event date is done. Here assume Event as a dev conference held at a place. – Ayyappa Oct 17 '19 at 16:43
  • I want to know which events are live (currently happening) or which events are upcoming through firestore queries. – Ayyappa Oct 17 '19 at 16:44
  • Again, that's not clear; *the Event is about to start in sometime* so say you have have a calendar that stores events and it's infinite so you could but dozens or hundreds of conferences on over the next 10 years. You want to show every upcoming event? That could be hundreds or even thousands. Same thing with finished. If you want to show every finished event, it could be thousands and as more get finished the list would expand. You need to define what upcoming means; it could be the next 5 events or the events for this year etc. – Jay Oct 17 '19 at 18:44
  • Yes want to show every upcoming events and with limit filter – Ayyappa Oct 17 '19 at 19:02
  • Firestore fully supports queries for less than and greater than as well as a range query. Three different queries will retrieve all of the events - here's a range query in Swift: `eventCollection.whereField("event_date", isGreaterThanOrEqualTo: 20191001).whereField("event_date", isLessThanOrEqualTo: 20191031).getDocuments { querySnapshot, err in` that retrieves all events in October. I don't like code in comments but it's a pretty broad question and unclear what the actual issue is. Can you include code in your question so we can see where you're stuck? What platform? – Jay Oct 17 '19 at 19:03
  • The issue is for querying all Live events happening at current time. As Firestore doesn't allow inequality operator on two different fields and can't make proper limit filter when combined with two queries mentioned in the below answer makes it unusable for bigger collections. – Ayyappa Oct 18 '19 at 02:07
  • There are solutions. However, time is dynamic and you would need some sort of trigger to make this work. e.g. while there maybe an event happing at his time point, 10 minutes from now the event has passed. Firebase is not aware of the current time (for a query situation) so it would not have a way to trigger an event. This is not a suggesting but imagine polling your database every second to see if the current time falls within any event times. Or perhaps every 5 minutes - or even set up an external CRON job coupled with a cloud function that does the heavy lifting for you. Just a thought. – Jay Oct 18 '19 at 16:17

2 Answers2

1

If both startTimestamp and endTimestamp properties exist in the database and are of type Date and not String or Number, then you can simply use a query to check if a particular date is within the bounds or not.

For example in Android, if you want to check if a particular date is within the bounds, you might think that a query like the one below will work:

eventsRef.whereGreaterThanOrEqualTo("startTimestamp", yourDate)
    .whereLessThanOrEqualTo("endTimestamp", yourDate);

But it won't. You'll get an Exception with the following message:

All where filters other than whereEqualTo() must be on the same field. But you have filters on 'startTimestamp' and 'endTimestamp'

The only solutuin you have is to create three separate queries.

Edit:

According to your comment, one query should check if your yourDate is before startTimestamp

eventsRef.whereLessThanOrEqualTo("startTimestamp", yourDate);

If it is, it means it's an upcoming event.

The second one would be to see if it's grater than the startTimestamp:

eventsRef.whereGreaterThanOrEqualTo("startTimestamp", yourDate);

Where we have two cases. One case, you perform a new (third) query to check if the data is less than endTimestamp:

eventsRef.whereLessThanOrEqualTo("endTimestamp", yourDate);

If it is, it means that the event is within the bounds, so it's a live event otherwise is grater than that which means that the event is finished.

To get that data in realtime, you should use a snapshot listener for every query.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
  • Can you please explain what you mean by three separate queries? For ex: Please consider getting Live status. – Ayyappa Oct 17 '19 at 16:25
  • Thanks for the answer. Once we perform 2 queries for Live case, how can I apply limit filter to it as the api is like this - getSubscribedEvents(limit:10, offset:0, Status.Live); – Ayyappa Oct 17 '19 at 17:19
  • To limit the result, simply use a `limit()` call and pass the exact limit you want, for example `limit(10)`. – Alex Mamo Oct 17 '19 at 17:27
  • But that may not give the expected max results count once we do intersection of those two queries. – Ayyappa Oct 17 '19 at 19:03
  • If you want the max results then you should not use any limitation, rather you should load data in smaller chunks. For that I recommend you see my answer from the following **[post](https://stackoverflow.com/questions/50741958/how-to-paginate-firestore-with-android)**. – Alex Mamo Oct 17 '19 at 19:10
  • 1
    It didn't answer the limit part but i need accept the solution stated is the only way to go with. However, i have another solution which is efficient and solves limit issue with minor structural changes for the subscribedEvents collection. Will give a try and post later. – Ayyappa Oct 18 '19 at 09:15
0

Here are the cases to handle this scenario. I'm pretty sure this is a very common problem but didn't find effective solutions for this anywhere.

Solution 1: Have all documents in a single collection called subscribedEvents As suggested Alex, We need to do for the following status.

Upcoming : currentTimestamp < startTimestamp

Finished : currentTimestamp > endTimestamp

Live : currentTimestamp > startTimestamp in 1st Query and currentTimestamp < endTimestamp in second query.

Problem : I can have lots of documents (nearly 10,000) in subscribedTimestamp and Live condition is not scalable as I can't limit the results while querying. As it needs to be intersected from the two queries, I need to query with out filters.

Solution 2: This is a bit of hack but scalable. Don't have all the documents in a single subCollection. Separate Upcoming events and put those documents in subscribedEvents/others/Upcoming collection.

When a user subscribes, If its an upcoming event, you can directly store in the subscribedEvents/others/Upcoming collection.

Rest of the documents can go directly into subscribedEvents collection.

Upcoming : Query all the documents with a limit filter from subscribedEvents/others/Upcoming collection.

Finished : currentTimestamp > endTimestamp

Live : currentTimestamp < endTimestamp

The benefit with this structure is we can apply limit filter and lots of documents don't need to be read for your query and there will be only one query required for each status.

Now this step needs additionally a cron job to make sure the upcoming events from the upcoming sub-collection are moved back to subscribedEvents.

However, if you have lesser documents, Solution 1 is the way to go. But not in my case.

Hope it helps someone where they have to scale efficiently.

Ayyappa
  • 1,876
  • 1
  • 21
  • 41