Datatable query using name rather than username

Question

I have a project where we don't store the name of a user and just use their username for everything.

I cam across a package called Ticketit which is a helpdesk ticketing system for Laravel.

The package uses the name of users for everything and so this caused a few errors. I have a getNameAttribute() accessor on my User model and so for the most part this satisfies the package, however there were some places that explicitly called name in Eloquent ::lists() queries.

For these I manually replaced name with username inside my own fork of this repo, and have this linked up to my project.

The datatable loads as expected on the page, but when I try to sort by any of the other columns or run a search inside it I get 500 errors in my Network tab of developer tools.

Previewing the response shows this:

QueryException in Connection.php line 662: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.name' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as row_count from ticketit inner join users on users.id = ticketit.user_id inner join ticketit_statuses on ticketit_statuses.id = ticketit.status_id inner join ticketit_priorities on ticketit_priorities.id = ticketit.priority_id inner join ticketit_categories on ticketit_categories.id = ticketit.category_id where completed_at is null and (LOWER(ticketit.id) LIKE %%h%% or LOWER(subject) LIKE %%h%% or LOWER(ticketit_statuses.name) LIKE %%h%% or LOWER(ticketit.updated_at) LIKE %%h%% or LOWER(users.name) LIKE %%h%% or LOWER(ticketit_priorities.name) LIKE %%h%% or LOWER(users.name) LIKE %%h%% or LOWER(ticketit_categories.name) LIKE %%h%%)) count_row_table)

Following the route this posts to, I get to TicketController@data. In the original package this is:

public function data(Datatables $datatables, $complete = false)
    {
        $user = $this->agent->find(auth()->user()->id);
        if ($user->isAdmin()) {
            if ($complete) {
                $collection = Ticket::complete();
            } else {
                $collection = Ticket::active();
            }
        } elseif ($user->isAgent()) {
            if ($complete) {
                $collection = Ticket::complete()->agentUserTickets($user->id);
            } else {
                $collection = Ticket::active()->agentUserTickets($user->id);
            }
        } else {
            if ($complete) {
                $collection = Ticket::userTickets($user->id)->complete();
            } else {
                $collection = Ticket::userTickets($user->id)->active();
            }
        }
        $collection
            ->join('users', 'users.id', '=', 'ticketit.user_id')
            ->join('ticketit_statuses', 'ticketit_statuses.id', '=', 'ticketit.status_id')
            ->join('ticketit_priorities', 'ticketit_priorities.id', '=', 'ticketit.priority_id')
            ->join('ticketit_categories', 'ticketit_categories.id', '=', 'ticketit.category_id')
            ->select([
                'ticketit.id',
                'ticketit.subject AS subject',
                'ticketit_statuses.name AS status',
                'ticketit_statuses.color AS color_status',
                'ticketit_priorities.color AS color_priority',
                'ticketit_categories.color AS color_category',
                'ticketit.id AS agent',
                'ticketit.updated_at AS updated_at',
                'ticketit_priorities.name AS priority',
                'users.name AS owner',
                'ticketit.agent_id',
                'ticketit_categories.name AS category',
            ]);
        $collection = $datatables->of($collection);
        $this->renderTicketTable($collection);
        $collection->editColumn('updated_at', '{!! \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", $updated_at)->diffForHumans() !!}');
        return $collection->make(true);
    }

Which I have edited to this in my fork:

public function data(Datatables $datatables, $complete = false)
    {
        $user = $this->agent->find(auth()->user()->id);
        if ($user->isAdmin()) {
            if ($complete) {
                $collection = Ticket::complete();
            } else {
                $collection = Ticket::active();
            }
        } elseif ($user->isAgent()) {
            if ($complete) {
                $collection = Ticket::complete()->agentUserTickets($user->id);
            } else {
                $collection = Ticket::active()->agentUserTickets($user->id);
            }
        } else {
            if ($complete) {
                $collection = Ticket::userTickets($user->id)->complete();
            } else {
                $collection = Ticket::userTickets($user->id)->active();
            }
        }
        $collection
            ->join('users', 'users.id', '=', 'ticketit.user_id')
            ->join('ticketit_statuses', 'ticketit_statuses.id', '=', 'ticketit.status_id')
            ->join('ticketit_priorities', 'ticketit_priorities.id', '=', 'ticketit.priority_id')
            ->join('ticketit_categories', 'ticketit_categories.id', '=', 'ticketit.category_id')
            ->select([
                'ticketit.id',
                'ticketit.subject AS subject',
                'ticketit_statuses.name AS status',
                'ticketit_statuses.color AS color_status',
                'ticketit_priorities.color AS color_priority',
                'ticketit_categories.color AS color_category',
                'ticketit.id AS agent',
                'ticketit.updated_at AS updated_at',
                'ticketit_priorities.name AS priority',
                'users.username AS owner',
                'ticketit.agent_id',
                'ticketit_categories.name AS category',
            ]);
        $collection = $datatables->of($collection);
        $this->renderTicketTable($collection);
        $collection->editColumn('updated_at', '{!! \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", $updated_at)->diffForHumans() !!}');
        return $collection->make(true);
    }

With this I have changed the users.name to users.username, but this isn't fixing the issue for me.

Can anyone help me figure out why, or what else I need to change as I haven't had any luck figuring out where else I need to change this.

Answer 1

You'll drive yourself nuts trying to edit all the places where the 3rd-party code tries to access name. My suggestion is to fix at the root rather than patch in many places:

1. Create a new name column in users table
2. Fill that column with the values in the username column

eg queries:

ALTER TABLE `users` ADD COLUMN `name` VARCHAR(30) NOT NULL AFTER `username`; 
UPDATE `users` SET `name` = `username`;

Now your DB will have the schema that your plugin expects.

Answer 2

I found the issue, it was in the initialisation of the DataTable where it was calling users.name rather than users.username.

After updating this, clearing the views cache php artisan view:clear it all worked fine!