Simple JOIN example

mous

Hi all,
I spent many hours trying to solve this issue but I don't know what can I do more. Any help would be very appreciated !
Here is the error message:
[code]
DataTables warning (table id = 'user'): Join was performed on the field 'groupsa_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.
[/code]
Yes the issue come from the field list. But ....
I have two tables:
- groupsa (id, groupname)
- user (id, username, groupsa_id) groupsa_id as FK

Here is my code
[code]
var editor;
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "extras/Editor/php/user.php",
"domTable": "#user",
"fields": [ {
"label": "username:",
"name": "username"
}, {
"label": "groupname:",
"name": "groupsa.id",
"type": "select"
}
]
} );
$('#user').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "extras/Editor/php/user.php",
"aoColumns": [
{ "mData": "username" },
{
"mData": "groupsa.groupname",
"sDefaultContent": ""
}
],
"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('groupsa.id').update( json.groupsa );
}
} );
} );
[/code]

and the php code
[code]
<?php
include( "lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'user' )
->field(
Field::inst( 'username' )
)
->join(
Join::inst( 'groupsa', 'object' )
->join( 'groupsa_id', 'id' )
->field(
Field::inst( 'groupname' )
)
);

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

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

echo json_encode( $out );
[/code]

Thanks in advance
Mous

allan

Hi Mous,

Just add `Field::inst( 'groupsa_id' )` to the field array for the main instance. That should do it :-)

Allan

mous

Hi Allan,
Thanks for your message. This resolve a big issue but unfortunately I still have others and it does not work like I want ;(
- Doing so, I have the groupsa_id displayed but I want the groupname to be displayed instead
- Also, I got errors when creating or editing rows. Please find below the error message.

[code]
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`archivweb_dev`.`user`, CONSTRAINT `fk_user_groupsa` FOREIGN KEY (`groupsa_id`) REFERENCES `groupsa` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)' in /home/mous/extras/Editor/php/lib/Database/Driver/Mysql/Query.php:98\nStack trace:\n#0 /home/mous/extras/Editor/php/lib/Database/Driver/Mysql/Query.php(98): PDOStatement->execute()\n#1 /home/mous/extras/Editor/php/lib/Database/Query.php(551): DataTables\\Database\\DriverMysqlQuery->_exec()\n#2 /home/mous/extras/Editor/php/lib/Database/Query.php(197): DataTables\\Database\\Query->_insert()\n#3 /home/mous/extras/Editor/php/lib/Database/Database.php(122): DataTables\\Database\\Query->exec()\n#4 /home/mous/ in /home/mous/extras/Editor/php/lib/Database/Driver/Mysql/Query.php on line 98, referer: http://localhost/user.html
[/code]

As you know I am dummy at JS ;) I don't know if you remember me ! from Nissa U4EA ;)

Again, thanks a lot for your support !

Mous

allan

Haha! Hi Mous - great to talk to you again! Gosh it has been a while hasn't it...

I hadn't realised it was yourself :-). Hope things are going well for you!

> - Doing so, I have the groupsa_id displayed but I want the groupname to be displayed instead

That's interesting - it looks like it should work to me. Is this what you've got for your PHP now:

[code]
<?php
include( "lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'user' )
->field(
Field::inst( 'username' ),
Field::inst( 'groupsa_id' )
)
->join(
Join::inst( 'groupsa', 'object' )
->join( 'groupsa_id', 'id' )
->field(
Field::inst( 'groupname' )
)
);

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

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

echo json_encode( $out );
[/code]

I don't immediately see what the problem would be with that I'm afraid. Are you able to link me to the page that you are working on? Skype or PM me the address if you don't want to make it public.

Regards,
Allan

mous

Hi,
Yes, it's true it was a very long time ! Hope that everything go well for you.; I will pm to you for more details
....
So, for the PHP script is the same as you put. but still have errors, I will configure an external server with the script ans will send you the link once configured

Thanks again

Mous