How to join two tables with ssp.class.php
As PaulF pointed out, you need to use JOIN or sub-query to retrieve father's name from the same table.
I assume you're using ssp.class.php to process your data on the server-side based on the example you've mentioned.
Class ssp.class.php doesn't support joins and sub-queries, but there is a workaround. The trick is to use sub-query as shown below in $table definition. Replace table with your actual table name in the sub-query.
$table = <<<EOT
(
SELECT
a.id,
a.name,
a.father_id,
b.name AS father_name
FROM table a
LEFT JOIN table b ON a.father_id = b.id
) temp
EOT;
$primaryKey = 'id';
$columns = array(
array( 'db' => 'id', 'dt' => 0 ),
array( 'db' => 'name', 'dt' => 1 ),
array( 'db' => 'father_id', 'dt' => 2 ),
array( 'db' => 'father_name', 'dt' => 3 )
);
$sql_details = array(
'user' => '',
'pass' => '',
'db' => '',
'host' => ''
);
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
You also need to edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks.
Make sure all column names are unique otherwise use AS to assign an alias.
NOTES
There is also github.com/emran/ssp repository that contains enhanced ssp.class.php supporting JOINs.
LINKS
See jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php for more information.
Datatables + PHP: Server-Side Processing on Multiple Tables
TL;DR: I ended up using a modification of the original Datatables ssp.class.php called ssp.php implemented by Emran Ul Hadi: https://github.com/emran/ssp
His modification accepts JOIN, WHERE, GROUP BY and column aliases. Although the file hasn't been updated in over a year, it still works with DataTables 1.12.x. I made some modifications to his version that increases its robustness and improves the documentation with clearer examples.
Will post my mods/updates here when I have a bit more time. Eventually I hope to put in a pull-request to get my updates into his repository.
Datatables server-side PHP using SSP class - how to group after join
The correct solution is:
<?php
$table = <<<EOT
(
SELECT
a.id,
a.pr_template_id,
a.title as producttitle,
b.pr_template_id,
b.category_id,
string_agg(DISTINCT c.title, ', ') as cattitle
FROM products_table a
LEFT JOIN product_categories_template_relations b ON a.pr_template_id = b.pr_template_id
LEFT JOIN product_categories c ON b.category_id = c.category_id
group by a.id, b.pr_template_id
) temp
EOT;
?>
Adding GROUP BY support to the ssp class in complex function
two suggestion
1 ) in this code you should use string concatenation for $where
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table` " . $where
);
2 ) seems you have order by limit and offset for pagination after group by (and group by id DESC is wrong)
$where = "recipient='".$recipient."' AND grouped=''
GROUP BY id ORDER BY id DESC LIMIT 10 OFFSET 5";
How to use database query with JOIN in server-side processing mode
There is a trick to use JOIN without modifying the code too much.
Change this line:
$sTable = "customers";
to:
$sTable =
"(
SELECT a.id AS crmid, b.name
FROM forms a
INNER JOIN users b ON a.agent_id = b.id
) table";
I simplified the query above only for the sake of code clarity. Just make sure that all column names are unique, otherwise use aliases where needed.
Then use column names/aliases in $aColumns variable. For the query above it wold be
$aColumns = array('crmid', 'name');
Serverside datatable combining column data and data from other tables
In order to achieve what I needed, I made some changes in the Datatable inizialization script and in the ss-books-get controller.
Datatable Inizialization:
$(document).ready(function() {
$('#books').DataTable( {
dom: "<'row'<'col-md-4'B><'col-md-4'f><'col-md-4'p>>" +
"<'row'<'col-md-6'><'col-md-6'>>" +
"<'row'<'col-md-12't>><'row'<'col-md-4'l><'col-md-4'i><'col-md-4'p>>",
buttons: [
{
extend: 'collection',
text: '<i class="la la-download"></i> Export',
autoClose: true,
className: 'btn btn-success btn-icon-sm btn-square dropdown-toggle',
buttons: [
{ text: '<i class="fas fa-copy"></i>\xa0\xa0 Copy', extend: 'copyHtml5'},
{ text: '<i class="fas fa-file-excel"></i>\xa0\xa0 Excel', extend: 'excelHtml5'},
{ text: '<i class="fas fa-file-csv"></i>\xa0\xa0 CSV', extend: 'csvHtml5'},
{ text: '<i class="fas fa-file-pdf"></i>\xa0\xa0 PDF', extend: 'pdfHtml5'},
{ text: '<i class="fas fa-print"></i>\xa0\xa0 Print', extend: 'print' }
],
fade: true,
}
],
"columnDefs": [
// Use render to mix the title column and the subtitle column and to add the HTML tags. Also generate de buttons grabbing the ID from the id column of the table.
{
"render": function ( data, type, row ) {
if (row[8] == "") {
var title = data;
} else {
var title = data + ' | ' + row[8];
}
return '<a class="kt-link kt-font-bold" href="./book/'+row[7]+'" >' + title + '</a>';
},
"targets": 0
},
{
"render": function ( data, type, row ) {
return '<a href="./book/'+row[7]+'" class="btn btn-sm btn-clean btn-icon btn-icon-md" title="View Book"> <i class="fas fa-eye"></i> </a> <a href="./book/'+row[7]+'#add-copy" class="btn btn-sm btn-clean btn-icon btn-icon-md" title="Add Copy"> <i class="fas fa-plus"></i> </a> <a href="./book/'+row[7]+'#edit-book" class="btn btn-sm btn-clean btn-icon btn-icon-md" title="Edit Book"> <i class="fas fa-edit"></i> </a> <a href="./index.php?action=book-del&id='+row[7]+'" id="btn" class="btn btn-sm btn-clean btn-icon btn-icon-md btn-book-del" title="Delete Book"> <i class="fas fa-trash-alt"></i> </a>';
},
"targets": 6
},
// Then hide the subtitle/id columns (which I won't need to show in frontend)
{ "visible": false, "targets": [ 7 ] },
{ "visible": false, "targets": [ 8 ] }
],
pageLength: 25,
responsive: true,
"processing": true,
"serverSide": true,
"ajax": "index.php?action=ss-books-get"
} );
} );
Controller ss-books-get:
<?php
$table = 'library_book';
$primaryKey = 'id';
$columns = array(
array(
'db' => 'title',
'dt' => 0
),
array( 'db' => 'isbn', 'dt' => 1 ),
array(
'db' => 'subject_id',
'dt' => 2,
'formatter' => function( $d, $row ) {
return library::getSubjectByID($d);
}
),
array(
'db' => 'id',
'dt' => 3,
'formatter' => function( $d, $row ) {
return library::getLevelsByBookID($d);
}
),
array(
'db' => 'id',
'dt' => 4,
'formatter' => function( $d, $row ) {
return library::getCategoriesByBookID($d);
}
),
array(
'db' => 'id',
'dt' => 5,
'formatter' => function( $d, $row ) {
return library::getCountCopiesByBookID($d);
}
),
array(
'db' => 'type',
'dt' => 6
),
array(
'db' => 'id',
'dt' => 7
),
array(
'db' => 'subtitle',
'dt' => 8
),
);
require( 'ssp.class.php' );
echo json_encode(
SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, $whereResult=null, $whereAll="hidden = 0 AND type = 1" )
);
I created 4 functions to get the subject, levels, categories and copies by book ID.
Here are the functions:
public static function getSubjectByID($id) {
if ($id == NULL) { $id = 0; } else {}
$a = new SQLMan();
$a->tablename = "library_subject";
$result = $a->select("","",$where="id =".$id);
if (empty($result)) {
return '<span class="kt-badge kt-badge--success greysuccess kt-badge--inline kt-badge--pill"> N/A </span>';
} else {
$result = $result[0];
return '<span class="kt-badge kt-badge--success kt-badge--inline kt-badge--pill">'.$result->fields["name"].'</span>';
}
}
public static function getCategoriesByBookID($id) {
$final = "";
$a = new SQLMan();
$a->tablename = "library_category";
$categoria= $a->select("","",$where=" hidden = 0");
$a = "";
$a = new SQLMan();
$a->tablename = "library_categoryvsbook";
$anycat = $a->select("many","","book_id=".$id);
if (count($anycat)>0) {
foreach($anycat as $cl) {
foreach($categoria as $cat) {
if ($cat->fields["id"] == $cl->fields["category_id"]) {
$final .= "<span class='kt-badge kt-badge--success kt-badge--inline kt-badge--pill'>".$cat->fields["name"]."</span> ";
$a = "";
} else {
}
}
}
} else {
$final .= "<span class='kt-badge kt-badge--success greysuccess kt-badge--inline kt-badge--pill'>N/A</span> ";
$a = "";
}
return $final;
}
public static function getLevelsByBookID($id) {
$final = "";
$a = new SQLMan();
$a->tablename = "library_level";
$categoria= $a->select("","",$where=" hidden = 0");
$a = "";
$a = new SQLMan();
$a->tablename = "library_levelvsbook";
$anycat= $a->select("many","","book_id=".$id);
if (count($anycat)>0) {
foreach($anycat as $cl) {
foreach($categoria as $cat) {
if ($cat->fields["id"] == $cl->fields["level_id"]) {
$final .= "<span class='kt-badge kt-badge--success kt-badge--inline kt-badge--pill'>".$cat->fields["name"]."</span> ";
$a = "";
} else {
}
}
}
} else {
$final .= "<span class='kt-badge kt-badge--success greysuccess kt-badge--inline kt-badge--pill'>N/A</span> ";
$a = "";
}
return $final;
}
public static function getCountCopiesByBookID($id) {
$result = "";
$a = new SQLMan();
$a->tablename = "library_copy";
$ejemplares= $a->select("","",$where=" hidden = 0 AND book_id =".$id);
$count = "0";
$countb = "0";
foreach($ejemplares as $ejem) {
if ($ejem->fields["status"] == "0") {
$count++;
} else {
}
}
$countb = count($ejemplares);
if ($count>0) {
$popcount = "kt-badge--success";
} else {
$popcount = "kt-badge--success greysuccessbadge";
};
$result .= "<span class='kt-badge ".$popcount." kt-badge--dot kt-badge--xl'></span> ";
$result .= $count . " / " . $countb;
$count = "0"; $countb = "0";
return $result;
}
And now it looks exactly the same but the proccesing is faster.
Calling a function in DataTable Server Side Processing
ssp.class.php doesn't support a JOIN. But we have a workaround for this:
Solution 1 (Use sub-query):
Use sub-query in your $table definition and replace dist_code with disname in $columns as shown below:
$dbDetails = [
'host' => '****',
'user' => '****',
'pass' => '****',
'db' => '****'
];
$table = '(SELECT r.*, d.disname FROM requestss r INNER JOIN districts d ON r.dist_code = d.discode) tbl';
$primaryKey = 'id';
$columns = [
[ 'db' => 'time_stamp', 'dt' => 0 ],
[ 'db' => 'disname', 'dt' => 1 ],
[ 'db' => 'req_type', 'dt' => 2 ]
];
// Include SQL query processing class
require( 'ssp.class.php' );
// Output data as json format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )
);
Then, you need to replace all instances of `$table` with $table to remove backticks in ssp.class.php file.
Solution 2 (Create a view):
If you don't want to edit ssp.class.php file, you can create a view in your database:
CREATE
VIEW requests_view
AS SELECT r.*, d.disname FROM requestss r INNER JOIN districts d ON r.dist_code = d.discode;
Then, use requests_view as your $table in getData.php file:
$dbDetails = [
'host' => '****',
'user' => '****',
'pass' => '****',
'db' => '****'
];
$table = 'requests_view';
$primaryKey = 'id';
$columns = [
[ 'db' => 'time_stamp', 'dt' => 0 ],
[ 'db' => 'disname', 'dt' => 1 ],
[ 'db' => 'req_type', 'dt' => 2 ]
];
// Include SQL query processing class
require( 'ssp.class.php' );
// Output data as json format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )
);
You may also consider to use third party PHP libraries like Customized SSP Class For Datatables Library or Datatables library for PHP which support JOINs.
Related Topics
Laravel Model Callbacks After Save, Before Save, etc
Updating Product Stock Programmatically in Woocommerce 3
How to Set the Cookie Content with Curl
Call to Undefined Method Maatwebsite\Excel\Excel::Load()
PHP Method Argument Type Hinting with Question Mark (Type)
Call to Undefined Function Odbc_Connect() Message While Connecting Sap Hana Database
Url Rewriting-Code for Rewriting
Difference Between Break and Continue in PHP
Storing Datetime as Utc in PHP/Mysql
Laravel Validation: Exists with Additional Column Condition - Custom Validation Rule
Php: Do Arrays Have a Maximum Size
How to View/Open a Word Document in My Browser Using with PHP or HTML
How to Execute a Stored Procedure in PHP Using SQLsrv and "" Style Parameters
Order by Before Group by Using Eloquent (Laravel)
Array_Map Function in PHP with Parameter
Call to Undefined Method Domdocument::Getelementsbyclassname()