schwartzmj
4 weeks ago

Help using orderBy on nested relationship query

Posted 4 weeks ago by schwartzmj

I have a Livewire component that generates an HTML table where you can click a table header and sort the data. It also has a text input that you can type in to filter the results by name. I'm having issues getting the "sorting" part of it to work.

Relevant Users model with two Application model relations:

Get all applications for the user

    public function applications() // as family user, get their applications
    {
        return $this->hasMany(Application::class, 'family_id');
    }

Get the "current" (most recent) application for the user

    public function currentApplication()
    {
        return $this->hasOne(Application::class, 'family_id')->latest();
    }

Note that I'm trying to have a toggle that re-orders the Users list and not the list of applications. They're ordered by the currentApplication status (which is a number). In other words, the applications table has a status field.

The query

Here's what my query building looks like (see the lines just below the comment // Eager load current application & assign orderBy):

    public function render()
    {
        $this->authorize('viewAny', User::class);
        $user = auth()->user();

        if ($user->role === 'agency') { // Agency query
            $query = $families = $user->families();

        } elseif ($user->role === 'admin') { // Admin query
            $query = User::where('role', 'family');
        }
        // Eager load agency
        $query->with('agency');
            
        // Eager load current application & assign orderBy
        $query->with(['currentApplication' => function ($query) {
            $query->orderBy('status', $this->sortDesc ? 'desc' : 'asc');
        }]);

        // Apply any Name Filters
        if ($this->nameFilter) {
            $query->where('name', 'like', '%' . $this->escape_like($this->nameFilter) . '%');
        }

        $families = $query->paginate(10); // execute query (and paginate)
        return view('livewire.families-index', compact('families'));
    }

The results

Using Laravel Debug Bar, here's what my queries are looking like before toggling:

select * from `applications` where `applications`.`family_id` in (2, 4, 9, 10, 11, 12, 13, 14, 15, 16) and `applications`.`deleted_at` is null order by `created_at` desc, `status` asc

and after toggling:

select * from `applications` where `applications`.`family_id` in (2, 4, 9, 10, 11, 12, 13, 14, 15, 16) and `applications`.`deleted_at` is null order by `created_at` desc, `status` desc

This sorting doesn't seem to be working because I'm sorting the applications in the query, but I want to sort the families (users) based on the application.status. It doesn't look like I can ->paginate(10) to execute the query and then sortBy() the collection, because then my {{ $families->links() }} doesn't work in the Livewire component.

Please sign in or create an account to participate in this conversation.