I am trying to create the database to allow for rooms rentals. People would be able to post the room availability and others would be able to book it off.
The way I am intending to do this to get around the no-joins (and I am up for suggestions) is as follows:
A Room table will have have the room details. In addition it will have 2 columns (Available times, booked times). Column 1 will reference another table (1:N relation) that has all the availabilities (say Jan1st at 1pm till Jan 10th at 10 pm, Feb 2nd at 8am till Feb 5th at 9pm and so on). Also it will have column 2 which reference another table that has all the occupied date/timeslots like the format before). The Room table has a GEO column offcourse
My question is, is the a way to do a search with condition on the Room table and the 2 related tables.
For example,
Select all the rooms that are within 10km from my location where the current date is within column 1 dates AND current date is NOT within column 2 dates.
If so do you have a sample syntax for how to condition the query on the Room table and its referenced tables
From the documentation, I was able to understand how to do condition on a table but not on its related tables
I hope my question makes sense. Again I am open for a different schema but thats the best I can think of
I couldn’t find a way to edit the question. I miss worded it. I know how to do a query on the Room Table and its referenced tables. But my issue is that it returns the intended Room row and ALL the relations. What I really want is to return the intended Room row and ONLY the relations that match the condition ( not all the rows in the related tables). Makes sense?
So for example: The room table has column 1 who is 1:N relation with the AvailableTimes table. Lets say that Row A is in the Room table. Row A has column 1 relates to Row A1, Row A2, and Row A3 in Available Times.
Now I do a query which applies to Row A and Row A1 only. The current returned result is Row A along with all the Relations Row A1, A2, and A3. I want only Row A and the relation A1 to be returned as this is where the conditions apply to.
Hmmm
Thats very interesting. I can almost see your point. But how does this structure handle the fact that the availability is not one block of time?
Meaning the room is advertised to be available from Jan 1st till Jan 6
And available from Feb 1st to Feb 6th only
See the availability is not one block of days. It can be multiple blocks that have different from and to dates.
Hi Vladimir, I see where you are going with this. The issue remaining here is that you have put the
availableFrom DATE_TIME
availableTo DATE_TIME
bookedFrom DATE_TIME
bookedTo DATE_TIME
All in one table. I n my initial design they are in different tables and there was a reason for this.
The Advertiser my add the following availabilities:
-Jan 1st till Jan 6
-Feb 1st to Feb 6th
Which I understand are to be in different rows according to the model you suggested.
But a renter may book Jan 2nd to Jan 3rd and another renter may book Jan 4th to Jan 5th.
See my point ? This is why I was wondering how can grab the rooms that are available and NOT booked for a certain day.
yeah, I understand you, but unfortunately I have no a quick solution for your needs.
Answering to your the main question in the topic I can say that’s impossible to search by sub-relations and have these relations filtered by your condition, but you do that in Business Logic in your custom ApiService
So, you may create an ApiService where you will find Row A with relations A1, A2, and A3
then remove unnecessary items and leave only A1 and return the result to client
