Simple Join How change table id=example - Editor 1.3.3

magentoplay

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 );

---

tangerine

$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'.

magentoplay

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?

tangerine

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

magentoplay

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 );
    }
} );

} );

magentoplay

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!

allan

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