title: Join two tables using another table in sequelize
body: <p>I have three MySQL tables: <code>app</code>, <code>app_metadata</code> and <code>ecosystem</code>.</p>
  
  <p>app table structure is:</p>
  
  <pre><code>+---------------+------------+----+---+-------+--------------+
  |Field          |Type        |Null|Key|Default|Extra         |
  +---------------+------------+----+---+-------+--------------+
  |id             |bigint(20)  |NO  |PRI|NULL   |auto_increment|
  |name           |varchar(255)|NO  |   |NULL   |              |
  +---------------+------------+----+---+-------+--------------+
  </code></pre>
  
  <p>app_metadata table structure is:</p>
  
  <pre><code>+---------------+------------+----+---+-------+--------------+
  |Field          |Type        |Null|Key|Default|Extra         |
  +---------------+------------+----+---+-------+--------------+
  |id             |bigint(20)  |NO  |PRI|NULL   |auto_increment|
  |app_id         |bigint(20)  |NO  |MUL|NULL   |              |
  |tag_name       |varchar(255)|NO  |   |NULL   |              |
  |tag_value      |text        |NO  |   |NULL   |              |
  +---------------+------------+----+---+-------+--------------+
  </code></pre>
  
  <p>ecosystem table structure is:</p>
  
  <pre><code>+---------------+------------+----+---+-------+--------------+
  |Field          |Type        |Null|Key|Default|Extra         |
  +---------------+------------+----+---+-------+--------------+
  |id             |bigint(20)  |NO  |PRI|NULL   |auto_increment|
  |name           |varchar(255)|NO  |   |NULL   |              |
  +---------------+------------+----+---+-------+--------------+
  </code></pre>
  
  <p>I would like to join <code>app table</code> with <code>ecosystem table</code> using the <code>tag_value column</code> and <code>tag_name column</code> (only when its value is <strong>ecosystem_id</strong>) from <code>app_metadata table</code>.</p>
  
  <p>This is the code which I tried to implement this use-case, but it does not generate the expected result.
  Actual result:</p>
  
  <pre><code>{
    "apps": [
      {
        "id": 1,
        "name": "EXAMPLE",
        "appMetadata": [
          {
            "id": 1,
            "tagName": "ecosystem_id",
            "tagValue": "1",
            "appId": 1,
            "ecosystem": {
              "id": 1,
              "name": "default"
            }
          },
          {
            "id": 470,
            "tagName": "vcalmic",
            "tagValue": "2",
            "appId": 1,
            "ecosystem": {
              "id": 2,
              "name": "dc/ed1"
            }
          }
        ]
      }
    ]
  }
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>{
    "apps": [
      {
        "id": 1,
        "name": "EXAMPLE",
        "appMetadata": [
          {
            "id": 1,
            "tagName": "ecosystem_id",
            "tagValue": "1",
            "appId": 1,
            "ecosystem": {
              "id": 1,
              "name": "default"
            }
          },
          {
            "id": 470,
            "tagName": "vcalmic",
            "tagValue": "2",
            "appId": 1,
            "ecosystem": null
          }
        ]
      }
    ]
  }
  </code></pre>
  
  <p>OR this one</p>
  
  <pre><code>{
    "apps": [
      {
        "id": 1,
        "name": "EXAMPLE",
        "ecosystem": {
           "id": 1,
           "name": "default"
         },
        "appMetadata": [
          {
            "id": 1,
            "tagName": "ecosystem_id",
            "tagValue": "1",
            "appId": 1
          },
          {
            "id": 470,
            "tagName": "vcalmic",
            "tagValue": "2",
            "appId": 1
          }
        ]
      }
    ]
  }
  </code></pre>
  
  <pre class="lang-js prettyprint-override"><code>Models.ecosystem.hasOne(Models.appMetadata, { foreignKey: 'tagValue' });
  Models.appMetadata.belongsTo(Models.ecosystem, { foreignKey: 'tagValue' });
  
  const options = {
    where: { accountId },
    include: [
      {
        model: Models.appMetadata,
        include: [{ model: Models.ecosystem }],
      }
    ]
  };
  const apps = await Models.app.findAll(options);
  </code></pre>