Laravel Eloquent has many through variation, remove duplicates

Question

I've been trying to get a variation of the Laravel Eloquent 'has many through' relation working.

I have 3 models: Invoice, InvoiceLine and Order; I would like to retrieve a distinct set of all Orders of a given Invoice through its InvoiceLines.

Invoice has 1 or more InvoiceLines; InvoiceLine has exactly 1 Invoice; InvoiceLine has zero or 1 Order; Order has zero or more InvoiceLines

Below is a simplified version of the tables:

• Invoice
  • id
• InvoiceLine
  • invoice_id
  • orderid
• Order
  • orderid

First I thought to use the default Has Many Through relationship but that won't work because my tables are different:

Then I thought to use the Many-To-Many (belongsToMany) relation:

class Invoice {
    public function orders(): BelongsToMany {
        return $this->belongsToMany(
            Order::class,
            'invoice_line',
            'invoice_id',
            'orderid'
        );
    }
}

The above code works for getting all orders, but the list is full of duplicates.

How can I setup this Invoice --> Order relation using Laravel 5.6 Eloquent ORM but have it so that the collection of Orders does not have any duplicates?

If this is not possible in a Eloquent ORM relationship definition, then how can I query these models in a different way so that I still have a relational object oriented collection of Invoices with its distinct set of related Orders?

Answer 1

Not sure if this can be done in Eloquent builder. Maybe something like this:

public function orders(): BelongsToMany {
    return $this->belongsToMany(
        Order::class,
        'invoice_line',
        'invoice_id',
        'orderid'
    )->distinct();
}

But you can also do:

// class Invoice
public function getRelatedOrders ()
{
    $orderIdList = $this->invoiceLines->pluck('order_id')->unique();
    return Order::whereIn('id', $orderIdList)->get();
{