Nested Editor

gatesnet

Hello,

I built a nested editor one for products and another one for prices. the relationship is one-to-many. 1 product can have multiple prices. everything is working fine but when selecting the product and editor open I need to get the prices for the selected products.

When I replace this one for example

$q->where('prices.product_id','products.id');

with

$q->where('prices.product_id','1');

I got the correct prices but this is a fixed value. How can I assign products.id in a dynamic when the row is selected and the editor is open?

and also, when I edit the price row in the nested editor, the new editor opens but it's empty. it should have the values of the selected price.

**prices.php controller. **

<?php
   session_start();

/*
 * Example PHP implementation used for the index.html example
 */
 
// 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\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    function logChange ( $db, $action, $id, &$values ) {
        $db->insert( 'log', array(
            'user'   => $_SESSION['name'],
            'action' => $action,
            'values' => json_encode( $values ),
            'row'    => $id,
            'when'   => date('Y-m-d H:i:s')
        ) );
    }
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'prices' )
    ->fields(
        Field::inst( 'prices.id' ),
        Field::inst( 'prices.price' ),
        Field::inst( 'prices.start_date' ),
        Field::inst( 'prices.end_date' ),
        Field::inst( 'prices.product_id' )
    )
    ->leftJoin('products', 'products.id', '=', 'prices.product_id')
    ->where(function ($q) {
        $q->where('prices.product_id','products.id');
    })


    ->on( 'postCreate', function ( $editor, $id, &$values, &$row ) {
        logChange( $editor->db(), 'create', $id, $values );
    } )
    ->on( 'postEdit', function ( $editor, $id, &$values, &$row ) {
        logChange( $editor->db(), 'edit', $id, $values );
    } )
    ->debug(true)
    ->process( $_POST )
    ->json();

products.php controllers:

<?php
session_start();

/*
* Example PHP implementation used for the index.html example
*/

// 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\Mjoin,
 DataTables\Editor\Options,
 DataTables\Editor\Upload,
 DataTables\Editor\Validate,
 DataTables\Editor\ValidateOptions;

 function logChange ( $db, $action, $id, &$values ) {
     $db->insert( 'log', array(
         'user'   => $_SESSION['name'],
         'action' => $action,
         'values' => json_encode( $values ),
         'row'    => $id,
         'when'   => date('Y-m-d H:i:s')
     ) );
 }



// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'products' )
 ->fields(
     //Field::inst( 'products.id' ),
     //Field::inst( 'image' ),
     Field::inst( 'products.internalcode' ),
     Field::inst( 'products.batch' ),
     Field::inst( 'products.brand' )           
     ->validator( Validate::notEmpty( ValidateOptions::inst()
     ->message( 'A Brand is required' )   
     ) ),
     Field::inst( 'products.vendorcode' ),
     /* Field::inst( 'price_row.price' )
         ->validator( Validate::numeric() )
         ->setFormatter( Format::ifEmpty(null) ), */
     Field::inst( 'products.sellingprice' )
         ->validator( Validate::numeric() )
         ->setFormatter( Format::ifEmpty(null) ),      
     Field::inst( 'products.name' )
         ->validator( Validate::notEmpty( ValidateOptions::inst()
             ->message( 'A Name is required' )   
         ) ),
     Field::inst( 'products.collection' ),
     Field::inst( 'products.modale_category' )
         ->validator( Validate::notEmpty( ValidateOptions::inst()
             ->message( 'A Category is required' )   
         ) ),
     Field::inst( 'products.modale_subcategory' )
         ->validator( Validate::notEmpty( ValidateOptions::inst()
             ->message( 'A Sub Category is required' )   
         ) ),
     Field::inst( 'products.dimensions' ),         
     Field::inst( 'products.designedby' ),
     Field::inst( 'products.materialfinishes' ),
     Field::inst( 'products.specifications' ),       
     Field::inst( 'products.description' ),     
     Field::inst( 'products.catalog_type' )
     ->validator( Validate::notEmpty( ValidateOptions::inst()
                 ->message( 'A Catalog Type is required' )   
             ) ), 
     Field::inst( 'products.colors' ), 
     Field::inst( 'products.unit' ), 
     Field::inst( 'products.weight' ),
     Field::inst( 'products.image_url' ),

     Field::inst( 'products.id' )
    )

     
    ->join(
        Mjoin::inst('files')
            ->link('products.id', 'products_files.product_id')
            ->link('files.id', 'products_files.file_id')
            ->fields(
                Field::inst('filename'),
                Field::inst('id')
            ),
        Mjoin::inst('prices')
            ->link('products.id', 'prices.product_id')
            ->fields(
                Field::inst('price')
            )
    )

 ->on( 'postCreate', function ( $editor, $id, &$values, &$row ) {
     logChange( $editor->db(), 'create', $id, $values );
 } )

 ->on( 'postEdit', function ( $editor, $id, &$values, &$row ) {
     logChange( $editor->db(), 'edit', $id, $values );
 } )






 ->debug(true)
 ->process( $_POST )
 ->json();
?>

Attached also a screenshot of the nested editors.

allan

I think this is the same as your other thread?

Allan

gatesnet

Thank you for the reply. But it’s not related to my question. Can you please take a look again on my post ?

Thanks

allan

Try replacing:

$q->where('prices.product_id','products.id');

with:

$q->where('prices.product_id','products.id', '=', false);

At the moment your query is resulting in WHERE prices.product_id = "products.id". That is always going to fail. Using the optional fourth parameter will stop the data binding.

See this section of the manual for more details.

Allan

gatesnet

Did not work! It gets all prices regardless of the selected product row.

allan

I think I might have missed something about this before - sorry. The list of "Cost Price" options - does that change for each item being selected? If so, an Ajax request will be needed on each edit to allow the data for that row to be retrieved.

This example shows how that might be done. Note the use of initEdit on line 116 (in the code shown below the table) which triggers an Ajax request to get the options to show for the parent item being edited. Do you have something like that on your page?

Allan

gatesnet

Thank you! But in mentioned example there is no sample of server-side script

allan

The two scripts used in that example are available as part of the PHP download package for Editor. Specifically have a look at the controllers/users.php and controllers/sites.php files.

Allan