title: How to generate query on join table using Sequelize?
body: <p>Let us say there are two tables namely User and User Role.
  The relationship between user and user role is one to many.
  Sequelize model for the user is as following -</p>
  
  <pre><code>const user = sequelize.define(
    'user', {
      id: {
        type: DataTypes.BIGINT,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
        field: 'id'
      },
      userName: {
        type: DataTypes.STRING(200),
        allowNull: false,
        field: 'username'
      },
      password: {
        type: DataTypes.STRING(200),
        allowNull: false,
        field: 'password'
      }
    }, {
      tableName: 'user'
    }
  );
  </code></pre>
  
  <p>Sequelize model for user role is as follwing -</p>
  
  <pre><code>const userRole = sequelize.define(
    'userRole', {
      id: {
        type: DataTypes.BIGINT,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
        field: 'id'
      },
      userId: {
        type: DataTypes.BIGINT,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
        field: 'user_id'
      },
      password: {
        type: DataTypes.STRING(200),
        allowNull: false,
        field: 'password'
      }
    }, {
      tableName: 'userRole'
    }
  );
  </code></pre>
  
  <p>Sequelize association is defined as follows -</p>
  
  <pre><code>user.hasMany(models.userRole, { foreignKey: 'user_id', as: 'roles' });
  userRole.belongsTo(models.user, { foreignKey: 'user_id', as: 'user' });
  </code></pre>
  
  <p>I want to generate the following query using </p>
  
  <pre><code>Sequelize -
  SELECT * 
  FROM   USER 
         INNER JOIN (SELECT user_role.user_id, 
                            role 
                     FROM   user_role 
                            INNER JOIN USER tu 
                                    ON tu.id = user_role.user_id 
                     GROUP  BY user_id 
                     ORDER  BY role) AS roles 
                 ON USER.id = roles.user_id; 
  </code></pre>
  
  <p>I am developing an API which will be consumed by the front end grid for showing user info. There is search functionality on role attribute of user role table. If any of role of a specific user is matched then I expect a user record with all the roles which are associated with that user. </p>