Backend Development

How to order by a related model in Laravel

Once in a while, or quite often, you may want to order results in an eloquent collection by a related model. For me, this turned out to be a bit of a challenge, so I wanted to share how I decided to do it.

I'm building an application that display's customer orders. I want my users to be able to order orders by Customer name if they would like. In order to do this, I added a select statement to the query when the request included an orderBy="customer" key and value.
The query looks like this:

$query->addSelect(['customer_name' => Customer::select('name')
                        ->whereColumn('id', 'orders.customer_id')])->orderBy('customer_name', $request->orderByDirection);

I'm adding an attribute to the collection of orders named "customer_name", so that I can orderBy that attribute.

For me, this worked the best, as on Stack overflow and other places, a lot of people do this by doing a SQL JOIN statement. The problem comes when you also want to search by customer name, and have already joined the tables, and the join statement raises an integrity constraint violation saying the id column is ambiguous.

Here is a complete implementation of my OrderController index method with search included:

public function index(Request $request)
        $orders = auth()->user()->currentTeam->orders()
           //Only search when the request includes the search key.
            ->when($request->search, function ($query) use ($request) {
                // This orWhereHas() method joins the customers table and the orders table, causing the JOIN method when ordering to 
                // throw a constraint violation.
                $query->where('id', $request->search)->orWhereHas('customer', function ($query) use ($request) {
                    $query->where('name', 'like', "%{$request->search}%");
            ->when($request->orderBy, function ($query) use ($request) {
                if ($request->orderBy == 'customer') {
                     // here is where I'm selecting the customer for the orders and adding the name as an attribute..
                    $query->addSelect(['customer_name' => Customer::select('name')
                        ->whereColumn('id', 'orders.customer_id')])->orderBy('customer_name', $request->orderByDirection);
                } else {
                    $query->orderBy($request->orderBy, $request->orderByDirection);
            }, function ($query) {
                $query->orderBy('id', 'desc');
        return inertia('Orders/Index', ['orders' => $orders, 'filters' => $request->only(['search', 'orderBy', 'orderByDirection'])]);

Now, I'm going to work on refactoring this to make it a little easier on the eyes. (:

Chris Wray website logo

Trying to do my part in making the world a better place.

This site is hosted for free, so I am thanking the services I am using by adding a link to their websites here in the footer.

© 2020 Chris Wray. All rights reserved.