In Server-Side processing unable to search and paginate for data from diff db tables

In Server-Side processing unable to search and paginate for data from diff db tables

bchiprebchipre Posts: 30Questions: 10Answers: 0

Hello @allan
We are using Server-Side processing for our table. The data in the table is being called from 3 different mysql tables.
1. We are in a position where we cannot search the user request through general search and column filter search for the data displayed from a different table. General search & Column Filter Search works only from a single table.
Ex: Unable to search "Receivable" and "Receivable Advance" as the data is arriving from a different table.
2. The table rows displayed shows all the 13 entries irrespective of "show 10 entries.". So pagination isn't working for us.

Can you help us to know where we are wrong?_ Unable to create a test case by having different tables._
Attaching a screenshot and the script for your reference.

Answers

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    edited January 2022

    With server side processing enabled your server script is responsible for sorting, searching and paging. That is the place to look Are you using a Datatables supplied server side processing script?

    Not sure if this thread is relevant to your issue but hope it helps.

    Kevin

  • bchiprebchipre Posts: 30Questions: 10Answers: 0

    @kthorngren Tried all the possibilities, but no success.

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    That sounds like it's going to be a fiddly script to filter from three different sources, but as Kevin said it'll be that script on the server that needs to handle the filtering.

    The protocol is discussed here. Also see examples here. If you download the DataTables repo, there are examples of the server-side scripts in /examples/server_side/scripts,

    Cheers,

    Colin

  • bchiprebchipre Posts: 30Questions: 10Answers: 0

    We were able to fix the Pagination issue ourselves. But when the data is arriving from 3 different MySQL tables, the column filter fails.

  • bchiprebchipre Posts: 30Questions: 10Answers: 0
    edited February 2022

    We were able to fix the Pagination issue ourselves. But when the data is arriving from 3 different MySQL tables, the column filter fails.! And more over the search text get automatically copied to the global search at the top.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    What do you mean "the data is arriving from 3 different MySQL tables"? Presumably you are using a join statement in the SQL to combine all three tables into a single select?

    Perhaps you can show me what cashentry_serverdata.php contains?

    Allan

  • bchiprebchipre Posts: 30Questions: 10Answers: 0

    Here is the cashentry_serverdata.php file attached. We are not using any join statement for SQL.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    It looks to me that you have a lot of work to do there if you want to use server-side processing! How many rows of data are you going to be working with. Obviously you are down at least one query per row, so you'll want to keep the number of rows down - what that the idea of using server-side processing?

    Combining sorting, filtering and paging with that structure I suspect will not be trivial! If it is possible to combine it all together into a single query, I would strongly recommend that.

    Allan

  • bchiprebchipre Posts: 30Questions: 10Answers: 0

    @allan thanks for the review. Will surly work on that and get back to you ASAP.

Sign In or Register to comment.