Laravel Eloquent deep nested query

Question

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)

but I get all invoices for one project. because you are using where('id', $id) — Niklesh Raut
– Niklesh Raut, Commented Mar 12, 2018 at 3:15

Ariel Pepito · Accepted Answer · 2018-03-12 03:33:50Z

1

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);

answered Mar 12, 2018 at 3:33

Ariel Pepito

6594 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wreigh · Accepted Answer · 2018-03-12 03:56:46Z

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');

Collectives™ on Stack Overflow

Laravel Eloquent deep nested query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related