Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

无外键关联的表 如何做关联查询 #64

Open
akaili opened this issue Mar 9, 2021 · 1 comment
Open

无外键关联的表 如何做关联查询 #64

akaili opened this issue Mar 9, 2021 · 1 comment

Comments

@akaili
Copy link

akaili commented Mar 9, 2021

当前使用的 Node.js 版本 (node -v)
14.16.0

当前使用的 sequelize 版本
"sequelize": "^6.5.0"

问题描述

我现在有两个模型, 它们之间现在没有关联关系,如何才能实现 两个模型的 非等值连接查询

SELECT
e.salary,
g.grade_level
FROM
employees e
JOIN job_grades g ON e.salary BETWEEN g.lowest_sal
AND g.lowest_sal;

代码片段

class employees extends Model {}
employees.init(
	{
		employee_id: {
			type: DataTypes.INTEGER(6),
			allowNull: false,
			autoIncrement: true,
			primaryKey: true
		},
		first_name: {
			type: DataTypes.STRING(20)
		},
		last_name: {
			type: DataTypes.STRING(25)
		},
		email: {
			type: DataTypes.STRING(25)
		},
		phone_number: {
			type: DataTypes.STRING(20)
		},
	
		salary: {
			type: DataTypes.DOUBLE(10, 2)
		},
		commission_pct: {
			type: DataTypes.DOUBLE(4, 2)
		},
		manager_id: {
			type: DataTypes.INTEGER(6)
		},
		hiredate: {
			type: DataTypes.DATE
		}
	},
	{
		sequelize: db,
		modelName: "employees",
		
	}
)
class job_grades extends Model {}
job_grades.init(
	{
		grade_level: {
			type: DataTypes.STRING(3),
			unique:true
		},
		lowest_sal: {
			type: DataTypes.INTEGER(11)
		},
		highest_sal: {
			type: DataTypes.INTEGER(11)
		}
	},
	{
		sequelize: db,
		modelName: "job_grade"
	}
)
 job_grades.removeAttribute('id')

@akaili akaili added the question label Mar 9, 2021
@lfb
Copy link

lfb commented Jul 19, 2021

有很多方法:
方法一:在查询时候做关联

include: [{
    association: Comment.hasMany(Reply, {
        foreignKey: 'comment_id',
        sourceKey: 'id',
    }),
    as: 'reply',
    // required: false,
    attributes: {
      exclude: ['comment_id','commentId' ,'updated_at', 'deleted_at']
    }
  }]

方法二: 统一用 Op.in查询,再做匹配拼接

方法三:自己写query left join 方式

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants