Server-side processing - data from 4 tables

culter

Hi guys, i discussed it with allan a while ago, but it was offtopic, so I'll create new question:

I need to grab data from 4 tables and display it with server-side processing (100k rows). I have working solution with 1 table which shows 7 columns in the main table and other columns are displayed when clicked on row in row details. Allan's advice was to to create view with all the columns from 4 tables, which I did. The problem is that in the second table there are several rows with additional info to every 1 row in first table, so when I display it in my main table, I see approx. 7 rows with the same id. The goal is to display only 1 unique id.

I'm thinking of 2 solutions:
1. show only unique id's in the main table, but then I don't know if the other rows from second table will be accessible
2. I don't know if it's good idea to create second serverside_processing.php with second table

Anyway, I'm afraid creating database view is useless in this case, or?

Thank you

allan

Sounds like you need to use a GROUP BY in your VIEW. Do you know what the SQL is that you need to create to get the display you want from all four tables? Or is that what the issue is (not so much the DataTables aspect of it)? It might be best to formulate a suitable SQL query first and StackOverflow would be the best place to ask about that if you aren't sure about that part.

Allan

rf1234

Since you have a 1:n relationship here I don't see another way. What you could do is to concatenate the n rows in the second (child) table to one (comma separated) string per column and record of the parent table and display them in one data table field. This would give you one row per row of the parent table. Like so:

ParentID, Further Parent Table Column(s), Comma separated string of child table values for Column 1 ... N (what ever the number of child table columns is that you want to display).

How do you create the comma separated strings?
Use getFormatters with your proprietary SQL to retrieve the child records and format them as comma separated strings and return them from the server.

Here is an example:
Parent table "report", child table "govdept" (one report can be about N govdepts). Something like this could be part of your getFormatter:

//get report govdepts
$dbh->query('SELECT DISTINCT govdept.name AS govdeptName,
                    govdept.id   AS govdeptId
               FROM govdept  
          LEFT JOIN report_has_govdept ON report_has_govdept.govdept_id = govdept.id  
          LEFT JOIN report ON report_has_govdept.report_id = report.id
              WHERE report.id = :id  
           ORDER BY 1 ASC');
$dbh->bind(':id', $reportId);
$govdeptNames = $dbh->resultsetAssoc(); // a two dimensional array is returned

$govdeptString = '';
$govdeptIdString = '';
foreach ($govdeptNames as $key => $val) {
    $govdeptString .= ( $val["govdeptName"] . "; " );
    $govdeptIdString .= ( $val["govdeptId"] . ", " );
}
if ( $govdeptString > '' ) {
    $govdeptString = substr($govdeptString, 0, -2); //cut off last comma and blank
    $govdeptIdString = substr($govdeptIdString, 0, -2); //cut off last comma and blank
}

example of getFormatter returning proprietary values in a PHP Editor instance. You can just alias a physically existing database field of your parent table and return whatever you like. In this case it is an array whith various fields in it. Use set->(false) to make sure Editor doesn't try to do updates which of course wouldn't work here. If you do it this way you won't even need a join to your child table in your Editor instance. This would all be done with your proprietary SQL.

//for non-derivative contracts we are checking whether single derivative contracts
//were assigned to them
//the array of the derivatives is displayed in the data table
Field::inst( 'contract.follow_up_days_govdept AS derivativesArray' )->set( false )
    ->getFormatter( function($val, $data, $opts) {
        return getFormatterDerivativesUnderlyingsArray
            ($data['contract.id'], $data['contract.instrument'], 'getDerivatives');
}),

As Allan pointed out GROUP BY is also an option. But only if you want to aggregate the child records (e.g. SUM them etc.). If you want to display the individual values of the child records GROUP BY isn't an option I am afraid.

culter

@Allan, I tested the view. It has 150k rows and 20 columns (the table with unique id has 30k rows and 15 columns) and the performance drop is really big since I switched from table to view. Even in the phpmyadmin the sorting takes 0.001s in the table and 20s in the view. But as you noticed, it's not the problem of DataTables. I will ask on SO.

@rf1234, Yes, I need to display the individual values, not SUM or something like that. I will try it tomorrow, thank you for your effort.

Just one question I have about this. Isn't it better to create separate query to get the data from the table based on the id of row? I need to display them after I click on a button. Is this a possible way or not?

Thank you very much.

allan

What if you add a LIMIT 10 to the statement? Is that significantly faster? If so, use server-side processing.

Isn't it better to create separate query to get the data from the table based on the id of row? I need to display them after I click on a button. Is this a possible way or not?

Yes. If you don't need to show the data up front, absolutely deferring it for later makes perfect sense. Use something like that shown in this example.

Allan