sequelize (js) association for multiple columns with one table

Question

hope you're doing great.

I have an issue with sequelizejs I cannot get to work for some reason, no matter how I set up the associations in between tables. My problem is, I have a table, called Results, this table has a structure of:

| id (primary) | first_guy_id | second_guy_id | third_guy_id | notes |
|--------------|--------------|---------------|--------------|-------|
|              |              |               |              |       |

This table connects to Users, and here comes the fun part, the table Users has a simple structure, like so

| id (primary) | first | last |
|--------------|-------|------|
|              |       |      |

What I would like to do with sequelize is, to get a result (in json afterwards) that looks similar to this, nested with connections:

{
  "id": 17,
  "first_guy_id": { "id": 12, "first": "First .... },
  "second_guy_id": { "id": 14, "first": "First .... },
  "third_guy_id": { "id": 19, "first": "First .... },
  "notes": "notes",
}

Doing the SQL JOIN in regular SQL is easy even if all the id's for each column (first_guy, second ...) are different but reach such a goal in sequelize rendered myself completely hopeless.

When I associate the two tables this way:

Results.hasOne(User, { as:'first_guy', foreignKey: 'first_guy' });
User.belongsTo(Results, { as: 'first_guy' }); 

When doing the actual call using

findOne ...
   include: [
        { model: User,
          include: [
            {
              model: User,
              as: 'first_ugy',
              where: {
                    'id': sequelize.col('Results.first_guy_id')
              },
            }
          ],
        },

I get an error of:

ER_BAD_FIELD_ERROR: Unknown column 'Results.user_id' in 'field list'

This is of course because it looks for user_id as foreign key, connecting to table "User" it thinks the key should be user_id.

Two problems I have finding this solution are:

1. How do I join the result on any field with sequelize same way I would do with regular SQL (pseudo code here) "INNER JOIN User.id on Results.first_guy_id as MY_FIELD_NAME"

2. How do I join all of those fields to the same model but with different results for each column? So the get result from Results would return nested 3 different users by their ID listed in the Results table in that specific row. Using same model?

I went through the docs of sequelize, tried many combinations but never achieving the result. It almost feels like i would have to end up writing models, that are named like those fields "First_gu", "second_guy", "third_guy" for Sequalize to work and auto append the "_id" there like it does with the User model now. But it feels like too much of an effort for something that should be solved by the "foreignKey" attribute in the belongsTo / hasOne?

Answer 1

Here's a simple solution. You should define relation for every result's column.

Results.belongsTo(User, { as:'firstGuy', foreignKey: 'first_guy_id'});
Results.belongsTo(User, { as:'secondGuy', foreignKey: 'second_guy_id'});
...

Then you can do querying like this:

Results.findById(resultId, {
   include: [{
      model: User,
      as: 'firstGuy'
   }, {
      model: User,
      as: 'secondGuy'
   },
   ...
   ]
}).then(function(result){
   var firstGuyUser = result.firstGuy;
});