How to join correctly?

Olan

I have a database with two tables:

StaticInformation with fields: id, name, typeID
StaticInformationType with fields: id, name

I want to display a table with the information coming from the StaticInformationTable but on the typeID column I want to display the name of the type instead of the typeID In other words, the name that correspondents to the id in the StaticInformationType table. typeID is a reference to id in the StaticInformationType table.

I have now the following code:

table.StaticInformation.js:

[code]
var editor;

$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "table.StaticInformation.php",
"domTable": "#StaticInformation",
"fields": [
{
"label": "Type:",
"name": "StaticInformationType.id",
"type": "select"
},
{
"label": "Name:",
"name": "name"
}
]
} );
$('#StaticInformation').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "table.StaticInformation.php",
"aoColumns": [
{
"mData": "StaticInformationType.name",
"sDefaultContent": ""
},
{ "mData": "name" }
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function ( settings, json ) {
editor.field('StaticInformationType.id').update( json.StaticInformationType );
}
} );
} );
[/code]

table.StaticInformation.php:

[code]
<?php

include( "extras/Editor-1.2.4/examples/php/lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'StaticInformation' )
->field(
Field::inst( 'typeID' ),
Field::inst( 'name' )
)
->join(
Join::inst( 'StaticInformationType', 'object' )
->join( 'typeID', 'id' )
->field(
Field::inst( 'name' )
)
);

$out = $editor
->process($_POST)
->data();

if ( !isset($_POST['action']) ) {
$out['StaticInformationType'] = $db
->select( 'StaticInformationType', 'id as value, name as label' )
->fetchAll();
}

echo json_encode( $out );

?>
[/code]

This basically works but when I change or add something, it alters the StaticInformationType table as well. This should be fixed and only the typeID must be changed in the StaticInformation table but this goes wrong also.

Hopefully someone can help me with is. I already spend several hours get what I have now but I cannot solve my problem....

Thanks!
Olan

allan

Hi Olan,

What you can do is use the `set()` method of the Join class to tell Editor to not change any information on the joined table - https://editor.datatables.net/docs/current/php/class-DataTables.Editor.Join.html#_set .

So in this case:

[code]
$editor = Editor::inst( $db, 'StaticInformation' )
->field(
Field::inst( 'typeID' ),
Field::inst( 'name' )
)
->join(
Join::inst( 'StaticInformationType', 'object' )
->join( 'typeID', 'id' )
->set( false )
->field(
Field::inst( 'name' )
)
);
[/code]

The other thing is:

> "name": "StaticInformationType.id"

I think you probably want `"name": "typeID"` here, so it is updating the base table.

Regards,
Allan

Olan

Hello Allan,

Thank you! Almost there I think but: when I set "name": "typeID" my select list is empty. When I use "name": "StaticInformationType.id" I have a list but when I make a new entry it doesn't set the id from StaticInformationType into my table.

regards,
Olan

allan

> when I set "name": "typeID" my select list is empty

Did you modify the `update()` call to reflect the change in name as well?

Allan

Olan

Yes, that was it! Thanks a lot!
Olan

Olan

I want to display a extra column in the staticInformation table with a extra value (info) coming from the StaticInformationType table. Now the name of the type is displayed in the table but I also want to display the info. Is that possible?

allan

Sure, add: `Field::inst( 'info' )` to your field array for the joined table in PHP to get the extra column, and then in Javascript you can use `mData: 'StaticInformationType.info'` to access the data and display it in the DataTable :-)

Allan

Olan

I got it working! [quote]allan said: to your field array for the joined table[/quote] was the trigger to make it work.
Thanks!

sasmit

Thanks Allan and Olan. I had a similar problem and was able to resolve it after reading this.

Sasmit

austinlyon

I was also a little confused about 'displaying' a joined field in the table and 'editing' the field through the editor with the join example given on Editor's website. This post cleared up my problem quite nicely.

An example like the one in this post would be super useful in the Editor examples section, since I think using joins for viewing and editing are two very different, but very useful things that Editor has to offer. Thanks for all your hard work Allan!

allan

Agreed - the joins in Editor 1.2 are rather confusing! The new join syntax in 1.3 is vasty (imho) improved. Its a lot more "SQL like" in that it looks rather like a typical LEFT JOIN syntax. New documentation coming with 1.3 as well :-)

Allan