DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Example join.html doesn't work with large database

Example join.html doesn't work with large database

slemoineslemoine Posts: 6Questions: 0Answers: 0
edited March 2013 in Bug reports
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

Replies

  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Hi Stéphane,

    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
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Hi Stéphane,

    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
  • slemoineslemoine Posts: 6Questions: 0Answers: 0
    Hi 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]
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Can you remove the fnInitComplete please? DataTables doesn't pass the JSON value through to fnInitComplete when using server-side processing.

    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
  • slemoineslemoine Posts: 6Questions: 0Answers: 0
    Hi 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
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Thank you of highlighting the sort issue. I will look into it. I suspect that one will be a lot more difficult to resolve since the joined columns are effectively generated data.

    > 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
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    where to write this code?

    $('#example').one( 'xhr', function (e, o, json) {
    editor.field('dept.id').update( json.dept );
    editor.field('access[].id').update( json.access );
    } );
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    edited June 2013
    Somewhere before you initialise the DataTable probably, although its hard to say without a test case.

    Allan
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    I copied exactly the example above did the alteration but still does not work.
    alteration will be necessary any more?
    I'm using 1.2.3-Editor
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Can you please link to a test page so I can see what exactly is happening and what is going wrong? Does your table have an ID for `example` (which the above code requires)?

    Allan
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    <!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 "../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
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    php:

    <?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 );
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    json:

    {"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"}]}
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    DataTables\Editor\Join :

    <?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();
    }
    }
    }
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    ...
    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;
    }
    }
  • allanallan Posts: 63,682Questions: 1Answers: 10,498 Site admin
    Before I look in a lot of detail at this, I should ask, I see that your Editor trial expired last month. Have you purchased Editor under a different account? If so, could you confirm which account so I can update my records please?

    Allan
  • AlexonAlexon Posts: 8Questions: 0Answers: 0
    I am a first phase of tests to make sure that the editor meets my needs and then will proceed with the full acquisition.
    And I really need to join the tables and it still could not test the function.
This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.