Forum Documentation Showcase Pricing Learn more

Searching a date in a List of Date Ranges

Experienced bubblers - I am sure this is achievable, may be a workaround?
I have a Listing that has a list of date ranges that stores its own availability. Like {12-Feb-2019 - 16-Feb-2019, 16-mar-2019 - 20 Mar 2019, and so on …}

When someone wants to search for a listing that is available between 14-Feb-2019 and 15-Feb-2019, the above listing should come up.

Now when I run a search query - bubble does not allow me to search on date ranges! which is a known issue I suppose. How can I say to the database that fetch me the listings that have availability for dates that the user has specified in the search query? Seems very basic and simple but I don’t know how this can work given the limitation. There must be another way. I am displaying the search results in a Repeating Group on a page - fyi.
@NigelG
@boston85719

I am sure you both can answer this In a blink

Which other way can I achieve this? Snapshots below

Hi there, @kmeenakshi26… I am clearly not Nigel or Boston (and I certainly understand why you tagged them directly because they rock and can probably, like you said, answer your question in a blink), but your post was interesting to me, so I did some thinking on (and experimenting with) your problem.

I’m curious… when a listing is booked, are you by any chance saving the booking (and its associated date range) to a Bookings (or something like that) data type. The reason I ask is because I am wondering if you could do away with the availability concept on the listing itself and, instead, search a bookings-related data type to get what you need.

To elaborate, each record in the Bookings data type would be tied to a listing. When a user is searching for listings that are available during a specified date range, you could search the Bookings data type for listings (unique elements) that do not have a booking where the date range overlaps with the search criteria.

Anyway, I could be way off base here, so please feel free to ignore this response if it doesn’t sound like anything that might work for you. That being said, if it does sound interesting, I experimented with the concept enough to know that it works (well, in my simple example at least), and I would be happy to help you get down this path if you want.

Best…
Mike

Thanks Mike, I am ever so grateful that you answered. My problem scenario comes even before the booking stage, when a user just like AirBnB searches for properties available in a specific date range. the owner of the property can set the available dates and if it matches the search criteria for the person looking for a bnb, it displays the listing in the search results.
If I have understood you correctly, we can use the booking type only after a person has booked the apartment and my scenario occurs just before that.

Ah, okay… I definitely missed the mark because I was only thinking of availability in terms of dates when a listing is not booked as opposed to an owner setting the available dates… sorry about that.

Just a thought… and like my last one, it could easily be missing the mark, too (or just be a bad idea altogether), but could you have an Available data type that works the same way as the Bookings data type I described in my initial response? So, instead of a list of available date ranges on the listing, each available date range for a listing would be a separate record (tied to the listing, of course) in the Available data type. At that point, the search for available listings would work the same as the search in my other example, except it would be the exact opposite (and easier to construct, too).

Again, this might not be a good idea, and I hope I’m not wasting your (or any other reader’s) time here. Just throwing some stuff out there as food for thought to try to get you around the limitation you are facing.

Best…
Mike

I think Mike is right

You can check out this post to see some links to a website with a lot of information about database structure. They may or may not have an example database for a reservation system, however, the concept is the same as an e-commerce product availability database except instead of having simple numbers it is dates, which complicates it a bit more, but the general idea as Mike pointed out is to have the separate data type for availability.

The key for you would be to have the ‘host’ set their availability and when a booking is made, update a bookings data type but also the availability data type as well.

Yeah, lists of addresses and ranges are not searchable. But you should be able to filter availability and then use a count ?

Thanks Nigel, I am not sure I completely understand you. If you could explain me how I should do the data structure for the listing to have an availability associated with it, then I can take it from there I think. Someone suggested the below approach but that would make the Listing record have as many columns.

Thanks Mike I think what you mean is this .

So if user ever specifies 10 periods of availability, there will be then columns of available date ranges in the Listing record? This will work for me but its not very elegant and will add more columns to the Listing record. Unless I have completely misunderstood you.

Hi there, @kmeenakshi26… my apologies for not being clearer in my last response, but that is definitely not what I was suggesting, and you were being much too kind by stopping at “it’s not very elegant”. :slight_smile:

My suggestion centers around completely removing the concept of availability from the Listing data type, and instead, you would have an Available data type that looks like this…

availability

As owners enter an available date range for a specific listing, instead of making changes to the listing’s record in the Listing table, you would create a new thing in the Available table, and that thing includes the listing itself and the date range the user entered. So, if an owner enters five available date ranges for one listing, you would have five separate records in the Available table, and each one of those records would have that listing’s ID in its listing field.

Does that make more sense now?

To go a bit further (and as Boston pointed out), when a booking is made for a listing, you would create a record in the Bookings table, and you would also search through the Available table for a “conflicting” record (that is, a record for the specified listing that includes the booked dates), and you would remove that record from the Available table.

Happy to provide more details and/or screenshots if you want to go this route and need any help… so just let me know.

Best…
Mike

1 Like

Genuis! I am sure this will work and I feel embarrassed why I didn’t get this before. Thank you Mike, I am sure this will work. You are a genius!
:blush:

1 Like

And now you are being much too kind with the word genius. :slight_smile:

No need at all to feel embarrassed… my explanation in that response could have been a lot clearer. I’m happy to hear you think this suggestion will work for you, and again, if you need any help with it, just give a shout.

Best…
Mike

Putting the date ranges on a seperate table is the way to go. Easier to maintain the lists that way.

I would be more tempted to do it the other way round and have Listing with a List of AvailableDates which has the date range.

That way you don’t need to do the whole search for AvailableDates each time. That may get quite slow.

So you could then do …

So find Bookings and use an Advanced filter to count the number of available date ranges ON THAT BOOKING for your target range. If the count > 0 then there is something available.

1 Like

Thanks Nigel, I am going to try and do this. Will keep you posted

1 Like