Data: an Office has many OfficeLocations, each of them has many Ratings. I need to write a query, that fetches only Offices, that have at lest one Rating. My query:

let condition = {
    include: [{
        model: OfficeLocation.unscoped(),
        attributes: [
            '"Office"."id" as "Office.id"',
            '"OfficeLocations"."id" AS "OfficeLocation.id"'
        ],
        include: [
            {
                model: Rating.unscoped(),
                attributes: [
                    '*',
                    sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"'))
                ]
            }
        ],
        group: '"Office.id", "OfficeLocation.id"',
        having: sequelize.where(
            sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"')),
            '>',
            0
        )
    }]
}

Office.findAll(condition).then(data => {
    res.send(data);
}).catch(e => {
    console.log(e);
});

But I have an error in console:

Error: Tried to select attributes using Sequelize.cast or Sequelize.fn without specifying an alias for the result, during eager loading. This means the attribute will not be added to the returned instance
at include.attributes.map.attr (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1307:17)
at Array.map (<anonymous>)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1287:52)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1355:39)

I've also tried

where: sequelize.literal('COUNT(DISTINCT(`OfficeLocations`.`Ratings`.`id`)) > 0'),

instead of attributes/group/having, but it doesn't work too.

Thanks.

Update

This SQL query works as I need:

sequelize.query(`
    SELECT
    "Office"."id" as "Office.id",
    "Office"."name",
    "Office"."website",
    "OfficeLocations"."id" AS "OfficeLocations.id",
    COUNT("OfficeLocations->Ratings"."id") as "RatingsCount"

    FROM "Companies" AS "Office"
    LEFT OUTER JOIN ( "OfficeLocations" AS "OfficeLocations"
        INNER JOIN "Ratings" AS "OfficeLocations->Ratings"
        ON "OfficeLocations"."id" = "OfficeLocations->Ratings"."OfficeLocationId"
    )
    ON "Office"."id" = "OfficeLocations"."OfficeId"

    GROUP BY "Office.id", "OfficeLocations.id"
    HAVING COUNT("OfficeLocations->Ratings"."id") > 0
`)

Except I want to fetch all data.

Data: an Office has many OfficeLocations, each of them has many Ratings. I need to write a query, that fetches only Offices, that have at lest one Rating. My query:

let condition = {
    include: [{
        model: OfficeLocation.unscoped(),
        attributes: [
            '"Office"."id" as "Office.id"',
            '"OfficeLocations"."id" AS "OfficeLocation.id"'
        ],
        include: [
            {
                model: Rating.unscoped(),
                attributes: [
                    '*',
                    sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"'))
                ]
            }
        ],
        group: '"Office.id", "OfficeLocation.id"',
        having: sequelize.where(
            sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"')),
            '>',
            0
        )
    }]
}

Office.findAll(condition).then(data => {
    res.send(data);
}).catch(e => {
    console.log(e);
});

But I have an error in console:

Error: Tried to select attributes using Sequelize.cast or Sequelize.fn without specifying an alias for the result, during eager loading. This means the attribute will not be added to the returned instance
at include.attributes.map.attr (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1307:17)
at Array.map (<anonymous>)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1287:52)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1355:39)

I've also tried

where: sequelize.literal('COUNT(DISTINCT(`OfficeLocations`.`Ratings`.`id`)) > 0'),

instead of attributes/group/having, but it doesn't work too.

Thanks.

Update

This SQL query works as I need:

sequelize.query(`
    SELECT
    "Office"."id" as "Office.id",
    "Office"."name",
    "Office"."website",
    "OfficeLocations"."id" AS "OfficeLocations.id",
    COUNT("OfficeLocations->Ratings"."id") as "RatingsCount"

    FROM "Companies" AS "Office"
    LEFT OUTER JOIN ( "OfficeLocations" AS "OfficeLocations"
        INNER JOIN "Ratings" AS "OfficeLocations->Ratings"
        ON "OfficeLocations"."id" = "OfficeLocations->Ratings"."OfficeLocationId"
    )
    ON "Office"."id" = "OfficeLocations"."OfficeId"

    GROUP BY "Office.id", "OfficeLocations.id"
    HAVING COUNT("OfficeLocations->Ratings"."id") > 0
`)

Except I want to fetch all data.

• javascript
• node.js
• sequelize.js

1 Answer 1

Please, see Model.findAll syntax for options argument.

1. group and having are properties of options object.
2. For alias of selected columns/expr (attributes option) use array: [expr, alias].
3. You can pass attributes.include and/or attributes.exclude arrays.
4. You can pass include[].attributes option for reference on attributes of the included Model.
5. Also you can use include[].required option for select between INNER AND OUTER JOIN.

Your case:

let options = {
        include: [
            {
                model: OfficeLocation,
                required: false, //false for OUTER JOIN, but I think that you can use INNER JOIN
                attributes: [
                    "id", //this is OfficeLocation.id, see 4th item above. 
                    [Sequelize.fn("COUNT", Sequelize.col('`OfficeLocations->Ratings`.`id`')), "RatingsCount"]
                ],
                include: [
                    {
                        model: UserRating,
                        attributes: [],
                        required: true
                    }
                ]
            }
        ],
        group: [
            `Office.id`,
            `OfficeLocations.id`
        ],
        having: Sequelize.where(Sequelize.fn("COUNT", Sequelize.col('`OfficeLocations->Ratings`.`id`')), ">", 0)
    };

Note that aliases generated by Sequelize may changes, so you should update it for Sequelize.col.