Sequelize.js使用hasMany两次联接表 [英] Sequelize.js join table twice using hasMany

问题描述

我正在使用Sequelize.js在同一张表上进行双重联接.我有一组团队对象和一组游戏对象.一个团队有很多游戏，因此它在游戏表中会有外键，但是每个游戏中都有两个团队，因此我需要两次加入表.使用sequelize ORM进行此操作的最佳方法是什么.

I am using Sequelize.js to do a double join on the same table. I have a set of Team objects and a set of Game objects. A team hasMany Games, so it would have foreign keys in the game table, but there are two teams in every game so i need to join the table twice. What is the best way to do this using the sequelize ORM.

Team = sequelize.define('teams',{
  name : Sequelize.STRING,
  location : Sequelize.STRING,
});

Game = sequelize.define('games',{
  homeTeamId : Sequelize.INTEGER,
  awayTeamId : Sequelize.INTEGER,
  location : Sequelize.STRING,
});

// Associations
Game.hasOne(Team, {foreignKey : 'homeTeamId'});
    .hasOne(Team, {foreignKey : 'awayTeamId'});

Team.hasMany(Game);

谢谢！

推荐答案

这实际上是一个有趣的问题.目前尚不支持此功能，但绝对可行.我在下面概述了两种方法:

That is an interresting question actually. This is not supported in sequelize at the moment, but it is definitely do-able. I've outlined two ways below:

var Sequelize = require('./index');

var sequelize = new Sequelize('sequelize_test', 'root', null, {
  host: "127.0.0.1",
  port: 3306
});

var Team = sequelize.define('teams',{
  name : Sequelize.STRING,
  location : Sequelize.STRING
}, {
  instanceMethods: {
    getGamesOne: function () {
      var chainer = new Sequelize.Utils.QueryChainer();

      chainer.add(this.getHomeGames());
      chainer.add(this.getAwayGames());

      return new Sequelize.Utils.CustomEventEmitter(function (emitter) {
        chainer.run().done(function (err, results) {
          var home = results[0],
            away = results[1];

          if (err) emitter.emit('error', err)
          else emitter.emit('success', home.concat(away));
        });
      }).run();      
    },

    getGamesTwo: function () {
      return Game.findAll({ where: ["homeTeamId = ? OR awayTeamId = ?", this.id, this.id ]})
    }
  }
});

var Game = sequelize.define('games',{
  homeTeamId : Sequelize.INTEGER,
  awayTeamId : Sequelize.INTEGER,
  location : Sequelize.STRING
});

// Associations
Game.belongsTo(Team, {foreignKey : 'homeTeamId'})
    .belongsTo(Team, {foreignKey : 'awayTeamId'});

Team.hasMany(Game, { as: 'AwayGames', foreignKey : 'awayTeamId'});
Team.hasMany(Game, { as: 'HomeGames', foreignKey : 'homeTeamId'});

Team.find(1).done(function (err, team) {
  team.getGamesOne().done(function(err, games) {
    console.log(games);
  });
  team.getGamesTwo().done(function(err, games) {
    console.log(games);
  });
})

GetGamesOne使用sequelize的关联来分别获取主场比赛和客场比赛，并在将其归还给您之前加入. GetGamesTwo手动构建查询，因此您只需要与数据库对话一次.选择您喜欢的任何一个-可能不太清楚发生了什么，但是您正在使用sequelize构建查询，因此即使以后更改键名仍然可以使用.两个非常简短明了，但是您必须自己处理查询.

GetGamesOne uses sequelize's assocations to fetch home games and away games seperately and join the before returning them to you. GetGamesTwo manually builds a query, so you only talk to the DB once. Pick whichever you prefer - in one it may not be very clear what is going on, but you are using sequelize to build the query so even if you change the names of the keys later on it will still work. Two is very short and clear, but you have to handle the query yourself.

我还将您的关系从游戏到团队更改为belongsTo-这意味着外键在游戏表中.

I also changed your relation from game to team to belongsTo - meaning that the foreign key is in the game table.

使用Sequelize;-)

Have fun using Sequelize ;-)

这篇关于Sequelize.js使用hasMany两次联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！