Ticked
182
10
Eloquent

Retrieving data involving a pivot table

Posted 2 years ago by Ticked

Hi,

The problem: I have 2 tables, "treatments" and "hospitals" and a pivot table "hospital_treatment"

On my view I need to show the results of my query as follows:

Hospital One
Treatment A
Treatment B
Treatment C

Hospital Two
Treatment B
Treatment C

Hospital Three
Treatment A
Treatment D

Etc...

My "Treatment" model:

    public function magic()
    {
        return $this->belongsToMany('App\Hospital');
    }

My "Hospital" model

    public function treatments()
    {
        return $this->belongsToMany('App\Treatment');
    }

My Controller

        $treatments2 =  Treatment::with('magic')
            ->join('hospital_treatment', 'hospital_treatment.treatment_id', '=', 'treatments.id')
            ->join('hospitals', 'hospital_treatment.hospital_id', '=', 'hospitals.id')
            ->where('treatments.status', 1)
            ->select('hospitals.name', 'treatments.friendly_name', 'treatments.slug', 'treatments.medical_name')
            ->orderBy('hospitals.name')
            ->orderBy('treatments.friendly_name')
            ->groupBy('hospitals.name')
            ->get();
        $treatments_h_groups = $treatments2;

    return view('treatments_h_groups'));

My View

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

Any advice on why this is not working will be much appreciated, I have a similar code (not involving a pivot table) working with no problems.

Thank you.

Please sign in or create an account to participate in this conversation.