I am building availabilities for a tutoring company.
I have a table with each tutor’s availabilities, with a starting and an ending property. I also have a related table with all the currently reserved slots of a tutor. All of starting, ending, slots are timestamps.
I am trying to run a query that says,
For a given timestamp, bring me the tutors that are available but do not have a reserved slot at that time.
Eg. Martha is available at 4pm EST tomorrow but she has an existing student then, so she should not appear in the results.
Here is where I am at.
4pm EST tomorrow is 1636405200000.
I am doing
tutor = 'Martha' and starting <= 1636405200000 and ending >= 1636405200000 and slots.timestamp != 1636405200000
As Martha has a reserved slot at the selected time, she should not appear. However, Martha appears in the query, since she has other reserved slots. She would not appear if this was her only reserved slot. So I need a query that will exclude Martha if there is even one object in the reserved slot table with the said timestamp.
I tried
tutor = 'Martha' and starting <= 1636405200000 and ending >= 1636405200000 and not slots.timestamp = 1636405200000
and that does not work either.
I think I am looking to use something like the SQL query NOT EXISTS, to bring me objects for which the timestamp in the related table does not exist. I can get NOT IN to work, but I cannot get NOT EXISTS to work in Backendless. Alternatively there may be a way to do it by returning results in which the count for the timestamp in the related table is less than one?
Any thoughts?
Hello @Andreas_Marinopoulos
I created the following data structure:
with following data:
given timestamp = 11/09/2021 01:00:00 = 1636412400000
GET request:
/data/tutor?where=tutor_name not in (tutor[slots.calendar.start_time <= 1636412400000 and slots.calendar.end_time >= 1636412400000].tutor_name)&property=tutor_name
response:
[
{
"tutor_name": "Nelly",
"___class": "tutor"
}
]
Documentation:
Search with SubQuery
Regards,
Vladimir
Thanks!
I see you have designed it so that tutors have slots relations and slots have calendar relations. Since the calendar is essentially tutorAvailability, my schema is:
tutors have slots relations and tutors also have calendar relations.
Can the same query be accomplished with these relations or do I need to change my schema?
Hello @Andreas_Marinopoulos
We will be happy to assist you. I need to ask you a few more questions so I can understand the problem better.
tutors have slots relations then slots have calendar relations and tutors also have calendar relations - right?
Tutor from tutors table have a reserved slot from tutors.calendar, or from tutors.slots.calendar, or from both?
You can compose a request that matches your scheme, based on the request that I gave and our documentation about Search with SubQuery
The way I have it currently
tutors have slots relations: these are the current reserved slots, where tutors have customers alreadytutors have calendar relations: these are the general times when tutors are available.
So today, slots do not have calendar relations. Tutors from tutors table have a reserved slot from tutors.slots.
I am trying to replicate your logic, and this is what I think will work:
tutor not in (tutor[slots.timestamp = 1610049600000].tutor_name) and (calendar.starting <= 1610049600000 and calendar.ending >= 1610049600000)
This should exclude tutors that have a reserved slot at 1610049600000 I think?
Hello @Andreas_Marinopoulos
Please write your applicationId.
Which table are you making a query on?
In order to quickly check how your query works, you can use REST console = Console → Data → select a table → REST CONSOLE.
Regards,
Vladimir
