Pulling data with 'Where' and 'Include' statements at the same time

Question

I have managed to get my include statements working with my foreign keys however when I try to add a 'where' statement to the findAll statement I am getting the below error. I have checked my foreign keys and models numerous times and I can't see any problems with them.

Do I have the syntax correct in the below API? The data in the keys is important so I don't want to have to spend more time on a workaround. I have seen other examples that have the where statement inside one of the include keys but the fields I want pulled are on the main table so I don't think this is the same situation.

Unhandled rejection Error: Include unexpected. Element has to be either a Model, an Association or an object.

API

`const sequelize = require("pg");
const { Sequelize, Op, Model, DataTypes } = require("sequelize");

exports.schoolDiveLogApproval = (req, res) => {

    try {
        const schoolID = req.params.schoolID;

        diveLog.findAll({
            include: [{
                model: diveType, as: 'diveTypeID_FK2',
            }, {
                model: diveSchool, as: 'diveSchoolID_FK',
            }, {
                model: current, as: 'currentID_FK',
            }, {
                model: visibility, as: 'visibilityID_FK',
            }, {
                model: user, as: 'userID_FK1',
            }, {
                model: diveSpot, as: 'diveSpotID_FK2'
            }],
            where: {
                [Op.and]: [
                    {diveSchoolID: schoolID},
                    {diveVerifiedBySchool: false}
                ]
            },
        })

...........`

Update

Error message

~~~text
~~~text
{
    "message": "missing FROM-clause entry for table \"diveLog\""
}

Controller API with raw SQL

```text
exports.schoolDiveLogApproval = async (req, res) => {

    try {
        const schoolID = req.params.schoolID;
        const { QueryTypes } = require('sequelize');
        await diveLog.sequelize.query(
            'SELECT * '+
            'FROM "diveLogs" '+
            'LEFT OUTER JOIN "diveTypes" AS "diveTypeID_FK2" ON "diveLogs"."diveTypeID" = "diveTypeID_FK2"."diveTypeID" ' +
            'LEFT OUTER JOIN "diveSchools" AS "diveSchoolID_FK" ON "diveLog"."diveSchoolID" = "diveSchoolID_FK"."diveSchoolID" ' +
            'LEFT OUTER JOIN "currents" AS "currentID_FK" ON "diveLog"."currentID" = "currentID_FK"."currentID" ' +
            'LEFT OUTER JOIN "visibilities" AS "visibilityID_FK" ON "diveLog"."visibilityID" = "visibilityID_FK"."visibilityID" ' +
            'LEFT OUTER JOIN "userLogins" AS "userID_FK" ON "diveLog"."userID" = "userID_FK1"."userID" ' +
            'LEFT OUTER JOIN "diveSpots" AS "diveSpotID_FK2" ON "diveLog"."diveSpotID" = "diveSpotFK2"."diveSpotID" ' +
            'WHERE "diveLogs"."diveSchoolID" = ? ' +
            'AND "diveLogs"."diveVerifiedBySchool" = ?',
            {
                type: QueryTypes.SELECT
            }
        )
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
};

All the table and column names look correct as per the pgAdmin database so I can't see where the problem could be other than the syntax.

I have gone off the way the SQL is executed in the IDE terminal so it should execute in theory. Is this more likely to be due to the way I pass the id?

Answer

With regards to the first error in your API, ensure you are requiring your models like below:

`const { diveLog, diveType, diveSchool, current, visibility, user, diveSpot } = require('../models');`

The second error message:

~~~text
{ "message": "relation \"divelogs\" does not exist" }

Indicates that the table "divelogs" cannot be found in your database, double check if the table exists, if it does not exist, run your migrations to create the table. Please note that, if enabled Sequelize will automatically make your table names plural. If you define a model called User, Sequelize will create the table as Users.

This answer was originally posted on Stack Overflow. You can find the full discussion here.