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