Simple Join How change table id=example - Editor 1.3.3

Simple Join How change table id=example - Editor 1.3.3

magentoplaymagentoplay Posts: 7Questions: 3Answers: 0

Hi, I am new with the code :-)
I try to use the Simple Join:
DataTables-1.10.2/extensions/Editor-1.3.3/examples/simple/join.html
But I get this error:


DataTables warning: table id=example - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bk_sales_flat_order_grid.id' in 'field list'

I think the error is becuse the ID for the example table is the same:
user.id
sites.id

But in my case the ID is different like:
user.id
sites.id_order

Please can you give me a clear example to do this, I expend 6 hours to fix :(

Here is my own code:



<?php

// DataTables PHP library
include( "../../php/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
 */
$data = Editor::inst( $db, 'bk_sales_flat_order_grid' )
    ->field( 
        Field::inst( 'bk_sales_flat_order_grid.increment_id' ),
        Field::inst( 'bk_sales_flat_order_grid.billing_name' ),
        Field::inst( 'bk_sales_flat_order_grid.status' ),
        Field::inst( 'bk_sales_flat_order_grid.entity_id' ),
        Field::inst( 'bk_sales_flat_order_item.sku' )
    )
    ->leftJoin( 'bk_sales_flat_order_item', 'bk_sales_flat_order_item.order_id', '=', 'bk_sales_flat_order_grid.entity_id' )
    
    ->process($_POST)
    ->data();

if ( ! isset($_POST['action']) ) {
    // Get a list of sites for the `select` list
    $data['bk_sales_flat_order_item'] = $db
        ->selectDistinct( 'bk_sales_flat_order_item', 'order_id as value, sku as label' )
        ->fetchAll();
}


echo json_encode( $data );

Answers

  • tangerinetangerine Posts: 3,350Questions: 37Answers: 394

    $data = Editor::inst( $db, 'bk_sales_flat_order_grid' )

    From the docs:

    An optional third parameter can also be passed in which specifies the primary key of the table - the default is id.

    The Editor is looking for a field called 'id' in your table 'bk_sales_flat_order_grid'.

  • magentoplaymagentoplay Posts: 7Questions: 3Answers: 0

    In my table 'bk_sales_flat_order_grid' the ID is called 'entity_id'
    And I can´t change for id. I need used 'entity_id'

    Then What can I do?

  • tangerinetangerine Posts: 3,350Questions: 37Answers: 394

    Read my post properly. See the quote from the docs?

  • magentoplaymagentoplay Posts: 7Questions: 3Answers: 0
    edited September 2014

    Apologies tangerine.
    I used the option: "idSrc"
    And the Error gone. But the table is empty. Please any suggestion?
    Here is my Databases:

    TABLE: bk_sales_flat_order_grid

    | entity_id | increment_id | status | billing_name |

    | 1 | 10001 | complete | jhon |
    | 2 | 10002 | complete | susan |
    | 3 | 10003 | complete | Manuel |

    TABLE: bk_sales_flat_order_item

    | order_id | sku |

    | 1 | item1 |
    | 2 | item2 |
    | 3 | item3 |

    MY CODE:

    $(document).ready(function() {

    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/join.php",
        table: "bk_sales_flat_order_grid",
        idSrc: "entity_id",
        fields: [ {
                label: "First name:",
                name: "bk_sales_flat_order_grid.increment_id"
            }, {
                label: "Last name:",
                name: "bk_sales_flat_order_grid.billing_name"
            }, {
                label: "Phone #:",
                name: "bk_sales_flat_order_grid.status"
            }, {
                label: "Site:",
                name: "bk_sales_flat_order_grid.entity_id",
                type: "select"
    
            }
        ]
    } );
    
    $('bk_sales_flat_order_grid').dataTable( {
    
        dom: "Tfrtip",
    
        ajax: {
            url: "../php/join.php",
            type: 'POST'
        },
        columns: [
            { data: "bk_sales_flat_order_grid.increment_id" },
            { data: "bk_sales_flat_order_grid.billing_name" },
            { data: "bk_sales_flat_order_grid.status" },
            { data: "bk_sales_flat_order_item.sku" }
        ],
        tableTools: {
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        },
        initComplete: function ( settings, json ) {
            // Populate the site select list with the data available in the
            // database on load
            editor.field( 'bk_sales_flat_order_grid.entity_id' ).update( json.bk_sales_flat_order_item );
        }
    } );
    

    } );

  • magentoplaymagentoplay Posts: 7Questions: 3Answers: 0
    edited September 2014

    I fix! if you are using the example Simple > Join:

    on the path:
    DataTables-1.10.2/extensions/Editor-1.3.3/examples/simple/join.html

    And your database 'user' have a diferent 'id', you need add into the file:
    join.php

    this code:

    ->pkey( 'MyCustom_id' )

    For example, like this:

    $data = Editor::inst( $db, 'user' )

    ->pkey( 'MyCustom_id' )

    ->field( 
    

    ...

    enjoy!

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Hi,

    As @tangerine noted you can use the optional third parameter to the constructor to perform he same actions as calling the pkey() method. i.e.:

    $data = Editor::inst( $db, 'bk_sales_flat_order_grid' ) ->pkey( 'MyCustom_id' ) ->field(
    

    and

    $data = Editor::inst( $db, 'bk_sales_flat_order_grid', 'MyCustom_id' ) ->field(
    

    are identical.

    This isn't actually related to the fact that you are using a join at all, but rather that your primary key is not called id which Editor uses as the default.

    Allan

This discussion has been closed.