const trails = sequelize.define(
'trails',
{
userId: {
type: DataTypes.INTEGER,
allowNull: false,
},
locationId: {
type: DataTypes.INTEGER,
allowNull: false,
},
categoryId: {
type: DataTypes.INTEGER,
allowNull: false,
},
imageId: {
type: DataTypes.INTEGER,
allowNull: false,
},
title: {
type: DataTypes.STRING,
allowNull: false,
},
review: {
type: DataTypes.STRING,
allowNull: false,
},
adminDistrict: {
type: DataTypes.STRING,
allowNull: false,
},
},
);
trails.associate = function (models) {
models.trails.hasMany(models.comments);
trails.belongsTo(models.locations, {
foreignKey: 'locationId',
});
trails.belongsTo(models.users, {
foreignKey: 'userId',
});
trails.belongsTo(models.categories, {
foreignKey: 'categoryId',
});
trails.belongsTo(models.images, {
foreignKey: 'imageId',
});
};
Sequelize에서 JOIN하는 방법에 대해 먼저 조사해 보았다.(DB: MySQL)
결과부터 말하자면 아래의 옵션을 JOIN할 때 include 내 required로 설정 할 수 있다.
LEFT OUTER JOIN은 아래 소스코드와 같이 include의 옵션으로 'required: false' 를 주면 된다.
const checkTrailsByTag = await trails.findAll({
where: {
categoryId: checkTag.id,
},
include: [
{
model: users,
required: false,
attributes: ['username'],
},
{
model: locations,
required: false,
attributes: ['location1', 'location2', 'location3', 'location4', 'location5'],
},
],
raw: true,
})
SQL
SELECT `trails`.`id`, `trails`.`userId`, `trails`.`locationId`, `trails`.`categoryId`, `trails`.`imageId`, `trails`.`title`, `trails`.`review`, `trails`.`adminDistrict`, `trails`.`createdAt`, `trails`.`updatedAt`, `user`.`username` AS `user.username`, `location`.`location1` AS `location.location1`, `location`.`location2` AS `location.location2`, `location`.`location3` AS `location.location3`, `location`.`location4` AS `location.location4`, `location`.`location5` AS `location.location5` FROM `trails` AS `trails` LEFT OUTER JOIN `users` AS `user` ON `trails`.`userId` = `user`.`id` LEFT OUTER JOIN `locations` AS `location` ON `trails`.`locationId` = `location`.`id` WHERE `trails`.`categoryId` = 1;
[
{
"id": 1,
"userId": 1,
"locationId": 1,
"categoryId": 1,
"imageId": 1,
"title": "test",
"review": "test",
"adminDistrict": "yokohama",
"createdAt": "2020-01-19T04:05:52.000Z",
"updatedAt": "2020-01-19T04:05:52.000Z",
"user.username": "1",
"location.location1": "[1,1]",
"location.location2": "[2,2]",
"location.location3": "[3,3]",
"location.location4": "[4,4]",
"location.location5": "[5,5]"
}
]