3 years ago

Retrieving data involving a pivot table

Posted 3 years ago by Ticked


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


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')
        $treatments_h_groups = $treatments2;

    return view('treatments_h_groups'));

My View

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

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.