CODEWITHSUNDEEP
phplaraveleloquentsubquery
Andrija Pavlovic
Andrija Pavlovic

Reputation: 107

Laravel Eloquent deep nested query

I'm still learning Laravel and I can't find the solution for this problem. I need to get invoices(with expenses) that are related to specific Partner Type.

I tried this:

$p = Project::with(['invoices.partner.partnerType' => function($query){
            $query->where('partnerTypeName', 'Lieferant');
        }, 'expenses'
    ])->where('id', $id)
      ->first();

I want to select invoices for Lieferant, but I get all invoices for one project.

Project Model:

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

Invoice Model

public function expenses()
{
    return $this->hasMany('App\Expense');
}
public function partner()
{
    return $this->belongsTo('App\Partner');
}

Partner Model

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

Edit: PartnerType Model

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

Edit 2: Database

Partner(partnerID, name, partnerTypeId)
PartnerType(partnerTypeId, partnerTypeName)
Project(projectID, name)
Invoice(invoiceID, name, projectID, partnerID)
Expenses(expenseID, invoiceID)

Upvotes: 0

Views: 1538

Answers (2)

Wreigh
Wreigh

Reputation: 3287

The solution to your problem is to update your query like this:

$p = Project::with(['invoices' => function($query){
        $query->with('expenses')->whereHas('partner.partnerType', function($q){
            $q->where('partnerTypeName', 'Lieferant');
        });
     }])
     ->where('id', $id)
     ->first();

But a cleaner solution would be using a scope for your problem.

In your Invoice model.

// Invoice.php
public function scopeByPartnerType($query, $partnerType)
{
    $query->whereHas('partner.partnerType', function($q) use ($partnerType) {
        $q->where('partnerTypeName', $partnerType);
    });
}

And then in your Project model, add another relation that will just get Invoices with a particular partner type.

// Project.php
public function lieferantInvoices()
{
    return $this->hasMany('App\Invoices')->byPartnerType('Lieferant');
}

Now you can do just this:

$project->find($id)->load('lieferantInvoices');

Upvotes: 0

Ariel Pepito
Ariel Pepito

Reputation: 659

If your models look like that. Should be like :

$p = Project::with(['invoices' => function($query){
            $query->where('partnerTypeName', 'Lieferant')
                  ->with(['expenses','partner' => function($q){
                        $q->with('partnerType');
                    }]);
        }])->where('id', $id)
      ->first();

 return dd($p);

Upvotes: 1

Related Questions