[server-side] Is it possible to perform a search on a single column that's an array of objects?

[server-side] Is it possible to perform a search on a single column that's an array of objects?

jelenamiojelenamio Posts: 3Questions: 1Answers: 1
edited October 2017 in Free community support

I'm using serverSide: true;
JSON for Datatables has this structure:

[{"id": 1, "username": "jd", "firstname": "John", "lastname": "Doe", "emails": [ {"id": 1, "user_id": 1, "email":"johndoe@company.com", }, { "id": 2, "user_id": 1, "email": "jdpersonal@gmail.io"} ]}, ...]

Notice how "emails" is an array of objects; In the table, this is presented as [object Object],[object Object], so I have to use editColumn to render it properly which is fine (I do a foreach, and then just display emails one by one, with line breaks in between); however, the search for some reason doesn't work. If I try to search by email, e.g. I type in the search field "jdpersonal" or even just @, it says that there are no results. How can I filter the results by "emails" column when using server-side?
Thanks

This question has an accepted answers - jump to answer

Answers

  • jelenamiojelenamio Posts: 3Questions: 1Answers: 1

    If this makes any difference:

    There are two tables, let's call them users and users_emails;

    Query:
    $builder = Users::with('emails')->select('id', 'username', 'firstname', 'lastname');

    This is how I get emails:
    public function emails() { return $this->hasMany('App\Models\UsersEmail', 'users_id'); }

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    I'm not sure what code you are using on the server-side, but, if you are using server-side processing then it is up to the server-side script to do the search of the data. Doing a search over an inner one-to-many join is not something I've done before, but it should probably be possible. It might require a fairly complex SQL statement, and I'm afraid I'm no SQL master.

    Allan

  • jelenamiojelenamio Posts: 3Questions: 1Answers: 1
    Answer ✓

    I figured it out. When I used addColumn and editColumn in my controller (php), the search didn't work. But when I removed all that and only did this in controller:

    $builder = //get stuff from db;
    return $datatables->eloquent($builder)->make(true);

    then in my blade view (view.blade.php) I've added "columns" array in DataTables init section and just defined everything there, something like this:

    "columns": [ {data: "id", name: "users.id"}, ... , {data: "users_emails", name: "users_emails.email", render: function(...) { } }, ... ]

    I did that for every column, and just performed necessary edit/add stuff in render functions. Hope this helps someone.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Nice one - thanks for sharing your solution with us.

    Allan

This discussion has been closed.