sequelize.js - feathers-sequelize Left Join Database Postgresql
问题描述
我对 feathersjs 很陌生,我被困在使用 LEFT JOIN 的数据库查询上。我已经阅读了 sequelizejs,但我被卡住了。我有 2 张桌子。
表格1:
Table "public.ek_jabatan"
Column | Type | Modifiers
----------------+--------------------------+-----------------------------------------------------------------
id_jabatan | integer | not null default nextval('ek_jabatan_id_jabatan_seq'::regclass)
id_instansi | integer | not null
nama_jabatan | character varying(100) | not null
urutan_jabatan | integer | not null
createdAt | timestamp with time zone |
updatedAt | timestamp with time zone |
Indexes:
"ek_jabatan_pkey" PRIMARY KEY, btree (id_jabatan)
表 2:
Table "public.ek_instansi"
Column | Type | Modifiers
---------------+--------------------------+-------------------------------------------------------------------
id_instansi | integer | not null default nextval('ek_instansi_id_instansi_seq'::regclass)
nama_instansi | character varying(100) | not null
lokasi | character varying(200) | not null
createdAt | timestamp with time zone |
updatedAt | timestamp with time zone |
Indexes:
"ek_instansi_pkey" PRIMARY KEY, btree (id_instansi)
在 PHP 中进行查询并不难:
SELECT id_jabatan, a.id_instansi, nama_jabatan, urutan_jabatan, nama_instansi, lokasi
FROM ek_jabatan a
LEFT JOIN ek_instansi b
ON b.id_instansi = a.id_instansi
如何在 FeatherJS 上进行这样的查询???
我试过了,
users.model.js
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
const sequelizeClient = app.get('sequelizeClient');
const users = sequelizeClient.define('users', {
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
password: {
type: DataTypes.STRING,
allowNull: false
},
statusId: {
type: Sequelize.INTEGER,
field: 'status_id'
}
}, {
hooks: {
beforeCount(options) {
options.raw = true;
}
}
});
// eslint-disable-next-line no-unused-vars
users.associate = function(models){
users.hasOne(models.userStatus, {
as: 'status',
foreignKey: 'id'
});
};
return users;
};
user_status.model.js
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
const sequelizeClient = app.get('sequelizeClient');
const userStatus = sequelizeClient.define('user_status', {
name: {
type: DataTypes.STRING,
allowNull: true
}
}, {
hooks: {
beforeCount(options) {
options.raw = true;
}
}
});
userStatus.associate = function (models) {
};
return userStatus;
};
users.hooks.js
before: {
all: [],
find: [
authenticate('jwt'),
context => {
const sequelize = context.params.sequelize || {};
sequelize.raw = true;
sequelize.include = [
{
model: context.app.services['userStatus'].Model,
as: 'status'
}
];
return context;
},
],
get: [ authenticate('jwt') ],
create: [ hashPassword() ],
update: [ hashPassword(), authenticate('jwt') ],
patch: [ hashPassword(), authenticate('jwt') ],
remove: [ authenticate('jwt') ]
},
当 npm start 时,我得到了错误。throw new Error(this.name + '.' + Utils.lowercaseFirst(Type.toString()) + ' 调用的东西不是 Sequelize.Model 的子类');
解决方案
您没有在钩子中正确设置“context.params.sequelize”。你需要做这样的事情:
context => {
// Make sure the object exists
if(!context.params.sequelize) {
context.params.sequelize = {};
}
const sequelize = context.params.sequelize;
sequelize.raw = true;
sequelize.include = [
{
model: context.app.services['userStatus'].Model,
as: 'status',
required: false, // Will always ensure LEFT JOIN
}
];
return context;
}
推荐阅读
- apache-spark - PySpark:aggregate() 正好需要 4 个参数(给定 3 个)
- google-sheets - 在文本字符串中的任何值出现在 Google 表格中之前和之后添加文本字符串
- python - 如何以编程方式运行主/从 Locust 跑步者,以便奴隶在最后停止
- javascript - 将变量放在 React 组件中的什么位置?
- javascript - req.body.name 未定义 - Nodemailer ejs
- c# - 从 Blazor 上的串行端口读取
- spring-boot - 测试 Spring Rest 端点
- java - 在 spring mvc 中按计数排序,用于 OneToMany 变量
- git - 我可以在提出 PR 后添加提交吗?
- python - MariaDB上使用pymysql的连接错误