1.10 and server-side table joins

mihomes

Just wondering if anyone has joined tables with 1.10 and server-side. I have come across a situation where I need to join two tables and modifying the default serverside example to do so is a bit beyond the scope of what I know right now. Would love to see an example if anyone has done so.

allan

I don't have an example of the new scripts doing a join, but this is based on the old script http://datatables.net/dev/server_processing.txt .

You can drop 1.10 in to the legacy mode by using sAjaxSource or `$.fn.dataTable.ext.legacy.ajax = true;` http://next.datatables.net/manual/server-side#Legacy

Allan

mihomes

Thanks for the link Allan. I'd like to stick with the pdo so maybe with some trial and error I can get it working. I may post back if I get stuck with something.

mihomes

Well, I got nowhere with this using the old code as an example... if anyone cares to take a look it would be appreciated. I am getting a 'base table or view not found' error.

Note that I slightly modified the original script and pass a $myWhere variable which allows me to set custom 'WHERE conditions'. This modification works as expected. I also include my db connections details in a separate file which works of course as well.

the processing php

[code]
<?php
// create condition so logs are shown for active users of active computers only
function active_users_condition($db, $account_id)
{
//get active computers
$active_users = get_active_users($db, $account_id);

//computer_name = 'number1' OR computer_name = 'number2'....

$where_string = array();
foreach($active_users as $key)
{
$where_string[] = "user.user_id = '".$key['user_id']."'";
}
$where_string = implode(" OR ", $where_string);

return $where_string;
}

$where_string = active_users_condition($db, $_SESSION['user']['account_id']);


// DB table to use
$table = 'users, security';

// Table's primary key
$primaryKey = 'users.user_id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'security.settings_id', 'dt' => 'settings_id' ),
array( 'db' => 'security.user_id', 'dt' => 'user_id' ),
array( 'db' => 'users.username', 'dt' => 'username' ),
array( 'db' => 'users.computer_name', 'dt' => 'computer_name' ),
array( 'db' => 'security.disable_desktop', 'dt' => 'disable_desktop' ),
array( 'db' => 'security.disable_start', 'dt' => 'disable_start' ),
array( 'db' => 'security.disable_shutdown', 'dt' => 'disable_shutdown' ),
array( 'db' => 'security.disable_run', 'dt' => 'disable_run' ),
array( 'db' => 'security.disable_mouse', 'dt' => 'disable_mouse' ),
array( 'db' => 'security.disable_bootkeys', 'dt' => 'disable_bootkeys' ),
array( 'db' => 'security.disable_cp', 'dt' => 'disable_cp' ),
array( 'db' => 'security.disable_network', 'dt' => 'disable_network' ),
array( 'db' => 'security.disable_taskbar', 'dt' => 'disable_taskbar' ),
array( 'db' => 'security.disable_clock', 'dt' => 'disable_clock' ),
array( 'db' => 'security.disable_logoff', 'dt' => 'disable_logoff' ),
array( 'db' => 'security.disable_startchange', 'dt' => 'disable_startchange' ),
array( 'db' => 'security.disable_taskman', 'dt' => 'disable_taskman' ),
array( 'db' => 'security.disable_clipboard', 'dt' => 'disable_clipboard' ),
array( 'db' => 'security.disable_drives', 'dt' => 'disable_drives' )
);

echo json_encode(
SSP::simple( $_GET, $db, $table, $primaryKey, $columns, "users.user_id = security.user_id AND (".$where_string.")")
//SSP::simple( $_GET, $db, $table, $primaryKey, $columns, "" )
//$myWhere = "" when empty

);

?>
[/code]

mihomes

I was able to fix the issue...

in the processing :

[code]
// DB table to use
$table = '`users` LEFT JOIN `security` USING (user_id)';
[/code]

and in ssp.class.php all instances of `$table` (two of them) need to be replaced with just $table. Note that in your processing you are including the backticks in the variable.

I am having problems with multiple column sorting and searching on using joined tables though... any ideas?

mihomes

Back to square one... I found the need to set aliases for the columns and cannot get that working. For instance if you join two tables and both have a user_id you want to select from which table you use it. Anyone else joining tables with 1.10 yet?

Here is what I am trying to accomplish...

[code]
$table = 'users AS u JOIN security AS s ON u.user_id = s.user_id';

// Table's primary key
$primaryKey = 'u.user_id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 's.settings_id', 'dt' => 'settings_id' ),
array( 'db' => 'u.user_id', 'dt' => 'user_id' ),
array( 'db' => 'u.username', 'dt' => 'username' ),
array( 'db' => 'u.computer_name', 'dt' => 'computer_name' ),
array( 'db' => 's.disable_desktop', 'dt' => 'disable_desktop' ),
array( 'db' => '.disable_start', 'dt' => 'disable_start' ),
array( 'db' => 's.disable_shutdown', 'dt' => 'disable_shutdown' ),
array( 'db' => 's.disable_run', 'dt' => 'disable_run' ),
array( 'db' => 's.disable_mouse', 'dt' => 'disable_mouse' ),
array( 'db' => 's.disable_bootkeys', 'dt' => 'disable_bootkeys' ),
array( 'db' => 's.disable_cp', 'dt' => 'disable_cp' ),
array( 'db' => 's.disable_network', 'dt' => 'disable_network' ),
array( 'db' => 's.disable_taskbar', 'dt' => 'disable_taskbar' ),
array( 'db' => 's.disable_clock', 'dt' => 'disable_clock' ),
array( 'db' => 's.disable_logoff', 'dt' => 'disable_logoff' ),
array( 'db' => 's.disable_startchange', 'dt' => 'disable_startchange' ),
array( 'db' => 's.disable_taskman', 'dt' => 'disable_taskman' ),
array( 'db' => 's.disable_clipboard', 'dt' => 'disable_clipboard' ),
array( 'db' => 's.disable_drives', 'dt' => 'disable_drives' )
);

echo json_encode(
SSP::simple( $_GET, $db, $table, $primaryKey, $columns)
);
[/code]

mihomes

I've posted my current state of trying this on stackoverflow... Allan I hope you do not mind me listing the link as it allows me to enter all the current code. http://stackoverflow.com/questions/21691141/datatables-1-10-serverside-with-table-joins-returning-null-value-responses

Current status of this is no error from firebug, however, all columns are returned as 'null' so the table shows with empty columns in its rows. draw, recordsTotal, and recordsFiltered are being returned properly. Searching also appears to work - since I know what the values in the empty columns 'should' be I can search for terms and it correctly redraws the table to only show those rows... albeit they are empty of course. Loading the table shows 5 rows of empty columns... I search for say 'joe' which only appears in one column of one row and the table redraws to show one row of empty columns and updates the 'showing' and 'filtered from' results correctly.

Seems like this is something easy I am missing so if anyone cares to look that would be great. I am sure this would be useful to many once working properly.

mihomes

I seem to have managed to find a solution to this, however, it looks kind of 'hacky'. Everything is working and returning properly though using either name or index format :

[code]
array( 'db' => '`security`.`settings_id`', 'dt' => 'settings_id' ),

or

array( 'db' => '`security`.`settings_id`', 'dt' => 5),
[/code]

Allan, would you be interested in taking a look at this as I am sure it can be cleaned up? This could then be another option to include for those needing table joins and where conditions on the data being requested. I actually have it setup in a way that it works without table joins or where conditions as well you just define them as empty "" if not using them.

allan

Nicely done. I'll try to take a look in the next few weeks, but I'm already fairly tied up with current development work and support.

Allan