Counting associated entries in Sequelize for multiple nested joins

Question

I have three tables in MySQL, simply represented as follows:

Buildings

id	building_name
1	Building A
2	Building B
..	....

Rooms

id	building_id	room_number
101	1	A.01
102	1	A.02
103	2	B.01
104	2	B.02
..	..	...

Tenancies

id	room_id	tenant_id
1	101	someID1
2	102	someID2
3	104	someID1

In sequelize I am trying to update a query I have which returns a list from the buildings table, so that it includes a count of the rooms associated to that building (where rooms.building_id = buildings.id), AND a count of the number of tenancies associated to that building (via tenancies.room_id = rooms.id -> rooms.building_id = buildings.id). So the join from tenancies to buildings is via the rooms table.

I have had some luck getting the roomsCount following this previous answer on StackOverflow. Where I am getting really stuck is how to include the third tenancies table so that I can simultaneously get a sum of the tenancies for rooms in that building.

The working Sequelize code at the moment looks like this:

Building.findAndCountAll({
            subQuery: false,
            where: {
                [Op.and]: [
                    //conditional variables (Some may be null if not specified in the query params)
                    building_name_condition,
                    building_operator_condition,
                    wifi_id_condition,
                    building_code_condition,
                    postcode_condition,
                    status_condition
                ]
            },
            attributes: {fieldsArray, include: [[sequelize.fn("COUNT", sequelize.col("rooms.id")), "roomsCount"]]},
            include: [{ model: Room, attributes: [] }],
            order: [sequelize.literal(`${order_condition}`)],
            limit,
            offset,
            group: ['buildings.id']
        })

I have tried a number of variations on the following to also get the tenancyCount but, I think because the joins are incorrectly done, it returns numbers which don't make much sense to me:

Building.findAndCountAll({
            subQuery: false,
            where: {
                [Op.and]: [
                    //conditional variables (Some may be null if not specified in the query params)
                    building_name_condition,
                    building_operator_condition,
                    wifi_id_condition,
                    building_code_condition,
                    postcode_condition,
                    status_condition
                ]
            },
            attributes: {fieldsArray, include: [[sequelize.fn("COUNT", sequelize.col("rooms.id")), "roomsCount"],[sequelize.fn("COUNT", sequelize.col("rooms->tenancies.id")), "tenancyCount"]]},
            include: [{ model: Room, attributes: [], required: true, include: [{ model: Tenancy, attributes: [], required: true }]}],
            order: [sequelize.literal(`${order_condition}`)],
            limit,
            offset,
            group: ['buildings.id']
        })

I know I am fundamentally getting the joins wrong but I am suffering cognitive overload in trying to work out how they should be done correctly and subsequently how that can be represented in the Sequelize format.

I would be grateful for any tips / insight in to a solution for this.

Counting associated entries in Sequelize for multiple nested joins

0 Answers0