Example join.html doesn't work with large database
Example join.html doesn't work with large database
slemoine
Posts: 6Questions: 0Answers: 0
I purchased Editor this week-end and was happy with it until I try to manage large database with joined tables...
In order to illustrate the troubles I discovered, I populate tables users, users_dept and users_access with more than 20000 records for the join example.
The only change I made in the code (join.html) is to enable the large data management with :
"bServerSide" : true,
"sServerMethod" : 'POST',
With this amount of records and the bServerSide modification, pagination, filters and editor doesn't work any more.
Could you help me, please.
Stéphane
In order to illustrate the troubles I discovered, I populate tables users, users_dept and users_access with more than 20000 records for the join example.
The only change I made in the code (join.html) is to enable the large data management with :
"bServerSide" : true,
"sServerMethod" : 'POST',
With this amount of records and the bServerSide modification, pagination, filters and editor doesn't work any more.
Could you help me, please.
Stéphane
This discussion has been closed.
Replies
I'm afraid that it looks like you have run into a bug in the current release of Editor - there is an issue with server-side processing and joined tables not integrating correctly together.
I will take a look at this and get back to you as soon as I have a solution.
Regards,
Allan
Sorry - I was wrong. This actually should work. I hadn't set the sServerMethod option in my test before, which of course is required, since that is where the Editor server-side script was expecting to find the data. Setting it allows it to work.
If you are still having problems with this, can you link me to a page showing the problem please?
Thanks,
Allan
Still not working.
I tried to implement this solution on my own development without succes on large database joined tables.
So I tried with the join example provided with the editor package. data are loaded but filter, pagination and editor window are not working.
Here is the code.
As you will see, it's exactly your example just tuned to enable ser side processing.
Best regards.
Stéphane
HTML
[code]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
DataTables Editor example
@import "../../../media/css/jquery.dataTables.css";
@import "../../TableTools/media/css/TableTools.css";
@import "../media/css/dataTables.editor.css";
var editor; // use a global for the submit and return data rendering in the examples
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/join.php",
"domTable": "#example",
"fields": [ {
"label": "First name:",
"name": "first_name"
}, {
"label": "Last name:",
"name": "last_name"
}, {
"label": "Department:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "dept.id",
"type": "select"
}, {
"label": "Access:",
// 'name' here will generate an array of the 'id' properties from
// the access parameter for the row's data and use that to enable
// the correct checkboxes.
"name": "access[].id",
"type": "checkbox"
}, {
// The 'extra' options aren't shown in the main table, but are
// given in the form as that can be useful (and show again JOINs).
"label": "Comments:",
"name": "extra.comments",
"default": ""
}, {
"label": "Review:",
"name": "extra.review",
"default": ""
}
]
} );
$('#example').dataTable( {
"sDom": "Tfrtip",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "php/join.php",
"sServerMethod" : 'POST',
"aoColumns": [
{ "mData": "first_name" },
{ "mData": "last_name" },
{
// Use the 'name' property from the 'dept' object in the
// JSON. It might not be set, so we also provide a default.
"mData": "dept.name",
"sDefaultContent": ""
},
{
// The 'access' property in the JSON is an array of objects.
// To display in the table we use the [] notation to access
// the array property 'name' and display as a comma separated
// list
"mData": "access",
"mRender": "[, ].name"
}
],
"oTableTools": {
"sRowSelect": "multi",
"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('dept.id').update( json.dept );
editor.field('access[].id').update( json.access );
}
} );
} );
DataTables Editor - joined tables example
First name
Last name
Department
Access
First name
Last name
Department
Access
[/code]
PHP
[code]
<?php
// DataTables PHP library
include( "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 join.html example
*/
$editor = Editor::inst( $db, 'users' )
->field(
Field::inst( 'first_name' )->validator( 'Validate::required' ),
Field::inst( 'last_name' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'dept', 'object' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'dept_id' ),
'user_dept'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
),
Join::inst( 'access', 'array' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'access_id' ),
'user_access'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
),
Join::inst( 'extra', 'object' )
->join( 'id', 'user_id' )
->field(
Field::inst( 'comments' ),
Field::inst( 'review' )
)
);
// 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 'department' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get department details
$out['dept'] = $db
->select( 'dept', 'id as value, name as label' )
->fetchAll();
$out['access'] = $db
->select( 'access', 'id as value, name as label' )
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
[/code]
You can work around that error at the moment using:
[code]
$('#example').one( 'xhr', function (e, o, json) {
editor.field('dept.id').update( json.dept );
editor.field('access[].id').update( json.access );
} );
[/code]
Allan
Thanks a lot for your prompt answers. It's now working quite well.
The only features not available are the sorting and the search on the columns coming from tables joined. but it's not so important for my application.
So the issue is close for me.
Just one last question. I made those tests because according to the quality of the datatable plug in, I expect to use it in a big application i'am going to transfer on Symfony2.
I wonder if I can rely on a support in the next few years.
Once again, thanks for your good job and prompt answer.
Stephane
> I wonder if I can rely on a support in the next few years.
While I can't guarantee it, in the same way that any business can't 100% guarantee that they will still be around in several years time, I do have a long term strategy / business plan, in which DataTables and Editor form the linch pin. So I certainly plan for them to be fully supported long term.
Allan
$('#example').one( 'xhr', function (e, o, json) {
editor.field('dept.id').update( json.dept );
editor.field('access[].id').update( json.access );
} );
Allan
alteration will be necessary any more?
I'm using 1.2.3-Editor
Allan
DataTables Editor example
@import "../media/css/jquery.dataTables.css";
@import "../media/css/TableTools.css";
@import "../media/css/dataTables.editor.css";
var editor; // use a global for the submit and return data rendering in the examples
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/join.php",
"domTable": "#example",
"fields": [ {
"label": "First name:",
"name": "first_name"
}, {
"label": "Last name:",
"name": "last_name"
}, {
"label": "Department:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "dept.id",
"type": "select"
}, {
"label": "Access:",
// 'name' here will generate an array of the 'id' properties from
// the access parameter for the row's data and use that to enable
// the correct checkboxes.
"name": "access[].id",
"type": "checkbox"
}, {
// The 'extra' options aren't shown in the main table, but are
// given in the form as that can be useful (and show again JOINs).
"label": "Comments:",
"name": "extra.comments",
"default": ""
}, {
"label": "Review:",
"name": "extra.review",
"default": ""
}
]
} );
$('#example').one( 'xhr', function (e, o, json) {
editor.field('dept.id').update( json.dept );
editor.field('access[].id').update( json.access );
} );
$('#example').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "php/join.php",
"aoColumns": [
{ "mData": "first_name" },
{ "mData": "last_name" },
{
// Use the 'name' property from the 'dept' object in the
// JSON. It might not be set, so we also provide a default.
"mData": "dept.name",
"sDefaultContent": ""
},
{
// The 'access' property in the JSON is an array of objects.
// To display in the table we use the [] notation to access
// the array property 'name' and display as a comma separated
// list
"mData": "access",
"mRender": "[, ].name"
}
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );
<!-- Includes required for the example page's behaviour - not needed for Editor itself -->
@import "support/examples.css";
@import "../media/css/demo_page.css";
@import "../media/css/shCore.css";
First name
Last name
Department
Access
First name
Last name
Department
Access
<?php
// DataTables PHP library
include( "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 join.html example
*/
$editor = Editor::inst( $db, 'users' )
->field(
Field::inst( 'first_name' )->validator( 'Validate::required' ),
Field::inst( 'last_name' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'dept', 'object' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'dept_id' ),
'user_dept'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
),
Join::inst( 'access', 'array' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'access_id' ),
'user_access'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
),
Join::inst( 'extra', 'object' )
->join( 'id', 'user_id' )
->field(
Field::inst( 'comments' ),
Field::inst( 'review' )
)
);
// 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 'department' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get department details
$out['dept'] = $db
->select( 'dept', 'id as value, name as label' )
->fetchAll();
$out['access'] = $db
->select( 'access', 'id as value, name as label' )
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
{"id":-1,"error":"","fieldErrors":[],"data":[],"aaData":[{"DT_RowId":"row_1","first_name":"Quynn","last_name":"Contreras","dept":{"id":"1","name":"IT"},"access":[{"id":"1","name":"Printer"},{"id":"3","name":"Desktop"},{"id":"4","name":"VMs"}],"extra":{}},{"DT_RowId":"row_2","first_name":"Kaitlin","last_name":"Smith","dept":{"id":"4","name":"Marketing"},"access":[{"id":"1","name":"Printer"},{"id":"4","name":"VMs"}],"extra":{}},{"DT_RowId":"row_3","first_name":"Cruz","last_name":"Reynolds","dept":{"id":"7","name":"Support"},"access":[],"extra":{}},{"DT_RowId":"row_4","first_name":"Sophia","last_name":"Morris","dept":{"id":"3","name":"Pre-Sales"},"access":[{"id":"3","name":"Desktop"},{"id":"4","name":"VMs"},{"id":"5","name":"Web-site"},{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_5","first_name":"Kamal","last_name":"Roberson","dept":{"id":"2","name":"Sales"},"access":[{"id":"2","name":"Servers"}],"extra":{}},{"DT_RowId":"row_6","first_name":"Dustin","last_name":"Rosa","dept":{"id":"6","name":"Accounts"},"access":[{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_7","first_name":"Xantha","last_name":"George","dept":{"id":"2","name":"Sales"},"access":[{"id":"2","name":"Servers"}],"extra":{}},{"DT_RowId":"row_8","first_name":"Bryar","last_name":"Long","dept":{"id":"1","name":"IT"},"access":[{"id":"1","name":"Printer"}],"extra":{}},{"DT_RowId":"row_9","first_name":"Kuame","last_name":"Wynn","dept":{"id":"2","name":"Sales"},"access":[{"id":"2","name":"Servers"}],"extra":{}},{"DT_RowId":"row_10","first_name":"Indigo","last_name":"Brennan","dept":{"id":"3","name":"Pre-Sales"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"}],"extra":{}},{"DT_RowId":"row_11","first_name":"Avram","last_name":"Allison","dept":{"id":"4","name":"Marketing"},"access":[{"id":"4","name":"VMs"},{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_12","first_name":"Martha","last_name":"Burgess","dept":{"id":"5","name":"Senior Management"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"5","name":"Web-site"}],"extra":{}},{"DT_RowId":"row_13","first_name":"Lael","last_name":"Kim","dept":{"id":"6","name":"Accounts"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"},{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_14","first_name":"Lyle","last_name":"Lewis","dept":{"id":"4","name":"Marketing"},"access":[],"extra":{}},{"DT_RowId":"row_15","first_name":"Veronica","last_name":"Marks","dept":{"id":"3","name":"Pre-Sales"},"access":[],"extra":{}},{"DT_RowId":"row_16","first_name":"Wynne","last_name":"Ruiz","dept":{"id":"6","name":"Accounts"},"access":[],"extra":{}},{"DT_RowId":"row_17","first_name":"Jessica","last_name":"Bryan","dept":{"id":"3","name":"Pre-Sales"},"access":[],"extra":{}},{"DT_RowId":"row_18","first_name":"Quinlan","last_name":"Hyde","dept":{"id":"7","name":"Support"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"}],"extra":{}},{"DT_RowId":"row_19","first_name":"Mona","last_name":"Terry","dept":{"id":"7","name":"Support"},"access":[],"extra":{}},{"DT_RowId":"row_20","first_name":"Medge","last_name":"Patterson","dept":{"id":"1","name":"IT"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"}],"extra":{}},{"DT_RowId":"row_21","first_name":"Perry","last_name":"Gamble","dept":{"id":"2","name":"Sales"},"access":[{"id":"2","name":"Servers"},{"id":"4","name":"VMs"}],"extra":{}},{"DT_RowId":"row_22","first_name":"Pandora","last_name":"Armstrong","dept":{"id":"6","name":"Accounts"},"access":[{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"},{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_23","first_name":"Pandora","last_name":"Briggs","dept":{"id":"3","name":"Pre-Sales"},"access":[],"extra":{}},{"DT_RowId":"row_24","first_name":"Maris","last_name":"Leblanc","dept":{"id":"4","name":"Marketing"},"access":[],"extra":{}},{"DT_RowId":"row_25","first_name":"Ishmael","last_name":"Crosby","dept":{"id":"5","name":"Senior Management"},"access":[],"extra":{}},{"DT_RowId":"row_26","first_name":"Quintessa","last_name":"Pickett","dept":{"id":"6","name":"Accounts"},"access":[],"extra":{}},{"DT_RowId":"row_27","first_name":"Ifeoma","last_name":"Mays","dept":{"id":"7","name":"Support"},"access":[],"extra":{}},{"DT_RowId":"row_28","first_name":"Basia","last_name":"Harrell","dept":{"id":"2","name":"Sales"},"access":[],"extra":{}},{"DT_RowId":"row_29","first_name":"Hamilton","last_name":"Blackburn","dept":{"id":"3","name":"Pre-Sales"},"access":[],"extra":{}},{"DT_RowId":"row_30","first_name":"Dexter","last_name":"Burton","dept":{"id":"1","name":"IT"},"access":[{"id":"1","name":"Printer"},{"id":"3","name":"Desktop"},{"id":"5","name":"Web-site"}],"extra":{}},{"DT_RowId":"row_31","first_name":"Quinn","last_name":"Mccall","dept":{"id":"3","name":"Pre-Sales"},"access":[{"id":"3","name":"Desktop"}],"extra":{}},{"DT_RowId":"row_32","first_name":"Alexa","last_name":"Wilder","dept":{"id":"4","name":"Marketing"},"access":[{"id":"4","name":"VMs"}],"extra":{}},{"DT_RowId":"row_33","first_name":"Rhonda","last_name":"Harrell","dept":{"id":"6","name":"Accounts"},"access":[{"id":"6","name":"Accounts"}],"extra":{}},{"DT_RowId":"row_34","first_name":"Jocelyn","last_name":"England","dept":{"id":"7","name":"Support"},"access":[{"id":"1","name":"Printer"},{"id":"2","name":"Servers"},{"id":"3","name":"Desktop"}],"extra":{}},{"DT_RowId":"row_35","first_name":"Vincent","last_name":"Banks","dept":{"id":"2","name":"Sales"},"access":[{"id":"2","name":"Servers"}],"extra":{}},{"DT_RowId":"row_36","first_name":"Stewart","last_name":"Chan","dept":{"id":"3","name":"Pre-Sales"},"access":[{"id":"3","name":"Desktop"}],"extra":{}}],"dept":[{"value":"1","0":"1","label":"IT","1":"IT"},{"value":"2","0":"2","label":"Sales","1":"Sales"},{"value":"3","0":"3","label":"Pre-Sales","1":"Pre-Sales"},{"value":"4","0":"4","label":"Marketing","1":"Marketing"},{"value":"5","0":"5","label":"Senior Management","1":"Senior Management"},{"value":"6","0":"6","label":"Accounts","1":"Accounts"},{"value":"7","0":"7","label":"Support","1":"Support"}],"access":[{"value":"1","0":"1","label":"Printer","1":"Printer"},{"value":"2","0":"2","label":"Servers","1":"Servers"},{"value":"3","0":"3","label":"Desktop","1":"Desktop"},{"value":"4","0":"4","label":"VMs","1":"VMs"},{"value":"5","0":"5","label":"Web-site","1":"Web-site"},{"value":"6","0":"6","label":"Accounts","1":"Accounts"}]}
<?php
namespace DataTables\Editor;
if (!defined('DATATABLES')) exit();
use
DataTables,
DataTables\Editor,
DataTables\Editor\Join,
DataTables\Editor\Field;
class Join extends DataTables\Ext {
function __construct( $table=null, $type='object' )
{
$this->table( $table );
$this->type( $type );
}
private $_fields = array();
/** @var array */
private $_join = array(
"parent" => null,
"child" => null,
"table" => null
);
/** @var string */
private $_table = null;
/** @var string */
private $_type = null;
/** @var string */
private $_name = null;
/** @var boolean */
private $_get = true;
/** @var boolean */
private $_set = true;
/** @var string */
private $_aliasParentTable = null;
public function aliasParentTable ( $_=null )
{
return $this->_getSet( $this->_aliasParentTable, $_ );
}
public function field ( $_=null )
{
if ( $_ !== null && !is_array($_) ) {
$_ = func_get_args();
}
return $this->_getSet( $this->_fields, $_, true );
}
public function fields ( $_=null )
{
if ( $_ !== null && !is_array($_) ) {
$_ = func_get_args();
}
return $this->_getSet( $this->_fields, $_, true );
}
public function get ( $_=null )
{
return $this->_getSet( $this->_get, $_ );
}
public function join ( $parent=null, $child=null, $table=null )
{
if ( $parent === null && $child === null ) {
return $this->_join();
}
$this->_join['parent'] = $parent;
$this->_join['child'] = $child;
$this->_join['table'] = $table;
return $this;
}
public function name ( $_=null )
{
return $this->_getSet( $this->_name, $_ );
}
public function set ( $_=null )
{
return $this->_getSet( $this->_set, $_ );
}
public function table ( $_=null )
{
if ( $_ !== null ) {
$this->_name = $_;
}
return $this->_getSet( $this->_table, $_ );
}
public function type ( $_=null )
{
return $this->_getSet( $this->_type, $_ );
}
public function data( $dte, &$data )
{
if ( ! $this->_get ) {
return;
}
$dteTable = $dte->table();
$dteTable = $dteTable[0];
$dteTableAlias = $this->_aliasParentTable === null ? $dteTable : $this->_aliasParentTable;
$joinField = isset($this->_join['table']) ? $this->_join['parent'][0] : $this->_join['parent'];
$pkeyIsJoin = $dte->pkey() === $joinField;
$pkeyTable = $this->_table===$dteTable ? $dteTable : $dteTableAlias;
for ( $i=0 ; $i_fields) ; $i++ ) {
$field = $this->_fields[$i];
if ( strpos( $field->dbField() , "." ) !== false ) {
if ( $field->set() && $this->_set ) {
echo json_encode( array(
"sError" => "Table selected fields (i.e. '{table}.{column}') in `Join` ".
"must be read only. Use `set(false)` for the field to disable writing."
) );
exit(0);
}
if ( strpos( $field->name() , "." ) !== false ) {
echo json_encode( array(
"sError" => "Table selected fields (i.e. '{table}.{column}') in `Join` ".
"must have a name alias which does not contain a period ('.'). Use ".
"name('---') to set a name for the field"
) );
exit(0);
}
}
}
// Set up the JOIN query
$stmt = $dte->db()
->query( 'select' )
->get( $pkeyTable.'.'.$joinField.' as _dte_pkey' )
->get( $this->_fields('get') )
->table( $dteTable .' as '. $dteTableAlias );
if ( isset($this->_join['table']) ) {
// Working with a link table
$stmt
->join(
$this->_join['table'],
$dteTableAlias.'.'.$this->_join['parent'][0] .' = '. $this->_join['table'].'.'.$this->_join['parent'][1]
)
->join(
$this->_table,
$this->_table.'.'.$this->_join['child'][0] .' = '. $this->_join['table'].'.'.$this->_join['child'][1]
);
}
else {
// No link table in the middle
$stmt
->join(
$this->_table,
$this->_table.'.'.$this->_join['child'] .' = '. $dteTableAlias.'.'.$this->_join['parent']
);
}
$res = $stmt->exec();
if ( ! $res ) {
return;
}
// Map to primary key for fast lookup
$join = array();
while ( $row=$res->fetch() ) {
$inner = array();
for ( $j=0 ; $j_fields) ; $j++ ) {
$field = $this->_fields[$j];
if ( $field->apply('get') ) {
$inner[ $field->name() ] = $field->val('get', $row);
}
}
if ( $this->_type === 'object' ) {
$join[ $row['_dte_pkey'] ] = $inner;
}
else {
if ( !isset( $join[ $row['_dte_pkey'] ] ) ) {
$join[ $row['_dte_pkey'] ] = array();
}
$join[ $row['_dte_pkey'] ][] = $inner;
}
}
if ( !$pkeyIsJoin && count($data) > 0 && !isset($data[0][ $joinField ]) ) {
echo json_encode( array(
"sError" => "Join was performed on the field '{$joinField}' which was not "
."included in the Editor field list. The join field must be included "
."as a regular field in the Editor instance."
) );
exit(0);
}
// Loop over the data and do a join based on the data available
for ( $i=0 ; $iidPrefix(), '', $data[$i]['DT_RowId'] ) :
$data[$i][ $joinField ];
if ( isset( $join[$rowPKey] ) ) {
$data[$i][ $this->_name ] = $join[$rowPKey];
}
else {
$data[$i][ $this->_name ] = ($this->_type === 'object') ?
(object)array() : array();
}
}
}
public function create ( $dte, $parentId, $data )
{
if ( ! $this->_set ) {
return;
}
if ( $this->_type === 'object' ) {
$this->_insert( $dte, $parentId, $data[$this->_name] );
}
else {
for ( $i=0 ; $i_name]) ; $i++ ) {
$this->_insert( $dte, $parentId, $data[$this->_name][$i] );
}
}
}
public function update ( $dte, $parentId, $data )
{
if ( ! $this->_set ) {
return;
}
if ( $this->_type === 'object' ) {
// update or insert
$this->_update_row( $dte, $parentId, $data[$this->_name] );
}
else {
$this->remove( $dte, array($parentId) );
$this->create( $dte, $parentId, $data );
}
}
public function remove ( $dte, $ids )
{
if ( ! $this->_set ) {
return;
}
if ( isset($this->_join['table']) ) {
$stmt = $dte->db()
->query( 'delete' )
->table( $this->_join['table'] )
->or_where( $this->_join['parent'][1], $ids )
->exec();
}
else {
$stmt = $dte->db()
->query( 'delete' )
->table( $this->_table )
->or_where( $this->_join['child'], $ids )
->exec();
}
}
private function _insert( $dte, $parentId, $data )
{
if ( isset($this->_join['table']) ) {
// Insert keys into the join table
$stmt = $dte->db()
->query('insert')
->table( $this->_join['table'] )
->set( $this->_join['parent'][1], $parentId )
->set( $this->_join['child'][1], $data[$this->_join['child'][0]] )
->exec();
}
else {
// Insert values into the target table
$stmt = $dte->db()
->query('insert')
->table( $this->_table )
->set( $this->_join['child'], $parentId );
for ( $i=0 ; $i_fields) ; $i++ ) {
$field = $this->_fields[$i];
if ( $field->apply( 'set', $data ) ) {
$stmt->set( $field->dbField(), $field->val('set', $data) );
}
}
$stmt->exec();
}
}
private function _update_row ( $dte, $parentId, $data )
{
if ( isset($this->_join['table']) ) {
// Got a link table, just insert the pkey references
$dte->db()->push(
$this->_join['table'],
array(
$this->_join['parent'][1] => $parentId,
$this->_join['child'][1] => $data[$this->_join['child'][0]]
),
array(
$this->_join['parent'][1] => $parentId
)
);
}
else {
// No link table, just a direct reference
$set = array(
$this->_join['child'] => $parentId
);
for ( $i=0 ; $i_fields) ; $i++ ) {
$field = $this->_fields[$i];
if ( $field->apply( 'set', $data ) ) {
$set[ $field->dbField() ] = $field->val('set', $data);
}
}
$dte->db()->push(
$this->_table,
$set,
array($this->_join['child'] => $parentId)
);
}
}
private function _fields ( $direction )
{
$fields = array();
for ( $i=0 ; $i_fields) ; $i++ ) {
$field = $this->_fields[$i];
if ( $field->apply( $direction, null ) ) {
if ( strpos( $field->dbField() , "." ) === false ) {
$fields[] = $this->_table.'.'.$field->dbField();
}
else {
$fields[] = $field->dbField() ." as '".$field->dbField()."'";
}
}
}
return $fields;
}
}
Allan
And I really need to join the tables and it still could not test the function.