Datatable Server Side with JOIN

Question

Good night!

I started using Datatable recently, I’m using with the server-side feature. I need to relate 2 tables with INNER JOIN, I saw that there is a "difficulty" in doing this type of procedure in the case of the server-side. I even found a custom ssp.class.php, but I didn’t quite understand how to use it...

Would anyone have a tutorial to share about it? Or some example?

Answer 1

When using the server-side option, you will have to adapt your server-side code to receive and return the values in a structure defined by the Datatables requests.

Datatables sends data with this structure more or less to the server:

• start: if you are using paging, you will have to return only the table rows that will be displayed, then start is the bottom bound of the search in the database
• length: from start, how many lines forward I will return, ie the number of items that will be displayed on a page
• draw: a control number that serves Datatables to know which page it is on, you will just return it back
• order: an array with columns and direction (ascending, descending) that you will use in the query to sort the table
• search: an array with values that you will use to search in the table, i.e., the WHERE of the query
• Columns: the table columns

In addition, Datatables also expects a response with this structure:

• draw: page control value for Datatables
• recordsTotal: total number of rows in your table in the unfiltered seat (a Count(*) without WHERE)
• recordsFiltered: total number of lines of the query with filters. If no filter has been made (search for specific values), then it will be the same as the recordsTotal
• date: an array of keys and values with the return of your query.

You will return all this in a JSON. JSON may have other data as required, but these data are required for the table to work correctly with paging and everything else.

Knowing this structure, now you have to assemble your queries using start, length, search, Sort, etc. The fact that you do a JOIN in your search makes no difference in this case, it works with any query. I suggest you see the code of that page, it shows an example of how to do this with Mysql, using the structure I mentioned, in a simpler way than the ssp.class.php that you quoted. This video on Youtube also explains quite clearly. Good luck!