Managing Cross-Table Sorting in Laravel Nova
Introduction
Laravel Nova, the elegantly designed administrative dashboard developed by Laravel itself, is known for its vast spectrum of features and its intuitive interface. From managing resources to customizing actions, Nova provides a sophisticated yet straightforward approach to data administration.
However, as comprehensive as Laravel Nova may be, like any other technology, it doesn't always cover every nuanced need out of the box. One such challenge that developers often face is the implementation of cross-table sorting, which will be discussed in this article in detail.
Setting Up The Scene
To illustrate this workaround, let's consider a web application that allows users to connect their accounts. The database for this application has two tables: users and accounts, which are linked via the user_id foreign key on accounts:
To fill these tables with some test data, I've created two factories:
and implemented them via a seeder:
After running the seeder, in the Nova admin panel the users’ data will look like this:
Adding A Column That indicates The Number Of Connected Accounts
To start, we can add a column to the users table that indicates the number of connected accounts. This can be done by declaring an append property on the User model:
We can then update the User resource file to include the number_of_accounts field:
Sorting By The Count Of Rows Related To The Entity
If we try to make that field sortable, it will throw an error saying that the column doesn’t exist, which is true as there’s no such column in users. A workaround to this is to override the indexQuery method and handle the sorting case by checking whether the orderBy and orderByDirection parameters are passed or not:
First we make sure to remove order clauses from the query, then manually join the accounts table and apply sorting in the SQL query.
Sorting By SUM/AVG
Using the same logic, we can write similar queries for the SUM/AVG of the values of the column balance and append these columns to users:
with the following translation to the builder language:
References