如何在Adonis中通过关联表查询指定Campaign的班级与用户邮箱

我有三张数据库表：

classes表 #

users表 #

campaign_user关联表 #

• classes表为查询主体；
• users表存储用户邮箱及角色；
• campaign_user为关联表，包含user_id、class_id和campaign_id字段。

需求 #

编写MySQL查询，找到campaign_id等于4的关联记录，将对应用户的email附加到班级信息中，最终返回结果如下：

[
  {id:1,name:"name1",email:"email@1"},
  {id:2,name:"name2",email:"email@2"}
]

其中id、name属于classes表，email属于users表。请问如何在Adonis框架中实现该查询？

方法一：使用查询构造器 #

直接通过Adonis的Database查询构造器完成关联查询，无需提前定义模型关联：

const Database = use('Database')

async function getClassesWithEmail() {
  const result = await Database
    .select('classes.id', 'classes.name', 'users.email')
    .from('classes')
    .innerJoin('campaign_user', 'classes.id', 'campaign_user.class_id')
    .innerJoin('users', 'campaign_user.user_id', 'users.id')
    .where('campaign_user.campaign_id', 4)
  
  return result
}

该查询通过两次内连接，依次关联classes与campaign_user、campaign_user与users表，筛选出campaign_id=4的记录后返回指定字段。

方法二：使用模型关联（推荐） #

如果已在Adonis中定义对应模型，可通过模型关联简化查询：

1. 定义模型关联 #

在app/Models/Classe.js（注意Class为JavaScript关键字，需重命名模型）中定义关联：

class Classe extends Model {
  users() {
    return this
      .belongsToMany('App/Models/User')
      .pivotTable('campaign_user')
      .pivotColumns(['campaign_id'])
  }
}

在app/Models/User.js中定义反向关联：

class User extends Model {
  classes() {
    return this
      .belongsToMany('App/Models/Classe')
      .pivotTable('campaign_user')
      .pivotColumns(['campaign_id'])
  }
}

2. 执行查询 #

通过模型关联筛选并获取目标数据：

const Classe = use('App/Models/Classe')

async function getClassesWithEmail() {
  const classes = await Classe.query()
    .select('classes.id', 'classes.name', 'users.email')
    .innerJoin('campaign_user', 'classes.id', 'campaign_user.class_id')
    .innerJoin('users', 'campaign_user.user_id', 'users.id')
    .where('campaign_user.campaign_id', 4)
    .fetch()
  
  return classes.toJSON()
}

也可通过关联方法查询后调整结果结构：

const Classe = use('App/Models/Classe')

async function getClassesWithEmail() {
  const classes = await Classe.query()
    .with('users', (builder) => {
      builder.where('campaign_user.campaign_id', 4).select('email')
    })
    .fetch()
  
  return classes.toJSON().map(classe => ({
    id: classe.id,
    name: classe.name,
    email: classe.users[0]?.email || null
  }))
}

内容的提问来源于stack exchange，提问作者Aviel Lanzman