Sorting by date ranges

Hi everyone!

So I am building a short term rental marketplace (similar idea to Airbnb) and every time someone creates a reservation, the date range gets stored in a data field with data type “list of date ranges” as a new range. However, the issue lies in the search results page - how can I get Bubble to check a singular date range against a list of date ranges specific to one property. So for example, if I am looking for properties available in Montana from April 15 to April 25, how can I get Bubble to search my data base and display properties whose list date range DO NOT overlap with the April 15 to 25. I have figured out the overlap parameter but only when you are checking one date range vs one date range and not one date range to a list of date ranges.

Hopefully that makes sense and thanks in advance for any help or suggestions.

1 Like

Hello,
I don’t know if you are making this app for training or if it is the original app!
But, I suggest not to use range data types (date or number).
These data types have special uses and you will probably face challenges in using their data.
For this case, it is better to store separate start and end dates

1 Like

So if you have something database wise like:
Property : Address, name, etc, etc

Reservation : Date Range, or better start and end date, Property, Price, Owner, Person Booking-user, etc

Then you have a page or group with listings, where someone puts in location (Montana) and Booking Range (April 15-25)

You RG showing listings would have a source something like:
Type of Content: Properties
Data Source: Do a Search for Properties
Where Location = Montana

Then, use a filter for an advanced search.
(So it will look like : Search for Properties: Filtered)

For the Filter:
Do A Search for Bookings (Filter:Property = This Property) each Bookings Date Range Is Before Booking Range AND
Do A Search for Bookings (Filter:Property = This Property) each Bookings Date Range Is After Booking Range

This might not be exactly correct, but should be pretty close.
I tried to use “Overlaps With” but that would tell you if something DOES overlap. And you want it to NOT overlap. And I did not see an operator for that.
What I do is store the start and stop date, and then create the date range just when I need it.
So in my case, here is my advanced filter:

Screen Shot 2023-04-09 at 5.01.12 PM

1 Like

@ratsoundsystems amazing thank you so much. I was able to replicate your flow and display properties with listings that have reservations within the search query date range.

Even though this worked, I still do not know how to search for properties that DO NOT have listings that overlap with the search query dates.

I wonder if anyone else has dealt with this?

Hello again.

I remind you again, if this app is going to be scaled in the future, do not use this method (using the date range).

For time overlap, if the start and end dates are separated, you can get the answer by comparing the dates properly.
Examples of comparing filters:
“End” not empty
Start X > End Y’s

“end” is empty: in use

Like others have said this might not be the most performative way to do it.
Do 2 searches in your data source for properties.
The first is for all the properties that match (in Montana)
then do a
“Minus List”
and do the second search for Properties that have overlapping dates.

What you will be left with is a list of Properties that don’t have overlapping dates.

1 Like

@NoCodeDataArtisan Thank you! I am a little lost in following, specifically the “end” parts. I have start date and end date for each booking, but do you have screenshots of the formula on how the search results string would look?
Also, what would happen if there is one reservation from 4/11-4/15 and someone needed the property from 4/12-4/14? Would it not show?

@ratsoundsystems - I did everything you said and now no properties are showing up. I wonder what I am doing wrong. Here are some screenshots
Screen Shot 2023-04-11 at 12.02.12 PM
Screen Shot 2023-04-11 at 12.02.22 PM
Screen Shot 2023-04-11 at 12.02.32 PM

1 Like

I had assumed the end date would be recorded after completion.

So if both dates have a value from the beginning, there must be a field for status?

Yes! Once the end date has occurred and all reviews and documentation has been recorded, then the status changes to complete. But I am looking to display properties (equipment) that have not been reserved for the time period which the user is looking for. Keep in mind that one item ideally can be reserved for multiple date periods at any one time, granted those times do not overlap…

1 Like

First, what is important is how you handle the database for this process.

According to the information I have so far, the method to implement for the database for this process of the app looks like this:

Three data types are required:

  1. Something that is rented (equipment)
  2. Renter (user)
  3. Lease (This table, record each lease data, and only this table is connected to two other tables)

So far, have you followed this method?

Sorry I don’t want to lead you in the wrong direction.
But as the other posters have mentioned, it is all about how the data tables are setup.

I have an equipment tracking app largely complete, and I found it rather complex. (I won’t finish it now it would not be affordable to host in the new WF plan). I also have a crew/labor tracking app my company uses.

Here are some things to consider:

  1. Lease end complete is fine to use, but you have to also consider multiple reservations in the future, AND for the case of equipment rental, if the item is overdue (not back in the booked date range). So probably going only by Lease complete will not get you where you want to go.

You will need to check for items that have a lease reservation in the future that overlap, plus any items that have a lease that has not ended BUT due not have overlapping dates (ie: overdue)

  1. I am not sure where all this takes place, and where your customers are located, but you will need to probably manage time zones somehow. I would suggest all time be translated to the time of where the lease will occur. In our case someone in the Eastern Time Zone making a reservation for an item might create issues since we are based in the PST, as their browser will try to translate the time vs the rental location’s time zone. So for example they will make a reservation for 8am, but our location will see 5am, before we are even open.

Just some stuff to consider.

1 Like

I believe so! I just want to be clear that when you say “Only this table is connected to two other tables.” Can you rephrase and I apologize.

Overall, I am building an outdoor gear rental marketplace. One item gets rented and when that happens a booking is created. This Booking has a host, equipment, renter, start date, end date, and status among other data points. The equipment itself only has a list of unavailable dates while the Booking has the start and end date. However, the equipment is connected to bookings.

1 Like

Yes, definitely, and I appreciate all the time you have taken to help me out. I feel like your original message is the way to go temporarily as it functioned… I just can’t figure out the “Minus list” part and it just removes all results. I will keep trying, but again thank you.

1 Like

Any part that is not understood, tell me so that I can explain.

Note that, we haven’t covered the search and filter yet. :sweat_smile:

Yes, that chart is correct. Here are screenshots of the data types I have

1 Like

hey @manny.serfaty

Great, well done for the database tables. :star_struck:


recommendation:

You can add another table for features:
Feature name
Feature value
Equipment (link)


Now delete all the fields that are in the form of a list from the main tables. (I marked them on the picture)


Now we come to the first question you asked. :sweat_smile:

  • According to the date selected by the user, find the bookings that have not been completed, then select each item’s equipment.

  • Then, from the list of all equipment, subtract the above list.

Output is a list of equipment that can be rented.

Ahhh so essentially, the search results would be a list of bookings’s equipments as opposed to a list of equipments? Since the RG is currently equipment, I can’t do a search for bookings unless I format it like Ratsoundsystems said above

1 Like