joins and native queries

New:
Where on relations:

ApplicationUser.find({where: {roles: {id: 1}}})

Raw MySQL where:

• only with explicit permission enableSql: true

ApplicationUser.find({where: {raw: 'ApplicationUser.id = 3'}}, {enableSql: true})

• tables in JOIN clauses (from relation where) are referenced with 1, 2, ... suffix

ApplicationUser.find({where: {roles: {raw: 'Role1.id = 1'}}}, {enableSql: true})

MySQL queries in include:

• only with explicit permission enableSql: true
• '__' prefix indicates a param to be replaced for the "parent" property after main query
• result: 'hasOne' indicates that result is an object, otherwise array is returned

ApplicationUser.find(
    {
      where: { id: 4 },
      include: [
        {
          type: 'subquery',
          result: 'hasOne',
          sql: 'SELECT COUNT(1) as count FROM RoleMapping WHERE principalId = ? AND principalType = ?',
          params: ['__id', 'USER'],
          property: 'rolesCount',
        },
      ],
    },
    { enableSql: true }
  )

returns:

{
    "name": "Jose Oliver",
    "rolesCount": {
        "count": 1
    }
}