Editor with JOIN

Editor with JOIN

asdrubalasdrubal Posts: 16Questions: 0Answers: 0
edited August 2012 in Editor
Hi,

I think I've seen this posted already but just can't find the topic.

My question is Can the Editor be used to update more than 1 table? Can I use JOIN for SQL query ?

Replies

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    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.

    Allan
  • asdrubalasdrubal Posts: 16Questions: 0Answers: 0
    Great to hear!!! Will check it out.
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    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

    Allan
  • asdrubalasdrubal Posts: 16Questions: 0Answers: 0
    edited August 2012
    Thanks!! Just checking it out.

    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]
    ?
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Hi,

    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
  • asdrubalasdrubal Posts: 16Questions: 0Answers: 0
    edited August 2012
    Getting this message:

    { "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?
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    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]

    Allan
  • agustin_garciaromeroagustin_garciaromero Posts: 42Questions: 0Answers: 0
    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]

    [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]
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Hi,

    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
  • agustin_garciaromeroagustin_garciaromero Posts: 42Questions: 0Answers: 0
    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]
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    edited March 2013
    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` .

    Allan
  • agustin_garciaromeroagustin_garciaromero Posts: 42Questions: 0Answers: 0
    Thank Allan,

    With your comments and this example I was able to achieve what I was looking for,
This discussion has been closed.