mysqladonis.js

Information from a third table using a linking table

I have three tables:

classes

id name
1 name1
2 name2

users

id email
1 email@1
2 email@2

campaign_user

user_id campaign_id class_id
1 4 1
2 4 2
  1. "classes" is the table I am doing the query on.
  2. "users" which contains the user's email and his role.
  3. "campaign_user" which is the linking table that has user_id and class_id and campaign_id.

I need to make a call in MYSQL that finds the user that belongs to the campaign and the class that has the rule equal to 4 (which is in the campaign_user table) and attach his email to the class How do you do it in Adonis?

enter image description here

I expect to get back: [{id:1,name:name1,email:email@1 },{id:2,name:name2,email:email@2 }]

id - Belongs to class.

name - Belongs to class.

email - Belongs to user.

Solution

  • I found a solution:

    in Models/class.ts

    @manyToMany(() => User, {
pivotTable: 'campaign_user',
localKey: 'id',
relatedKey: 'id',
pivotForeignKey: 'class_id',
pivotRelatedForeignKey: 'user_id',
onQuery(query) {
  query.where('campaign_user.role', 4).select('email', 'phone', 'username')
  },  
})
public managerDetails: ManyToMany<typeof User>

    in campaign model:

      @hasMany(() => Class)
  public classes: HasMany<typeof Class>

    And when I use it then I created the function:

    public async classesIndex(): Promise<Class[]> {
const c: Campaign = this
await c.load('classes')
for (let i = 0; i < c.classes.length; i++) {
  await c.classes[i].load('managerDetails')
}
return c.classes
}