Using server side rendering with data from multiple tables

Noah Corlew

I have a table with multiple foreign keys for data from other tables that I wish to display in a single datatable. The datasets I am using are huge, so server side rendering is a requirement.

I am able to display this data, and I am able to sort by data that is in the table I am directly creating the datatable from (like the local id of the row). However in any column where I am relying on a foreign key, where I am getting my data through another table in the database, sorting does not work.

I am using Laravel 7 as the main framework for the site, and am currently using eloquent models with attributes and relationships to retrieve the data I wish to display through the foreign keys.

Here is a simplified example of my setup:

I have my Main Table with an id, and the foreign keys user_id and site_id

My User table has a column user_name, and my Site table has a column site_name

My Main Table model has relationships on user and site

class MainTable extends Model
{
   ...

   public function user()
    {
        return $this->hasOne('App\Models\User', 'id', 'user_id');
    }

   public function site()
    {
        return $this->hasOne('App\Models\Site', 'id', 'user_id');
    }
}

and my user and site models have the attributes I am attempting to show in the datatable.

Where I initialize my datatable, I use the model relations to set the data of the column.

When I attempt to sort with data from a foreign table, I get an error implying that datatables is searching for a column on my main table with the name of the relation on my Main Table model, which does not exist.

Without duplicating data in my main table, how can I fix this issue?

allan

It sounds like you are using server-side processing (serverSide) and that the server-side script is not coping with DataTables asking for a sort on joined table.

What I'm not quite clear on, is your DataTable basically showing the main table left joined with the site and user tables? If so, our Editor PHP libraries will work with the left join and server-side processing (the PHP libs for Editor are MIT licensed).

Allan