Searching an accessor of eager-loaded relation from datatables

Question

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->name@$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?

Did you ever find a solution to this? Running into the same issue. I am not using a relation, but still, not able to search on an accessor field. — waterloomatt, Feb 05 '20 at 01:18
@waterloomatt Yes I did get it working, just about a year after I asked the question. See my answer below. — miken32, Feb 05 '20 at 18:27

miken32 · Accepted Answer · 2021-10-01T17:21:31.713

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.

score 0 · Answer 2 · answered Nov 20 '18 at 19:09

0

Try appending the accesor to the model.

class Department extends Model {

    protected $appends = ['email'];

    // the rest of your code
}

Note: Attributes in the appends array will also respect the visible and hidden settings configured on the model.

Source: Appending Values To JSON

answered Nov 20 '18 at 19:09

Kenny Horna

13,485
4
44
71

It's already appended; as I said, the email shows up fine – it's just not searchable because of how Laravel Datatables treats it. – miken32 Nov 20 '18 at 19:11

Searching an accessor of eager-loaded relation from datatables

2 Answers2