How to query entities where date-time-range overlaps another date-time-range

Get Help
1

Hey I am making a booking form to reserve a room, the bookings can be recurring for which I use a script to generate multiple entities in the booking database. But I need to first figure out for each of these generated instances if it overlaps with an already existing booking in that database. How can I query for overlapping date-time-ranges?
This is what I currently was trying:

const existingBookings = await fibery.executeSingleCommand({
        "command": "fibery.entity/query",
        "args":
        {
            "query":
            {
                "q/from": `${spaceName}/Room-Booking`,
                "q/select": [
                    `${spaceName}/Timeslot`,
                    {[`${spaceName}/Booking Type`]: ["enum/name"]},
                    {[`${spaceName}/Organizer`] : ["fibery/id"]}
                ],
                "q/where": [
                    "q/and",
                    [">=", [`${spaceName}/Timeslot`, "fibery/end"], "$start"],
                    ["<=", [`${spaceName}/Timeslot`, "fibery/start"], "$end"]
                ],
                "q/limit": "q/no-limit"
            },
            "params":
            {
                "$start": occurrence.Start,
                "$end": occurrence.End
            }
        }
    });

However, this does not work as the date-time-range type does not have a start or end field, according to the errors. So is there a way to do this with a query? It can be done in formulas: How to check if a [date] falls within a [date range]

2

What is the exact error?

What does your schema look like?

3

This is the error that I receive:
image

Request Database Schema:

{
    "fibery/name": "AI-Pioneers-DB/Room-Booking-Request",
    "fibery/fields": [
        {
            "fibery/name": "fibery/id",
            "fibery/type": "fibery/uuid",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/id?": true,
                "fibery/readonly?": true
            },
            "fibery/id": "5c240c70-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/public-id",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/public-id?": true,
                "fibery/readonly?": true
            },
            "fibery/id": "5c240c71-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/creation-date",
            "fibery/type": "fibery/date-time",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/creation-date?": true,
                "fibery/readonly?": true,
                "fibery/default-value": "$now"
            },
            "fibery/id": "5c240c74-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/modification-date",
            "fibery/type": "fibery/date-time",
            "fibery/meta": {
                "fibery/modification-date?": true,
                "fibery/required?": true,
                "fibery/readonly?": true,
                "fibery/default-value": "$now",
                "fibery/secured?": false
            },
            "fibery/id": "5c240c75-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/rank",
            "fibery/type": "fibery/rank",
            "fibery/meta": {
                "fibery/secured?": false
            },
            "fibery/id": "3a867c5c-2e92-41d9-b290-0b75aa6ab546",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/created-by",
            "fibery/type": "fibery/user",
            "fibery/meta": {
                "fibery/readonly?": true,
                "fibery/default-value": {
                    "fibery/id": "$my-id"
                }
            },
            "fibery/id": "5c240c76-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Attendees",
            "fibery/type": "AI-Pioneers-DB/Person",
            "fibery/meta": {
                "fibery/collection?": true,
                "fibery/relation": "9ca91ac0-a674-11ef-8429-8b37147afcd0",
                "ui/object-editor-order": 8
            },
            "fibery/id": "92592e70-a674-11ef-8429-8b37147afcd0",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Booking Period",
            "fibery/type": "fibery/date-time-range",
            "fibery/meta": {
                "ui/object-editor-order": 19,
                "fibery/secured?": true
            },
            "fibery/id": "d5c9c125-6dcb-4349-8685-d5dfc794d446",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Booking Type",
            "fibery/type": "AI-Pioneers-DB/Booking Type_AI-Pioneers-DB/Room-Booking-Request",
            "fibery/meta": {
                "ui/object-editor-order": 7,
                "fibery/type-component?": true,
                "fibery/relation": "2c56d8a0-a5fe-11ef-9a64-6bdc59276d75",
                "fibery/required?": false,
                "fibery/entity-of-field-type-can-be-created-by-creator-only?": true
            },
            "fibery/id": "852b29b7-6664-47a3-9eef-92c5d968f690",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Description",
            "fibery/type": "Collaboration~Documents/Document",
            "fibery/meta": {
                "fibery/entity-component?": true,
                "ui/object-editor-order": null,
                "fibery/required?": true
            },
            "fibery/id": "5c240c73-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Duration",
            "fibery/type": "fibery/decimal",
            "fibery/meta": {
                "ui/object-editor-order": 2,
                "ui/number-format": "Number",
                "ui/number-currency-code": null,
                "ui/number-thousand-separator?": false,
                "ui/number-unit": "h",
                "ui/number-precision": 1,
                "fibery/secured?": true
            },
            "fibery/id": "a5ccb79f-4e05-4767-976c-d27c7ecdf51f",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Name",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "fibery/secured?": true,
                "ui/title?": true
            },
            "fibery/id": "5c240c72-a5fd-11ef-9a64-6bdc59276d75",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Recurrence",
            "fibery/type": "AI-Pioneers-DB/Recurrence_AI-Pioneers-DB/Room-Booking-Request",
            "fibery/meta": {
                "ui/object-editor-order": 9,
                "fibery/type-component?": true,
                "fibery/relation": "f4d40ed0-a674-11ef-8429-8b37147afcd0",
                "fibery/required?": false,
                "fibery/default-value": {
                    "fibery/id": "f62aec40-a674-11ef-8429-8b37147afcd0"
                }
            },
            "fibery/id": "c5dfb662-b7fd-46f8-bcdc-410eb014a167",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Recurrence Day",
            "fibery/type": "AI-Pioneers-DB/Recurrence Day_AI-Pioneers-DB/Room-Booking-Request",
            "fibery/meta": {
                "fibery/collection?": true,
                "ui/object-editor-order": 11,
                "fibery/type-component?": true,
                "fibery/relation": "0dfeb4d0-a677-11ef-8429-8b37147afcd0",
                "fibery/entity-of-field-type-can-be-created-by-creator-only?": true
            },
            "fibery/id": "4db12e6c-4342-43a3-9e83-112d8628ebff",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Recurrence Number",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "ui/object-editor-order": 18,
                "fibery/secured?": true
            },
            "fibery/id": "dba56a12-0660-44ec-a8ef-f5ace09b5310",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Submitter",
            "fibery/type": "AI-Pioneers-DB/Person",
            "fibery/meta": {
                "fibery/relation": "a55129d0-a67c-11ef-8429-8b37147afcd0",
                "ui/object-editor-order": 16
            },
            "fibery/id": "9b3e94a0-a67c-11ef-8429-8b37147afcd0",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Submitter Academy",
            "fibery/type": "AI-Pioneers-DB/Submitter Academy_AI-Pioneers-DB/Room-Booking-Request",
            "fibery/meta": {
                "ui/object-editor-order": 6,
                "fibery/type-component?": true,
                "fibery/relation": "08a6cc81-a5fe-11ef-9a64-6bdc59276d75",
                "fibery/required?": false
            },
            "fibery/id": "5168650a-b6c8-425b-93ee-ce6da31d7db5",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Submitter Email",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "ui/type": "email",
                "ui/object-editor-order": 5,
                "fibery/secured?": true
            },
            "fibery/id": "34d27fee-2613-4adf-acd5-dfcda4750c54",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Submitter Lastname",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "ui/object-editor-order": 4,
                "fibery/secured?": true
            },
            "fibery/id": "19636dc2-61c5-4380-922a-4cc479e325de",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Submitter Name",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "ui/object-editor-order": 3,
                "fibery/secured?": true
            },
            "fibery/id": "b007d698-1b68-47ca-9198-ae6c697dd51a",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "Collaboration~Documents/References",
            "fibery/type": "Collaboration~Documents/Reference",
            "fibery/meta": {
                "fibery/collection?": true,
                "fibery/entity-component?": true
            },
            "fibery/id": "04f52294-0708-4b05-917a-80051720dee7",
            "fibery/deleted?": false
        }
    ],
    "fibery/meta": {
        "fibery/primitive?": false,
        "fibery/domain?": true,
        "fibery/secured?": true,
        "ui/color": "#673db6",
        "app/mixins": {
            "fibery/rank-mixin": true,
            "Collaboration~Documents/ReferencesMixin": true
        }
    },
    "fibery/id": "c081e589-4077-4208-8121-59187076c425",
    "fibery/deleted?": false
}

Bookings Database Schema:

{
    "fibery/name": "AI-Pioneers-DB/Room-Booking",
    "fibery/fields": [
        {
            "fibery/name": "fibery/id",
            "fibery/type": "fibery/uuid",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/id?": true,
                "fibery/readonly?": true
            },
            "fibery/id": "f68ba6b0-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/public-id",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/public-id?": true,
                "fibery/readonly?": true
            },
            "fibery/id": "f68ba6b1-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/creation-date",
            "fibery/type": "fibery/date-time",
            "fibery/meta": {
                "fibery/secured?": false,
                "fibery/creation-date?": true,
                "fibery/readonly?": true,
                "fibery/default-value": "$now"
            },
            "fibery/id": "f68ba6b4-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/modification-date",
            "fibery/type": "fibery/date-time",
            "fibery/meta": {
                "fibery/modification-date?": true,
                "fibery/required?": true,
                "fibery/readonly?": true,
                "fibery/default-value": "$now",
                "fibery/secured?": false
            },
            "fibery/id": "f68ba6b5-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/rank",
            "fibery/type": "fibery/rank",
            "fibery/meta": {
                "fibery/secured?": false
            },
            "fibery/id": "9f366b0e-eabf-47cd-96bb-3c6f8ac9d5f7",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "fibery/created-by",
            "fibery/type": "fibery/user",
            "fibery/meta": {
                "fibery/readonly?": true,
                "fibery/default-value": {
                    "fibery/id": "$my-id"
                }
            },
            "fibery/id": "f68ba6b6-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Booking Type",
            "fibery/type": "AI-Pioneers-DB/Booking Type_AI-Pioneers-DB/Room-Booking",
            "fibery/meta": {
                "ui/object-editor-order": 4,
                "fibery/type-component?": true,
                "fibery/relation": "48648f00-a67c-11ef-9df2-23ccd47acd19",
                "fibery/required?": false,
                "fibery/entity-of-field-type-can-be-created-by-creator-only?": true
            },
            "fibery/id": "51ccc484-ac14-4ddc-a2f3-cd1091ec671b",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Description",
            "fibery/type": "Collaboration~Documents/Document",
            "fibery/meta": {
                "fibery/entity-component?": true,
                "ui/object-editor-order": null,
                "fibery/required?": true
            },
            "fibery/id": "f68ba6b3-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Name",
            "fibery/type": "fibery/text",
            "fibery/meta": {
                "fibery/secured?": true,
                "ui/title?": true
            },
            "fibery/id": "f68ba6b2-a670-11ef-826b-ef293927c4d2",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Organizer",
            "fibery/type": "AI-Pioneers-DB/Person",
            "fibery/meta": {
                "fibery/relation": "c5916e20-a67d-11ef-9df2-23ccd47acd19",
                "ui/object-editor-order": 5
            },
            "fibery/id": "bf2c9eb1-a67d-11ef-9df2-23ccd47acd19",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Organizer Fibery Account",
            "fibery/type": "fibery/user",
            "fibery/meta": {
                "ui/object-editor-order": 7,
                "formula/lookup?": true,
                "fibery/readonly?": true,
                "formula/formula?": true,
                "history/disabled?": false,
                "formula/formula": {
                    "expression": [
                        "bf2c9eb1-a67d-11ef-9df2-23ccd47acd19",
                        "cfa5fa71-9dfe-11ef-9abd-85de953df2f1",
                        "4e42fa13-cd1c-482f-a47b-5309fe9f1daa"
                    ],
                    "params": {}
                }
            },
            "fibery/id": "2a4f7900-ab66-11ef-b97f-6359120db022",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Status",
            "fibery/type": "AI-Pioneers-DB/Status_AI-Pioneers-DB/Room-Booking",
            "fibery/meta": {
                "ui/object-editor-order": 6,
                "fibery/type-component?": true,
                "fibery/relation": "9e3b8a70-a74c-11ef-b23c-eda4822a2231",
                "fibery/required?": false
            },
            "fibery/id": "65ddf801-f1a7-4235-82c2-d3154c19f0b9",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "AI-Pioneers-DB/Timeslot",
            "fibery/type": "fibery/date-time-range",
            "fibery/meta": {
                "ui/object-editor-order": 1,
                "fibery/secured?": true
            },
            "fibery/id": "5c43db16-2184-4265-b387-a5682e10db42",
            "fibery/deleted?": false
        },
        {
            "fibery/name": "Collaboration~Documents/References",
            "fibery/type": "Collaboration~Documents/Reference",
            "fibery/meta": {
                "fibery/collection?": true,
                "fibery/entity-component?": true
            },
            "fibery/id": "b7501a7d-df94-4e8c-b045-75ab7d9e2462",
            "fibery/deleted?": false
        }
    ],
    "fibery/meta": {
        "fibery/primitive?": false,
        "fibery/domain?": true,
        "fibery/secured?": true,
        "ui/color": "#673db6",
        "app/mixins": {
            "fibery/rank-mixin": true,
            "Collaboration~Documents/ReferencesMixin": true
        }
    },
    "fibery/id": "51785876-7eb5-4560-b304-e7a870377173",
    "fibery/deleted?": false
}
4

Where is
occurrence.Start
coming from?

5

It is from the rest of the script that is generating the occurrences for a recurring booking. It is one instance from an array of Javascript objects that each look like:

{ Start: <Date>, End: <Date>}
6

When using a filter to compare the start/end date of a range, I think you need to use this syntax:

[">=", ["q/end", [`${spaceName}/Timeslot`]], "$start"],
["<=", ["q/start", [`${spaceName}/Timeslot`]], "$end"]
2 Likes
7

Thank you, this worked :slight_smile: