title: sequelize (js) association for multiple columns with one table
body: <p>hope you're doing great.</p>
  
  <p>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 <strong>Results</strong>, this table has a structure of:</p>
  
  <pre><code>| id (primary) | first_guy_id | second_guy_id | third_guy_id | notes |
  |--------------|--------------|---------------|--------------|-------|
  |              |              |               |              |       |
  </code></pre>
  
  <p>This table connects to <strong>Users</strong>, and here comes the fun part, the table <strong>Users</strong> has a simple structure, like so </p>
  
  <pre><code>| id (primary) | first | last |
  |--------------|-------|------|
  |              |       |      |
  </code></pre>
  
  <p>What I would like to do with sequelize is, to get a result (in json afterwards) that looks similar to this, nested with connections:</p>
  
  <pre><code>{
    "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",
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>When I associate the two tables this way:</p>
  
  <pre><code>Results.hasOne(User, { as:'first_guy', foreignKey: 'first_guy' });
  User.belongsTo(Results, { as: 'first_guy' }); 
  </code></pre>
  
  <p>When doing the actual call using</p>
  
  <pre><code>findOne ...
     include: [
          { model: User,
            include: [
              {
                model: User,
                as: 'first_ugy',
                where: {
                      'id': sequelize.col('Results.first_guy_id')
                },
              }
            ],
          },
  </code></pre>
  
  <p>I get an error of:</p>
  
  <pre><code>ER_BAD_FIELD_ERROR: Unknown column 'Results.user_id' in 'field list'
  </code></pre>
  
  <p>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. </p>
  
  <p>Two problems I have finding this solution are: </p>
  
  <ol>
  <li><p>How do I join the result on any field with sequelize same way I would do with regular SQL (pseudo code here) "<strong>INNER JOIN User.id on Results.first_guy_id as MY_FIELD_NAME</strong>" </p></li>
  <li><p>How do I join all of those fields to the same model but with different results for each column? So the get result from <strong>Results</strong> would return nested 3 different users by their ID listed in the <strong>Results</strong> table in that specific row. Using same model?</p></li>
  </ol>
  
  <p>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 <strong>User</strong> 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?</p>