How to get data from pivot based on two others pivots?


#1

Hi :slight_smile:

I have browsed the whole web looking for an answer for my problem, I have not managed to find one, I’m counting on you :smile:


  • I have Users (table: users)
  • I have a Clients enumerations (table: clients)
  • I have a Skills enumerations (table: skills)

  • User can have Clients (table: user_clients)
  • User can have Skill (table: user_skills)

This is the easy part. It works.


But, and this is where I need your help, I’d like to:

  • A user can attach one of his skills to one of his clients (table: user_skills_clients ?)

This is how I tried to represent the relationship:

I don’t know how to code this relationship with Lucid.

I would like to get a user with his skills attached by clients.

const result = {
  id: 1,
  firstname: 'John',
  lastname: 'Doe',
  skills_clients: [
    {
      id: 1,
      name: 'Client 1',
      skills: [
        { id: 1, name: 'AdonisJS' },
        { id: 2, name: 'Awesomeness' }
      ]
    },
    {
      id: 2,
      name: 'Client 2',
      skills: [
        { id: 3, name: 'Thank' },
        { id: 4, name: 'you' }
      ]
    },
  ]
}

Thank you very much for your help!!


#2

Can you provide the migrations and models for this code. I can try to look into it using manyThrough relationship?

A sample project with just the migrations and models will do it for me


#3

Here is it: https://github.com/vincentducorps/help-adonis-lucid-2342

Ready with:

  • Migrations (based on schema above)
  • Seeds (except for the users_skills_clients table obviously)
  • Models (User, Skill, Client)
  • Controllers (User, Skill, Client)
  • Routes (User, Skill, Client)

Routes:



Thank you virk!!


#4

Hey @virk,

Have you succeeded?
Can I do something to help?


#5

You could probably simplify this considerably by creating a model for your user_clients relationship.

If UserClient is a model, it can haveOne client, belongTo one user, and haveMany user_skills

Then you could fetch all of a user’s UserClient’s, with client info and skill info.