title: Custom or override join in Sequelize.js
body: <p>I need to create a custom join condition using <a href="http://sequelizejs.com" rel="nofollow noreferrer">Sequelize.js</a> with MSSQL. Specifically, I need to join <code>TableB</code> based on a <code>COALESCE</code> value from columns in <code>TableA</code> and <code>TableB</code> and end up with a join condition like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>LEFT OUTER JOIN [TableB]
      ON [TableB].[ColumnB] = COALESCE (
          [TableC].[ColumnC],
          [TableA].[ColumnA]
      )
  </code></pre>
  
  <p>I'd settle for an <code>OR</code> clause in my join:</p>
  
  <pre class="lang-sql prettyprint-override"><code>LEFT OUTER JOIN [TableB]
      ON [TableB].[ColumnB] = [TableA].[ColumnA]
      OR [TableB].[ColumnB] = [TableC].[ColumnC]
  </code></pre>
  
  <p>I <a href="https://stackoverflow.com/a/39774664/2345211">read</a> that you can achieve behaviour like this by including <code>required: false</code> in your scope definition. As you can see, I've plastered my scope with it attempting to get this to work.</p>
  
  <p>The best I could get is this (note the <code>AND</code> clause):</p>
  
  <pre class="lang-sql prettyprint-override"><code>LEFT OUTER JOIN [TableB]
      ON [TableB].[ColumnB] = [TableA].[ColumnA]
      AND [TableB].[ColumnB] = COALESCE (
          [TableC].[ColumnC],
          [TableA].[ColumnA]
      )
  </code></pre>
  
  <p>If I were using MySQL, I think I could simply use the <code>COALESCE</code> value from the <code>SELECT</code> in the <code>JOIN</code> and be good to go but in my prior research read that it was required to recalculate the value.</p>
  
  <p>I've included a stripped down model definition for <code>TableA</code>:
  </p>
  
  <pre><code>export default (sequelize, DataTypes) =&gt; sequelize.define('TableA',
      {
          // Attributes omitted..
      },
      {
          classMethods: {
              associate ({
                  TableB,
                  TableC
              }) {
                  this.belongsTo(TableB, {
                      foreignKey: 'ColumnA',
                      targetKey: 'ColumnB'
                  });
  
                  this.belongsTo(TableC, {
                      foreignKey: 'ColumnA',
                      targetKey: 'ColumnC'
                  });
              },
              attachScope ({
                  TableB,
                  TableC
              }) {
                  this.addScope('defaultScope', {
                      attributes: [
                          ...Object.keys(this.attributes),
                          [
                              sequelize.fn(
                                  'COALESCE',
                                  sequelize.col('[TableC].[ColumnC]'),
                                  sequelize.col('[TableA].[ColumnA]')
                              ),
                              'ColumnA'
                          ]
                      ],
                      include: [
                          {
                              model: TableB,
                              where: {
                                  ColumnB: sequelize.fn(
                                      'COALESCE',
                                      sequelize.col('[TableC].[ColumnC]'),
                                      sequelize.col('[TableA].[ColumnA]')
                                  )
                              },
                              required: false
                          },
                          {
                              model: TableC,
                              required: false
                          }
                      ],
                      required: false
                  }, { override: true });
              }
          }
      }
  );
  </code></pre>
  
  <p>Any assistance would be greatly appreciated, and if any additional information is required please let me know.</p>
  
  <p><em>Note: I'm working with a legacy database and unfortunately cannot change the data structure.</em></p>