Combining paginate and joined table

[mwebsites94]

Hello everybody

I have an invoice model which is related to another model (addresses) in which the address and name of the person the invoice is sent to are stated. I create an overview and want to sort by a couple of things, such as date, invoice number and name of the addressed person. The relation is defined trough a hasOne function.

I have tried two solutions in which different problems occur

option 1 invoicecontroller.php:

$invoices = Auth::user()->invoice()->with(['address'])
->join('addresses', 'invoices.id', '=', 'addresses.invoice_id')
->sortable()
->paginate(25);

return view('admin.invoices.overview', compact('invoices'));

In this everyhting except the sorting by name. This does nothing.

Another option is this:

invoicecontroller.php

$invoices = Auth::user()->invoice()->sortable(['invoice_date' => 'desc'])->paginate(25);
return view('admin.invoices.overview', compact('invoices'));

Invoice.php

public function nameSortable($query, $direction){
  return $query->join('addresses', 'invoices.id', '=', 'addresses.invoice_id')
  ->orderBy('name', $direction);
}

In this I can sort by anything but it doesnt show the name. When I sort by name the names show up again. A workaround is to print the name differently, namely $invoice->address->name instead of $invoice->name, but than I can't sort by name anymore.

Does anybody know a workaround?

[Kyslik]

This looks like an edge case I did not think about :(.

Can you post SQLs for both cases? You can get the SQLs from https://github.com/barryvdh/laravel-debugbar.