Ad

Searching An Accessor Of Eager-loaded Relation From Datatables

I've got the following models:

<?php
class User extends Model {
    public function department() {
        return $this->hasOne(Department::class);
    }
}

class Department extends Model {
    protected $appends = ["email"];
    public function getEmailAttribute() {
        return "$this->[email protected]$this->domain";
    }
    public function user() {
        return $this->belongsTo(User::class);
    }
}

I'm pulling a list of users, including their departments, and showing this (using the Laravel DataTables package) in a datatable with server-side pagination/sorting/searching:

<?php
class UserController extends Controller {
    public function dt() {
        $users = User::with("department")
            ->where("location_id", session("current_location"));
        return DataTables::of($users)->make();
    }
}

Within the datatables setup, one of my columns is defined as follows:

{data: "department.email"}

This displays the email accessor property without issue. The problem comes when I try to search, or sort based on this column:

DataTables warning: table id=DataTables_Table_0 - Exception Message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'departments.email' in 'where clause'

Obviously, datatables isn't aware that this is an accessor, and tries to include it in the query – with predictable results.

The only workaround I could find reference to is using the filterColumn method, which allows you to define a custom WHERE clause for a specific column. But as far as I can tell this a) requires you to define the column manually with a query builder, and b) only works on the model directly, not one of its relations.

Is there any way that I can search and sort this accessor property the same way I can with the "real" properties of the relation?

Ad

Answer

So here's how I ended up solving this. It's not an ideal solution, but basically I recreated the accessor in SQL, manually building the query, and then used Datatables' filterColumn functionality.

<?php
class UserController extends Controller {
    public function dt() {
        $concat = "CONCAT(departments.name, '@', departments.domain)";

        $users = User::select(["users.*", DB::raw("$concat AS dept_email")])
            ->leftJoin("departments", "users.department_id", "=", "departments.id")
            ->whereNull("departments.deleted_at")
            ->where("location_id", session("current_location"))
            ->with("departments");

        return DataTables::of($users)
            ->filterColumn(
                "dept_email",
                fn ($q, $k) => $q->whereRaw("$concat LIKE ?", ["%$k%"]);
            )
            ->make();
    }
}

Then I just included the generated column in my table definition and searches work as expected.

Ad
source: stackoverflow.com
Ad