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


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!!


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


Here is it:

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)


Thank you virk!!


Hey @virk,

Have you succeeded?
Can I do something to help?


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.


@vincentducorps Hi, off-topic, but I am curious to know what tool did you use to draw the UML class diagram?