Sunday 12 March 2017

How can I achieve this SQL QUERY in sequelize (multiple joins and multiple AND/OR)

I've been struggling to achieve this (below SQL statement) in sequelize for a while now with no luck. I initially had to make separate sequelize queries to get the data but that just posed many limitations.

    `SELECT "Documents".* FROM "Documents"
  INNER JOIN "AccessTypes"
    ON "AccessTypes"."id" = "Documents"."accessTypeId"
  INNER JOIN "Users"
    ON "Users"."id" = "Documents"."userId"
  INNER JOIN "Departments"
    ON "Departments"."id" = "Users"."departmentId"
  WHERE
    (("AccessTypes".name != 'private'
      AND "Departments"."id" = ${req.decoded.departmentId})
    OR "Users".id = ${req.decoded.id})
      AND ("Documents"."title" ILIKE '%${searchQuery}%'
        OR "Documents"."content" ILIKE '%${searchQuery}%'`

This is as far as I got

    var dbQuery = {
    where: {
      $or: [
        {
          title: {
            $iLike: `%${searchQuery}%`
          }
        },
        {
          content: {
            $iLike: `%${searchQuery}%`
          }
        }
      ]
    },
    include: [{
      model: db.Users,
      where: { departmentId: req.decoded.departmentId }
    },
    {
      model: db.AccessTypes,
      where: { name: { $ne: 'private'}}
    }]
  };

  db.Documents.findAll(dbQuery)

I still need to fetch another set of documents based on the userId supplied. I feel the way to go will be to perform an 'Include' within an '$or' statement. however my research so far leads me to believe that's not possible.

Here are my models Access Types

export default (sequelize, DataTypes) => {
  const AccessTypes = sequelize.define('AccessTypes', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      isUnique: true
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations can be defined here
        AccessTypes.hasMany(models.Documents, {
          foreignKey: 'accessTypeId',
          onDelete: 'CASCADE'
        });
      }
    }
  });
  return AccessTypes;
};

Users

export default (sequelize, DataTypes) => {
const Users = sequelize.define('Users', {
    username: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    },
    firstname: {
      type: DataTypes.STRING,
      allowNull: false
    },
    lastname: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false,
      validate: {
        isEmail: true
      }
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    roleId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 3
    },
    departmentId: {
      type: DataTypes.INTEGER,
      allowNull: false
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations defined here
        Users.belongsTo(models.Roles, {
          onDelete: 'CASCADE',
          foreignKey: 'roleId'
        });
        Users.belongsTo(models.Departments, {
          onDelete: 'CASCADE',
          foreignKey: 'departmentId'
        });

        Users.hasMany(models.Documents, {
          as: 'documents',
          foreignKey: 'userId',
          onDelete: 'CASCADE'
        });
      }
    }, ...

Departments

export default (sequelize, DataTypes) => {
  const Departments = sequelize.define('Departments', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      isUnique: true
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations can be defined here
        Departments.hasMany(models.Users, {
          foreignKey: 'departmentId',
          onDelete: 'CASCADE'
        });
      }
    }
  });
  return Departments;
};

and Documents

export default (sequelize, DataTypes) => {
  const Documents = sequelize.define('Documents', {
    title: {
      type: DataTypes.STRING,
      allowNull: false
    },
    content: {
      type: DataTypes.TEXT,
      allowNull: false
    },
    userId: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    accessTypeId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 1
    },
    docTypeId: {
      type: DataTypes.INTEGER,
      allowNull: false
    }
  }, {
    classMethods: {
      associate: (models) => {
        // associations can be defined here
        Documents.belongsTo(models.Users, {
          foreignKey: 'userId',
          as: 'user',
          onDelete: 'CASCADE'
        });
        Documents.belongsTo(models.DocumentTypes, {
          foreignKey: 'docTypeId',
          onDelete: 'CASCADE'
        });
        Documents.belongsTo(models.AccessTypes, {
          foreignKey: 'accessTypeId',
          onDelete: 'CASCADE'
        });
      }
    }
  });
  return Documents;
};

Any Pointers Will be greatly appreciated Thanks in Advance



via OreB

No comments:

Post a Comment