Double WHERE IN with Eloquent many to many relations

Question

I want to retrieve information through Eloquent with my relations.

Here is the query now :

SELECT `elements`.*, `stuff_equipement_stats_elements`.`id_stats` AS 
`pivot_id_stats`, `stuff_equipement_stats_elements`.`id_elements` AS 
`pivot_id_elements`
FROM `elements`
INNER JOIN `stuff_equipement_stats_elements` ON `elements`.`id_elements` = 
`stuff_equipement_stats_elements`.`id_elements`
WHERE `stuff_equipement_stats_elements`.`id_stats` IN (1, 4, 10, 12, 13, 15, 
20, 21, 23, 29, 32)

And I would like to have my query like this :

SELECT `elements`.*, 
`stuff_equipement_stats_elements`.`id_stats`,
`stuff_equipement_stats_elements`.`id_equipement` AS `pivot_id_stats`, 
`stuff_equipement_stats_elements`.`id_elements` AS `pivot_id_elements`
FROM `elements`
INNER JOIN `stuff_equipement_stats_elements` ON `elements`.`id_elements` = 
`stuff_equipement_stats_elements`.`id_elements`
**INNER JOIN equipement ON equipement.id_equipement = 
stuff_equipement_stats_elements.id_equipement**
WHERE `stuff_equipement_stats_elements`.`id_stats` IN (1, 4, 10, 12, 13, 15, 
20, 21, 23, 29, 32) **AND stuff_equipement_stats_elements.id_equipement IN 
(11, 14, 19, 27)**

I have an entity composed with 4 keys. From now, I must add a relation to "equipement" to tell Eloquent he must retrieve from both side... I tried different things but no result.

Here are my models :

StuffModel.php

public function equipements(){
    return $this->belongsToMany(EquipementModel::class, 
'stuff_equipement', 'id_stuff','id_equipement')->withPivot('side');
}

EquipementModel.php

public function stats(){
    return $this->belongsToMany(StatsModel::class, 'equipement_stats', 
'id_equipement','id_stats')->withPivot('poids_equipement_stats')- 
>withPivot('nb_elem_aleatoire');
}

StatsModel.php

public function elema(){
    return $this->belongsToMany(ElementModel::class, 
'stuff_equipement_stats_elements', 'id_stats','id_elements');
}

BuilderController.php

StuffModel::with('equipements.stats.elema')>where('lien_stuff',$id)->first()

I tried to add relation between equipement and element thinking Eloquent would know this relation but no success... Like this :

    public function elem(){
    return $this->belongsToMany(ElementModel::class, 'stuff_equipement_stats_elements', 'id_stats','id_elements');
}

Thanks!

EDIT :

Here is my relation :

Stuff belongsToMany Equipement, Equipement belongsToMany Stats,

Stats and Equipements together have Elements (we must know bot)

I don't understand how to precise elem() that it must search through stats (this is what it is doing now) and equipements.

EDIT 2 :

Here is my diagram. What I'm doing now is getting all equipement for a defined stuff. And through my equipement I can know all stats of this concerned "stuff". It's ok for that.

Now I must retrieve elements but IN the result of stats and equipement both (see my second query)

Here is my diagram :

stuff_equipement_stats_elements :

id_stuff, id_equipement, id_stats, id_elements are primary key.

I can add an element depending on the three others key. I now want to retrieve this table through eloquent but with a where clause ON the stats AND ON the equipement

EDIT :

    "id_stats":32,
"n_stats":"Mastery",
"image_stats":null,
"pivot":{  
   "id_equipement":11,
   "id_stats":32,
   "weight":"235",
   "rand-elem":"3"
},
"elements":[  
   {  
      "id_elements":2,
      "n_elements":"Feu",
      "pivot":{  
         "id_stats":32,
         "id_elements":2,
         "id_equipement":15
      }
   },
   {  
      "id_elements":3,
      "n_elements":"Eau",
      "pivot":{  
         "id_stats":32,
         "id_elements":3,
         "id_equipement":13
      }
   }
]

Here the JSON response I got, I must filter the pivot parent (coming from the previous belongs to many) with the new pivot, otherwise I will not have the good response

Answer 1

UPDATE: Finally, I could understand your question :P. It seems you can't get all elements directly from Stuff model, because there will be a lot of duplicate keys for stuff_id & element_id in the stuff_equipement_stats_elements table.

However, I think there could be another way, could you try:

Create a model StuffEquipementStatsElements:

class StuffEquipementStatsElements
{
    public function stuff()
    {
        return $this->belongsTo('App\Stuff','stuff_id');
    }

    public function elements()
    {
        return $this->belongsTo('App\Element','elemnent_id');
    }
}

In your controller:

StuffEquipementStatsElements::with('stuff')->with('elements')->where('stuff_id',$id)->get();

ORIGINAL:

I try to draw the diagram to understand your question easier: