Editor SELECT-JOIN one to one statement troubleshooting.

davykiash

I would like to display data from a select-join statement with a one to one relationship. However I get the following error

"DataTables warning: table id=suppliers_table - Table part of field "uuid" was not found.
In Editor instances that use a join, all fields must have the database table set explicitly"

The code on my server side is as follows (The query in question is indented)

/*
    
    Query:
    
    SELECT items_suppliers.uuid,supplier.supplier_desc FROM items_suppliers 
    LEFT JOIN supplier ON supplier.uuid = items_suppliers.items_suppliers_link_supplier_uuid
    WHERE inventory_suppliers.items_suppliers_link_item_uuid = "item_uuid"
        

*/

                            
                
Editor::inst( $db_details, 'items_suppliers', 'uuid')
    ->fields(
    
        Field::inst( 'uuid' ),
        Field::inst( 'supplier.supplier_desc' ),
    
        )   
        
        ->leftJoin( 'supplier', 'supplier.uuid', '=', 'items_suppliers.items_suppliers_link_supplier_uuid' )
        
        ->where( 'inventory_suppliers.items_suppliers_link_item_uuid', item_uuid, '=' )
        
        ->process($_POST)
        
        ->json(false))
    
);

The editor on the client side

editor_supplier = new $.fn.dataTable.Editor( {
    "ajax": {
            "url": "../actions/process.php",
            "type": "POST",
            "data": function ( d ) {
                 return $.extend( {}, d, {
                   "item_uuid": "25",
                 } );
              },
          },
    table: "#inventory_table",
    fields: [ 
                {
                    label: "Supplier:",
                    name: "supplier_desc"
                },                              
        ]
});

At the datatable I will only need to remove (delete) the supplier

"buttons": [
    { extend: "remove"  , editor: editor_supplier ,text : "Remove Supplier","className": "btn btn-info"}
]

What am I missing?

tangerine

In Editor instances that use a join, all fields must have the database table set explicitly

       Field::inst( 'uuid' ),

allan

Yes - just adding items_suppliers as a prefix to the field name should fix it. You probably also want to add ->set( false ) to that, so your users can't modify the uuid!

Allan

davykiash

@tangerine I have explicit set the tables and now my code looks like this. It worked with no records. But after adding one record I encountered an error.

Editor::inst( $db_details, 'items_suppliers', 'items_suppliers.uuid')
    ->fields(
    
        Field::inst( 'items_suppliers.uuid' ),
        Field::inst( 'supplier.supplier_desc' ),
    
        )   
        
        ->leftJoin( 'supplier', 'supplier.uuid', '=', 'items_suppliers.items_suppliers_link_supplier_uuid' )
        
        ->where( 'items_suppliers.items_suppliers_link_item_uuid', item_uuid, '=' )
        
        ->process($_POST)
        
        ->json(false))
    
);
    

However am now getting a new error.

Duplicate field detected - a field with the name suppliers.uuid already exists..

What am I overlooking?

davykiash

I have located my issue. Thanks @allan and @tangerine.

The prefixing sorted the issue!

allan

I'd suggest detailing the question you have, showing the code you are using, detailing any error messages and linking to a test case showing the issue.

I'd also suggest not posting duplicate questions.

Allan