Sequelize query with two JOINs on custom fields

Question

Currently I've got a solution with the following raw query, which works well.

Here's a SqlFiddle playground.

And here's the raw query that works well:

const sql = `SELECT u.*, g.name AS "gender"
  FROM users u
  INNER JOIN connections con ON con.user_id = u.id
  INNER JOIN completed_quizzes q ON q.user_id = u.id
  LEFT JOIN genders g ON g.id = u.gender_id
  WHERE
    con.other_user_id='foo'
    AND q.quiz_id=1
    AND con.status='approved'
    AND u.deleted_at IS NULL
    AND con.deleted_at IS NULL
    AND q.deleted_at IS NULL
  LIMIT 10
  OFFSET 0
`;

But I'm trying to replace it with Sequelize's object modelling. So far, the most meaningful solution I've come up with this:

const sequelize = {
      include: [
        {
          model: ConnectionsModel,
          as: 'connections',
          where: { otherUserId: userId, status: ConnectionStatus.approved },
          required: true,
          duplicating: false
        },
        {
          model: CompletedQuizzesModel,
          as: 'completedQuizzes',
          where: { userId, quizId },
          required: true,
          duplicating: false
        },
        {
          model: GendersModel
        }
      ],
      nest: true,
      // have to specify `raw: false`, otherwise Sequelize returns only the first associated record
      raw: false
    };

It generates the following query (I've prettified it, for ease of reading):

SELECT u.*, g.name AS gender FROM users AS u 
    INNER JOIN connections AS con ON u.id = con.user_id AND (con.status = 'approved' AND con.other_user_id = 'foo') 
    INNER JOIN completed_quizzes AS q ON u.id = q.user_id AND (q.user_id = 'foo' AND q.quiz_id = '1')
    LEFT OUTER JOIN genders AS g ON u.gender_id = g.id 
WHERE u.deleted_at IS NULL 
LIMIT 20 
OFFSET 0;

But it returns nothing... no rows. Whereas, the original solution returns the rows as needed.

I can see the difference between the general where (which is applied to the whole query in the raw query) and the same attributes appended to the on clause in the joins. Not sure if that's the problem, though.

Here's the SqlFiddle playground again.

Answer 1

You added an extra condition in the include option CompletedQuizzesModel. In the original SQL query you don't have a condition on userId.

So this include should be like this:

{
          model: CompletedQuizzesModel,
          as: 'completedQuizzes',
          where: { quizId }, // removed userId
          required: true,
          duplicating: false
        }

The result query works just like the original one:

SELECT u.*, g.name AS gender FROM users AS u 
    INNER JOIN connections AS con ON u.id = con.user_id AND (con.status = 'approved' AND con.other_user_id = 'foo') 
    INNER JOIN completed_quizzes AS q ON u.id = q.user_id AND (q.quiz_id = '1')
    LEFT OUTER JOIN genders AS g ON u.gender_id = g.id 
WHERE u.deleted_at IS NULL 
LIMIT 20 
OFFSET 0;