Hello everybody,

I have this SQL Query :

            ->select('herbs.name as hname', 'herbs.sciname','hinteractions.*','targets.name as targetname', 'forces.name as force_name')
            ->leftJoin('hinteractions', 'herbs.id', '=', 'herb_id')
            ->leftJoin('forces', 'forces.id', '=', 'force_id')
            ->leftJoin('targets', 'targets.id', '=', 'hinteractions.target_id')->where('herbs.id', $id)
            ->get();```

who gives me this :


> Illuminate\Support\Collection {#463 ▼
  #items: array:3 [▼
    0 => {#467 ▼
      +"hname": "Ail"
      +"sciname": "Allium sativum L."
      +"id": 1
      +"herb_id": 6
      +"target_id": 11
      +"note": """
        Des études cliniques montrent que l'ail n'a pas d'effet sur le CYP1A2


                    Zhou décrit des interactions théoriques au niveau enzymatique
        """
      +"user_id": 1
      +"force_id": 1
      +"validated": null
      +"created_at": null
      +"updated_at": null
      +"targetname": "Absorption digestive"
      +"force_name": "forte"
    }


I have my information in my table for the right herb :

https://www.dropbox.com/s/s5291dhwbo95z9e/Capture%20d%27%C3%A9cran%202020-05-23%2014.28.53.png?dl=0

Now I need other information, because a herb might have one to several effects.

I have a join table called "hinteraction_has_effects"

https://www.dropbox.com/s/yk7ki2q39ahnhz4/Capture%20d%27%C3%A9cran%202020-05-23%2014.31.03.png?dl=0

With this query :


`            $hinteractions_has_effects = Hinteraction::with('effects')->get();`

that gives me this :


> Illuminate\Database\Eloquent\Collection {#1381 ▼
  #items: array:3 [▼
    0 => App\Hinteraction {#1464 ▼
      #connection: "mysql"
      #table: "hinteractions"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:9 [▼
        "id" => 1
        "herb_id" => 6
        "target_id" => 11
        "note" => """
          Des études cliniques montrent que l'ail n'a pas d'effet sur le CYP1A2


                      Zhou décrit des interactions théoriques au niveau enzymatique
          """
        "user_id" => 1
        "force_id" => 1
        "validated" => null
        "created_at" => null
        "updated_at" => null
      ]
      #original: array:9 [▶]
      #changes: []
      #casts: []
      #classCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: array:1 [▼
        "effects" => Illuminate\Database\Eloquent\Collection {#1473 ▼
          #items: array:2 [▼
            0 => App\Effect {#1509 ▼
              #connection: "mysql"
              #table: "effects"
              #primaryKey: "id"
              #keyType: "int"
              +incrementing: true
              #with: []
              #withCount: []
              #perPage: 15
              +exists: true
              +wasRecentlyCreated: false
              #attributes: array:4 [▼
                "id" => 1
                "name" => "inhibiteur"
                "created_at" => "2020-05-22 06:17:25"
                "updated_at" => "2020-05-22 06:17:25"
              ]

I got each time all effects/interactions, not only for the herb with ID 6 for example.

Like you said,  I can include SELECT herb.name,... to this query :


`            $hinteractions_has_effects = Hinteraction::with('effects')->get();`


Do you have an example please ?

It should be something like this :


`$hinteractions_has_effects = Hinteraction::with(['effects'])->select('herbs.name as hname', 'herbs.sciname','targets.name as targetname', 'forces.name as force_name')
            ->leftJoin('hinteractions', 'herbs.id', '=', 'herb_id')
            ->leftJoin('forces', 'forces.id', '=', 'force_id')
            ->leftJoin('targets', 'targets.id', '=', 'hinteractions.target_id')->where('herbs.id', $id)
            ->get();

Thank you for your help.