Retrieving data involving a pivot table

Level 80

@Ticked I don’t understand why you’re attempting to build the query yourself, when the relations should be set up on the models?

If you do set up the relations on the models (a Hospital belongs to many Treatment, and vice versa) then you can fetch all hospitals, and group the resultant collection by the hospital name:

$hospitals = Hospital::with('treatment')->get()->groupBy('name');

This will give a collection where the key is the hospital name, and the the value is a collection of treatments, which you can now iterate over in your Blade template:

<ul class="list-unstyled">
    @foreach($hospitals as $hospital => $treatments)
        <li>
            <h4>{{ $hospital }}</h4>
            <ul class="list-unstyled treatments-group">
                @foreach($treatments as $treatment)
                    <li>
                        <a href="#">
                            <h5>
                                <i class="fa fa-file-text-o"></i>
                                {{ $treatment->name }}
                            </h5>
                            <p>{{ $treatment->friendly_name }}</p>
                        </a>
                    </li>
                @endforeach
            </ul>
        </li>
    @endforeach
</ul>

1 like

Level 1

Thank you @martinbean,

You are right. My lack experience...

I have applied your code and for some reason, it only "echo" the hospital's names, the treatments that belong to each hospital won't come up.

I have tried many changes but no luck, when I do dd($hospitals); on my controller, I get the following:

Collection {#929 ▼
  #items: array:8 [▼
    "Test Hospital A" => Collection {#465 ▼
      #items: array:1 [▼
        0 => Hospital {#467 ▼
          #fillable: array:2 [▶]
          #connection: null
          #table: null
          #primaryKey: "id"
          #keyType: "int"
          #perPage: 15
          +incrementing: true
          +timestamps: true
          #attributes: array:5 [▶]
          #original: array:5 [▶]
          #relations: array:1 [▼
            "treatment" => Collection {#926 ▼
              #items: array:33 [▼
                0 => Treatment {#721 ▼
                  #fillable: array:17 [ …17]
                  #connection: null
                  #table: null
                  #primaryKey: "id"
                  #keyType: "int"
                  #perPage: 15
                  +incrementing: true
                  +timestamps: true
                  #attributes: array:20 [ …20]
                  #original: array:22 [ …22]
                  #relations: array:1 [ …1]
                  #hidden: []
                  #visible: []
                  #appends: []
                  #guarded: array:1 [ …1]
                  #dates: []
                  #dateFormat: null
                  #casts: []
                  #touches: []
                  #observables: []
                  #with: []
                  #morphClass: null
                  +exists: true
                  +wasRecentlyCreated: false
                }
                1 => Treatment {#725 ▶}
                2 => Treatment {#727 ▶}
                3 => Treatment {#731 ▶}
                4 => Treatment {#740 ▶}
                5 => Treatment {#744 ▶}
                6 => Treatment {#749 ▶}
                7 => Treatment {#754 ▶}
                8 => Treatment {#756 ▶}
                9 => Treatment {#773 ▶}
                10 => Treatment {#781 ▶}
                11 => Treatment {#783 ▶}
                12 => Treatment {#787 ▶}
                13 => Treatment {#798 ▶}
                14 => Treatment {#803 ▶}
                15 => Treatment {#807 ▶}
                16 => Treatment {#813 ▶}
                17 => Treatment {#819 ▶}
                18 => Treatment {#825 ▶}
                19 => Treatment {#830 ▶}
                20 => Treatment {#835 ▶}
                21 => Treatment {#841 ▶}
                22 => Treatment {#846 ▶}
                23 => Treatment {#850 ▶}
                24 => Treatment {#856 ▶}
                25 => Treatment {#863 ▶}
                26 => Treatment {#867 ▶}
                27 => Treatment {#873 ▶}
                28 => Treatment {#882 ▶}
                29 => Treatment {#890 ▶}
                30 => Treatment {#900 ▶}
                31 => Treatment {#916 ▶}
                32 => Treatment {#919 ▶}
              ]
            }
          ]
          #hidden: []
          #visible: []
          #appends: []
          #guarded: array:1 [▶]
          #dates: []
          #dateFormat: null
          #casts: []
          #touches: []
          #observables: []
          #with: []
          #morphClass: null
          +exists: true
          +wasRecentlyCreated: false
        }
      ]
    }
    "Test Hospital B" => Collection {#463 ▶}
    "Test Hospital C" => Collection {#464 ▶}
    "Test Hospital D" => Collection {#461 ▶}
  ]
}

Is it correct that there is not the treatment name?

miigaa

Level 24

No need to group, just loop through the hospitals.

<ul class="list-unstyled">
    @foreach(Hospital::with('treatments')->get() as $hospital)
        <li>
            <h4>{{ $hospital->name }}</h4>
            <ul class="list-unstyled treatments-group">
                @foreach($hospital->treatments as $treatment)
                    <li>
                        <a href="#">
                            <h5>
                                <i class="fa fa-file-text-o"></i>
                                {{ $treatment->name }}
                            </h5>
                            <p>{{ $treatment->friendly_name }}</p>
                        </a>
                    </li>
                @endforeach
            </ul>
        </li>
    @endforeach
</ul>

Level 1

Thank you @selmonal ,

I have updated my controller following your suggestion, it works but when updating the full query it fails. I need to check the treatment status.

I have added ->orderBy('name'), so my hospitals are in order, it works

I have added ->where('treatment.status', 1), it fails.

$hospitals = Hospital::with('treatment')
->where('treatment.status', 1)
->orderBy('name')
->get();

Do you have any suggestion?

miigaa

Level 24

I think you should fetch from treatments. like this

$treatments = Treatment::where('status', 1)->with('magic')->get()->groupBy('magic_id');

dd($treatments);

Level 80

@Ticked You seemed to have named your relation using the singular version (treatment) rather than plural (treatments). If a Hospital belongs to many Treatment, then the relation name should be plural:

class Hospital extends Model
{
    public function treatments()
    {
        return $this->belongsToMany(Treatment::class);
    }
}

You can then loop over hospitals (as @selmonal says), and each treatment for each hospital:

$hospitals = Hospital::with('treatments')->orderBy('name')->get();

<ul>
    @foreach($hospitals as $hospital)
        <li>
            <h4>{{ $hospital->name }}</h4>
            <ul>
                @foreach($hospital->treatments as $treatment)
                    <li>{{ $treatment->name }}</li>
                @endforeach 
            </ul>
        </li>
    @endforeach
</ul>

Level 1

Hey @martinbean,

Thank your for your time, at the moment I have the code exactly as the one you have provided. Unfortunately, the treatments do not get ordered, only the hospitals.

I have tried:

$hospitals = Hospital::with('treatments')->orderBy('name')->orderBy('treatments.friendly_name')->get();

I get:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'treatments.friendly_name' in 'order clause' (SQL: select * from `hospitals` order by `name` asc, `treatments`.`friendly_name` asc)"

I know is looking on hospitals rather than treatments... I have tried many changes..

Best Answer

Level 80

@Ticked Might be worth reading through the Laravel documentation on Eloquent relationships, rather than copy-and-pasting code and not understanding what’s going on, otherwise we’re writing the solution for you.

You can pass options (such as ordering) to relationships via an array in your query:

$hospitals = Hospital::with(['treatments' => function ($query) {
    $query->orderBy('friendly_name');
}])->orderBy('name')->get();

Alternatively, if you always want treatments to be ordered this way, then you can chain it to the relation:

class Hospital extends Model
{
    public function treatments()
    {
        return $this->belongsToMany(Treatment::class)->orderBy('friendly_name');
    }
}

You don’t then need to include it when querying in your controllers, and can go back to just:

$hospitals = Hospital::with('treatments')->orderBy('name')->get();

Level 1

Hi @martinbean,

I just wanted to say thank you, your advice is spot on, I'll pay more attention to the documentation, I checked but I could not figure out how to get all this working. I spent time reading, the problem on my coding experience stage is to understand what happens, contributors like you are definitely worth following... Thanks.