How do you query based on conditions that apply to root table and its referenced tables


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

Thank you


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.

How can I achieve this?

Hi SnakeEyes

what if you will have something like this:


  • location GEO_REF 1:1 GEO_POINT


  • room DATA_REF 1:1 Room
  • availableFrom DATE_TIME
  • availableTo DATE_TIME
  • bookedFrom DATE_TIME
  • bookedTo DATE_TIME

and then you can find

  • all Room filtered by dateTime range and location
(availableFrom > 11/17/2017 AND availableTo > 11/22/2017) AND (distance( 12.21212, -43.3333, room.location.latitude, room.location.longitude ) < km(10))
  • all Orders on the Room
  room.objectId = 'XXXX-XXXX-XXXXX-XXXXX'</img></img></img></img>

Regards, Vlad

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.

yes, that’s right

it will be two records in Orders table

and you will able to find one of these or both Orders according to your whereClause

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