node.js - 续集-左外部联接(Sequelize - Left outer join)

Question

The query which i wanted to generate with sequelize is:

(我想用续集生成的查询是：)

SELECT A.*,B.rating from skills A  LEFT OUTER JOIN ratings B ON A.skill_id=B.skill_id  AND B.emp_id='12345' WHERE A.role_id='developer';

ratings has a column skill_id which is a foreign key which refers to skill_id in skills table which is primary key.

(评级有一个列Skill_id，这是一个外键，它引用技能表中的skill_id作为主键。)

I tried below association:

(我尝试了以下关联：)

skillsets.hasOne(ratings,{foreignKey:'skillID',sourceKey:'skillsetID' });
ratings.belongsTo(skillsets,{foreignKey:'skillID',targetKey:'skillsetID'});

I am not sure of purpose of hasOne.

(我不确定hasOne的目的。)

Without it, only with the belongsTo statement, i got error that ratings is not associated to skills.

(没有它，仅使用belongsTo语句，我得到错误，即评分与技能无关。)

Drawback is , use of hasOne creates a new Object 'rating' and rating gets populated into that object unnecessarily.

(缺点是，使用hasOne会创建新的“评分”对象，并且不必要地将评分填充到该对象中。)

Query gets generated as:

(查询生成为：)

SELECT "skills"."skill_id" AS "skillsetID", "skills"."role_id" AS "roleID", "skills"."field", "skills"."skill_name" AS "skillName", "rating"."rating_id" AS "rating.ratingID", "rating"."rating" AS "rating.rating" FROM "skills" AS "skills" LEFT OUTER JOIN "ratings" AS "rating" ON "skills"."skill_id" = "rating"."skill_id" AND "rating"."emp_id" = '12345' WHERE "skills"."role_id" = 'developer';

Problem is with "rating"."rating" AS "rating.rating".

(问题在于“评级”。“评级”为“评级。评级”。)

Could you please help me on what's right way to do a left outer join for the same?

(您能帮我做同样的左外连接的正确方法吗？)

  ask by Gayathri translate from so

Answer 1

I presume you want all of your model A 's entries irrespective of entries having B 's attached to it or not (using the LEFT JOIN ).

(我假设您希望所有模型A的条目，无论是否附加了B的条目（使用LEFT JOIN ）。)

Sequelize equivalent query would be

(序列化等效查询将是)

A.findAll({
   where: {
       // YOUR CONDITION GOES HERE
   },
   include: [
       {
            model: B,
            require: false
       }
   ] 
}

It will grab all A model instances according to your given condition.

(它将根据您给定的条件获取所有A模型实例。)

It will also attach B model, if B 's foreign key is attached to it, otherwise null will be set.

(它也将附上B模型中，如果B的外键连接到它，否则null将被设置。)