In the current release, no - in the release that will occur in a few hours time (1.2) yes :-). Join support is a new feature in Editor 1.2, which will include a demo of how it can be done and a tutorial on the subject.
Here we go: http://datatables.net/forums/discussion/11284/editor-1.2-now-available :-)
Tutorial and example are available:
http://editor.datatables.net/tutorials/php_join
http://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html
Am I reading the example correct?
For a simple query like: [code]
$result = mysql_query("SELECT * FROM members JOIN pracownik on (members.login=pracownik.other2)");[/code] it should be:
[code]<?php
/*
* Example PHP implementation used for the join.html example
*/
$editor = Editor::inst( $db, 'members' )
->field(
Field::inst( 'member_id' )->validator( 'Validate::required' ),
Field::inst( 'login' )->validator( 'Validate::required' ),
Field::inst( 'passwd' )->validator( 'Validate::required' ),
Field::inst( 'security' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'pracownik', 'object' )
->join( 'login', 'other2' )
Yes! That looks ideal to me for your database structure. You need to include the DataTables libraries of course, but the Editor initialisation looks spot on
{ "sError": "The Join class is not available in the trial version of Editor. A license for Editor, including full source, can be purchased from http://editor.datatables.net ."}
when opening join.php file.
Does it mean I will not be able to test it without purchasing first?
That is correct - the Join class is not included in the trial version. However, it does include everything needed to work with single tables, to get a feel for Editor, understand its implementation and what it is capable of.
From the Editor FAQs ( http://editor.datatables.net/purchase/ ):
[quote]
- What is not included in the trial package?
The 15 day free trial package of Editor does not include offline documentation, the Editor source code or the server-side Join classes. All of these items are available in the fully licensed package which can be downloaded upon purchase.
[/quote]
Hi Allan,
I tried to replicate your tutorial (http://editor.datatables.net/tutorials/php_join) with my tables and code as shown below, but I'm getting two type of errors:
A)If I leave the code exactly as below, I get the following error and nothing is loaded in the DataTables
[quote]
DataTables warning (table id = 'example'): Join was performed on the field 'production_mgr_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.
[/quote]
B)But if I add the following line to the PHP (see ">>>>"), everything is displayed correctly, except that when I try to update a record, I get the following error and the record isn't updated with selected value
[quote]
{"id":-1,"error":"","fieldErrors":[{"name":"production_mgr_id","status":"This field is required"}],"data":[]}
[/quote]
Can you please take a look and let me know what I missing? I looked several times at your code and at the Single(object) / Direct Reference (which I believe is my model, since I'm not using an intermediate table as you do in your example), but not able to find what I'm doing wrong.
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;
// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'production_mgr' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get production_mgr details
$out['rar_users'] = $db
->query( 'select', 'rar_users' )
->get( 'id as value, name as label' )
->where( 'title_id','PM','=' )
->exec()
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
[/code]
JS
[code]
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/table.project_estimates_join.php",
"domTable": "#example",
"fields": [
{
"label": "Release Q",
"name": "id_release"
},
{
"label": "Project name:",
"name": "project_name"
},
{
"label": "Customer:",
"name": "customer"
},
{
"label": "Field Manager:",
"name": "field_manager"
},
{
"label": "Production Mgr:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "rar_users.id",
"type": "select"
}
]
} );
$('#example').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "php/table.project_estimates_join.php",
"aoColumns": [
{ "mData": "id_release" },
{ "mData": "project_name" },
{ "mData": "customer" },
{ "mData": "field_manager" },
{
// Use the 'name' property from the 'rar_users' object in the
// JSON. It might not be set, so we also provide a default.
"mData": "rar_users.name",
"sDefaultContent": ""
}
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select and radio fields based on
// what is available in the database
editor.field('rar_users.id').update( json.rar_users );
}
} );
} );
[/code]
which tells the PHP classes that it should get that piece of data, but you don't want it to be in the insert or edit since you don't want those values to change - you just want the join aspect of it to refer to that value, which is what the `Join` class is doing.
Thanks Allan,
In fact, I really want the production_mgr_id attribute in the parent table to be updated based on User selection.
I'm performing the join to be able to get the production_mgr name from child table, display this name to user and save its new id.
BTW, I tried as you suggested, but I'm receiving a new error, it seems like it's trying to insert the production_mgr_id in the child table, while I'm trying the opposite.
[quote]
An error has occurred - Please contact the system administrator
br />
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '23502' for key 'PRIMARY'' in C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Driver\Mysql\Query.php:98
Stack trace:
...
#4 C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Database.php(141): DataTables\Database->insert('rar_users', Array)
#5 C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Editor\Join.php(620): DataTables\Database->push('rar_users', Array, Array)
#6 C in C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Driver\Mysql\Query.php on line 98
[/quote]
Oh I see! So you what the id link to be updated, but the 'joined' data to not be changed. The `Join` class has a `set` method ass well, so you can set that to false (i.e. ` ->set( false );` ) which means Editor won't try to alter the values of the joined table.
Then you simply need to update the production_mgr_id field as you would with any other. There is an example of that there: http://editor.datatables.net/release/DataTables/extras/Editor/examples/joinSelf.html .
So ultimately you need to add a field in your Editor fields for the production manager. Currently you have `rar_users.id` , but it seems to me that you should be using `production_mgr_id` .
Replies
Allan
Tutorial and example are available:
http://editor.datatables.net/tutorials/php_join
http://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html
Allan
Am I reading the example correct?
For a simple query like: [code]
$result = mysql_query("SELECT * FROM members JOIN pracownik on (members.login=pracownik.other2)");[/code] it should be:
[code]<?php
/*
* Example PHP implementation used for the join.html example
*/
$editor = Editor::inst( $db, 'members' )
->field(
Field::inst( 'member_id' )->validator( 'Validate::required' ),
Field::inst( 'login' )->validator( 'Validate::required' ),
Field::inst( 'passwd' )->validator( 'Validate::required' ),
Field::inst( 'security' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'pracownik', 'object' )
->join( 'login', 'other2' )
->field(
Field::inst( 'imie' )->validator( 'Validate::required' ),
Field::inst( 'nazwisko' )
)
);
// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();
// Send it back to the client
echo json_encode( $out );[/code] and then
[code] "aoColumns": [
{ "mData": "member_id" },
{ "mData": "login" },
{ "mData": "passwd" },
{ "mData": "security" },
{"mData": "pracownik.imie"},
{"mData": "pracownik.nazwisko"}[/code]
?
Yes! That looks ideal to me for your database structure. You need to include the DataTables libraries of course, but the Editor initialisation looks spot on
Allan
{ "sError": "The Join class is not available in the trial version of Editor. A license for Editor, including full source, can be purchased from http://editor.datatables.net ."}
when opening join.php file.
Does it mean I will not be able to test it without purchasing first?
From the Editor FAQs ( http://editor.datatables.net/purchase/ ):
[quote]
- What is not included in the trial package?
The 15 day free trial package of Editor does not include offline documentation, the Editor source code or the server-side Join classes. All of these items are available in the fully licensed package which can be downloaded upon purchase.
[/quote]
Allan
I tried to replicate your tutorial (http://editor.datatables.net/tutorials/php_join) with my tables and code as shown below, but I'm getting two type of errors:
A)If I leave the code exactly as below, I get the following error and nothing is loaded in the DataTables
[quote]
DataTables warning (table id = 'example'): Join was performed on the field 'production_mgr_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.
[/quote]
B)But if I add the following line to the PHP (see ">>>>"), everything is displayed correctly, except that when I try to update a record, I get the following error and the record isn't updated with selected value
[quote]
{"id":-1,"error":"","fieldErrors":[{"name":"production_mgr_id","status":"This field is required"}],"data":[]}
[/quote]
[code]
$editor = Editor::inst( $db, 'project_estimates' )
->field(
...
Field::inst( 'field_manager' )->validator( 'Validate::required' ),
>>>> Field::inst( 'production_mgr_id' )->validator( 'Validate::required' )
)
->join(
...
);
[/code]
Can you please take a look and let me know what I missing? I looked several times at your code and at the Single(object) / Direct Reference (which I believe is my model, since I'm not using an intermediate table as you do in your example), but not able to find what I'm doing wrong.
PHP
[code]
<?php
// DataTables PHP library
include( "../../Editor-1.2.3/examples/php/lib/DataTables.php" );
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;
/*
* Example PHP implementation used for the project_estimates_join.html example
*/
$editor = Editor::inst( $db, 'project_estimates' )
->field(
Field::inst( 'id_release' )->validator( 'Validate::required' ),
Field::inst( 'project_name' )->validator( 'Validate::required' ),
Field::inst( 'customer' )->validator( 'Validate::required' ),
Field::inst( 'field_manager' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'rar_users', 'object' )
->join( 'production_mgr_id', 'id' )
->fields(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
)
);
// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'production_mgr' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get production_mgr details
$out['rar_users'] = $db
->query( 'select', 'rar_users' )
->get( 'id as value, name as label' )
->where( 'title_id','PM','=' )
->exec()
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
[/code]
JS
[code]
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/table.project_estimates_join.php",
"domTable": "#example",
"fields": [
{
"label": "Release Q",
"name": "id_release"
},
{
"label": "Project name:",
"name": "project_name"
},
{
"label": "Customer:",
"name": "customer"
},
{
"label": "Field Manager:",
"name": "field_manager"
},
{
"label": "Production Mgr:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "rar_users.id",
"type": "select"
}
]
} );
$('#example').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "php/table.project_estimates_join.php",
"aoColumns": [
{ "mData": "id_release" },
{ "mData": "project_name" },
{ "mData": "customer" },
{ "mData": "field_manager" },
{
// Use the 'name' property from the 'rar_users' object in the
// JSON. It might not be set, so we also provide a default.
"mData": "rar_users.name",
"sDefaultContent": ""
}
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select and radio fields based on
// what is available in the database
editor.field('rar_users.id').update( json.rar_users );
}
} );
} );
[/code]
Try changing this:
[code]
Field::inst( 'production_mgr_id' )->validator( 'Validate::required' )
[/code]
to:
[code]
Field::inst( 'production_mgr_id' )->set ( false )
[/code]
which tells the PHP classes that it should get that piece of data, but you don't want it to be in the insert or edit since you don't want those values to change - you just want the join aspect of it to refer to that value, which is what the `Join` class is doing.
Regards,
Allan
In fact, I really want the production_mgr_id attribute in the parent table to be updated based on User selection.
I'm performing the join to be able to get the production_mgr name from child table, display this name to user and save its new id.
BTW, I tried as you suggested, but I'm receiving a new error, it seems like it's trying to insert the production_mgr_id in the child table, while I'm trying the opposite.
[quote]
An error has occurred - Please contact the system administrator
br />
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '23502' for key 'PRIMARY'' in C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Driver\Mysql\Query.php:98
Stack trace:
...
#4 C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Database.php(141): DataTables\Database->insert('rar_users', Array)
#5 C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Editor\Join.php(620): DataTables\Database->push('rar_users', Array, Array)
#6 C in C:\SkyDrive\webapps\htdocs\Editor-1.2.3\examples\php\lib\Database\Driver\Mysql\Query.php on line 98
[/quote]
Then you simply need to update the production_mgr_id field as you would with any other. There is an example of that there: http://editor.datatables.net/release/DataTables/extras/Editor/examples/joinSelf.html .
So ultimately you need to add a field in your Editor fields for the production manager. Currently you have `rar_users.id` , but it seems to me that you should be using `production_mgr_id` .
Allan
With your comments and this example I was able to achieve what I was looking for,