Laravel models to implement one to many and many to many in single query

Question

i have this table structure, project has one to many relation with rewards , rewards and shipping has many to many relation with pivot table reward_ship.

projects       rewards         shipping       reward_ship
---------      --------        --------       ------------ 
id             id              id             id 
title          amount          location       reward_id
amount         project_id      name           ship_id

i am trying to extract one particular project details with all other associate tables data(rewards and shipping data using reward_ship table) in one query.

These is how i am trying

Projects Model

    class Rewards extends Model {
         public function projs(){
              return $this->hasMany('App\Rewards');
          }
         public function rewds(){
              return $this->belongsToMany('App\Shipping')
              ->withPivot('reward_ship', 'ship_id', 'reward_id');
         }
         public function shiplc(){
               return $this->belongsToMany('App\Rewards')
               ->withPivot('reward_ship', 'ship_id', 'reward_id');
         }
      }
class Rewards extends Model {
    public function proj() {
        return $this->belongsTo('App\Projects');
    }
}

Controller api class

Route::get('projects/{id}', function($id) {
$p = Projects::find($id);
$getd = Rewards::with('proj')
    ->where('rewards.project_id', '=', $p->id)
    ->get();
 });

it doesn't work. i search and tried many related model base query in larvel. i know my implementation are wrong. Please suggest me to work out.

Check this out https://stackoverflow.com/questions/23788844/hasmanythrough-with-one-to-many-relationship/23789210#23789210 — Rutvij Kothari, Nov 15 '17 at 18:51
or i want to get data of rewards and shipping by giving project_id — Raikumar Khangembam, Nov 15 '17 at 18:51
can you please suggest me in example code of model class with controller api for this?? — Raikumar Khangembam, Nov 15 '17 at 18:55
@RaikumarKhangembam you have 2x model Rewards this don't seem correct. — francisco, Jan 25 '22 at 14:15

score 2 · Answer 1 · answered Nov 15 '17 at 19:10

You can use Laravel 5.5 new feature API Resources.

It helps you to format the output of objects such as models or collections, to display attributes and also relationships.

So, you could do something like this in your ItemResource:

    <?php

    namespace App\Http\Resources;

    use Illuminate\Http\Resources\Json\Resource;

    class Project extends Resource
    {
        /**
         * Transform the resource into an array.
         *
         * @param  \Illuminate\Http\Request
         * @return array
         */
        public function toArray($request)
        {
            return [
                'project_id' => $this->project_id,
                'title' => $this->title,
                'amount' => $this->amount,
                // To access relationship attributes:
                'rewards' => $this->rewards->load('shippings'),
            ];
        }
    }

Then in your controller, you just need to create a new Resource instance and pass the item object that you want to return:

use App\Http\Resources\Project as ProjectResource;

// some code

    /**
     * Show a single formatted resource.
     *
     * @param Project $project
     * @return ProjectResource
     */
    public function show($project)
    {
        return new ProjectResource($project);
    }

// the rest of your code

The output should be the expected.

score 1 · Answer 2 · answered Nov 15 '17 at 19:06

class Project extends Model
{
    public function rewds()
    {
        return $this->hasMany('App\Rewards');
    }

    public function shiplc()
    {
        return $this->hasManyThrough('App\Shipping', 'App\Rewards');
    }
}

class Rewards extends Model
{
    public function shiplc()
    {
        return $this->belongsToMany('App\Shipping');

    }

    public function projs()
    {
        return $this->belongsTo('App\Project');
    }

}

class Shipping extends Model
{
    public function shiplc()
    {
        return $this->belongsToMany('App\Shipping');

    }
}

Route::get('projects/{id}', function($id) {
    $p = Projects::with(['rewds', 'shiplc'])->find($id);
});

score 1 · Answer 3 · answered Nov 15 '17 at 19:10

Project.php

class Project extends Model {
    public function rewards() {
        return this->hasMany(Reward::class, 'project_id', 'id');
    }
}

Reward.php

class Reward extends Shipping {
    public function shipping(){
        return $this->belongsToMany(Shipping::class, 'reward_ship', 'reward_id', 'ship_id');
    }

    public function project(){
        return $this->belongsTo(Project::class);
    }
}

You can retrieve it like this:

$projectDetails = Project::where('id', $projectId)
                             ->with(['rewards', 'rewards.shipping'])->get();

score 1 · Accepted Answer · answered Nov 15 '17 at 19:11

You have to fix the relationships that you have :

Projects Model :

public function rewards(){
    return $this->hasMany('App\Rewards');
}

Rewards Model :

public function projects() {
    return $this->belongsTo('App\Projects');
}

public function shippings(){
    return $this->belongsToMany('App\Shipping','reward_ship', 'reward_id', 'ship_id');
}

Shipping model:

public function rewards(){
    return $this->belongsToMany('App\Rewards','reward_ship', 'ship_id', 'reward_id');
}

After that you can call the relationships in the controller to eager load the wanted elements like this :

$project = Projects::with('rewards.shippings')
                    ->where('id', $project_id)
                    ->get();

And in the view you can loop over the rewards then get the shippings like this :

@foreach ($project->rewards as $reward)
    <p>This is a reword {{ $reward->amount }}</p>
    @foreach ($reward->shippings as $shipping)
        <p>This is a shipping {{ $shipping->name }}</p>
    @endforeach 
@endforeach

Laravel models to implement one to many and many to many in single query

4 Answers4