Error with Mjoin

jeq214

I'm working with a view and two tables:
qryvoids <- voids_input <- empfile

Ultimately, I want to pull info from qryvoids, linked to voids_input, where users can modify fields in voids_input. I have a voids_input.manager_id field that is linked to empfile table, which will grab the name of the manager.

I get the following error:

DataTables warning: table id=void-records - Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing.

Here is my query:

$date = isset($_POST["date"]) ? date("Y-m-d",strtotime($_POST["date"])) : "";
$region = isset($_POST["region"]) ? $_POST["region"] : "";
$store = isset($_POST["store"]) ? $_POST["store"] : "";
$user = isset($_POST["user"]) ? $_POST["user"] : "";

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, "qryvoids" )
    ->fields(
            Field::inst( "qryvoids.store" ),
            Field::inst( "qryvoids.date" ),
            Field::inst( "qryvoids.check_num" ),
            Field::inst( "qryvoids.deletion" )
                ->options( Options:: inst()
                        ->table( "namedel" )
                        ->value( "code" )
                        ->label( "name" )
                        ->where( function ($q) use ($store) {
                            $q->where( "namedel.store", $store);
                        })
                ),
            Field::inst( "namedel.name" ),
            Field::inst( "qryvoids.menu_price" ),
            Field::inst( "voids_input.id" ),
            Field::inst( "voids_input.key" ),
            Field::inst( "voids_input.manager_id" )
                ->options( Options:: inst()
                    ->table( "empfile" )
                    ->value( "emp_number" )
                    ->label( array( "first_name", "last_name" ) )
                    ->where( function ($q) use ($store) {
                        $q->where( "store", $store);
                        $q->where( "emp_type", 'M');
                    })
            ),
            Field::inst( "voids_input.reason" ),
            Field::inst( "voids_input.user_id" )
    )
    ->leftJoin( "namedel", "namedel.code", "=", "qryvoids.deletion")
    ->leftJoin( "voids_input", "voids_input.key", "=", "qryvoids.id")
    ->join(
            Mjoin::inst("empfile")
            ->link("qryvoids.id", "voids_input.key")
            ->link("empfile.emp_number", "voids_input.manager_id")
            ->fields(
                    Field::inst("emp_number")
                    ->options( Options:: inst()
                            ->table( "empfile" )
                            ->value( "emp_number" )
                            ->label( array( "first_name", "last_name" ) )
                            ->where( function ($q) use ($store) {
                                    $q->where( "store", $store);
                                    $q->where( "emp_type", 'M');
                                })
                        ),
                    Field::inst( "empfile.first_name" ),
                    Field::inst( "empfile.last_name" )
                )
        )
    ->where( function ( $q ) {
        $q->where( "qryvoids.deletion", "(0,16)", 'NOT IN', false);
    })
    ->where( "qryvoids.store", $store )
    ->where( "qryvoids.date", $date )
    ->process($_POST)
    ->json();

Here is my js:

$("#void-records").DataTable({
    dom: "rtlp",
    ajax: {
        url: "/functions/updateVoids.php",
        type: "post",
        data: function(d){
            d.date = date = $("#curr-date").val();
            d.region = region = $("#curr-region").val();
            d.store = $("#curr-store").val();
        }
    },
    columns: [
        {
            data: null,
                defaultContent: "",
                className: "select-checkbox",
                "orderable": false, 
                "width": "5%"
        },
        {data: "qryvoids.check_num"},
        {data: "namedel.name"},
        {data: "qryvoids.menu_price", 
            render: $.fn.dataTable.render.number( ",", ".", 0, "$" ), 
            "width": "10%"
        },
        {
            data: null, 
            render: function(data, type, row){
                // Combine the first and last names into a single table field
                return data.empfile.first_name + " " + data.empfile.last_name;
            },
            editField: "voids_input.manager_id", 
            "width": "15%"
        },
        {
            data: "voids_input.reason", 
            "width": "50%",
                className: "editable"
        }
    ],
    keys: {
                 columns: ":not(:first-child)",
         editor:  voidEditor
    },
    select: {
                 style:    "os",
                 selector: "td:first-child",
                 blurable: true
        },
    buttons: [
        {extend: "edit", editor: voidEditor}
    ]
});

jeq214

I managed to get past this error by removing the table name from the fields, but now I get manager as "undefined undefined", even though I see the name coming back in the json data.

Updated php:

Editor::inst( $db, "qryvoids" )
    ->fields(
            Field::inst( "qryvoids.store" ),
            Field::inst( "qryvoids.date" ),
            Field::inst( "qryvoids.check_num" ),
            Field::inst( "qryvoids.deletion" )
                ->options( Options:: inst()
                        ->table( "namedel" )
                        ->value( "code" )
                        ->label( "name" )
                        ->where( function ($q) use ($store) {
                            $q->where( "store", $store);
                        })
                ),
            Field::inst( "namedel.name" ),
            Field::inst( "qryvoids.menu_price" ),
            Field::inst( "voids_input.id" ),
            Field::inst( "voids_input.key" ),
            Field::inst( "voids_input.manager_id" )
                ->options( Options:: inst()
                    ->table( "empfile" )
                    ->value( "emp_number" )
                    ->label( array( "first_name", "last_name" ) )
                    ->where( function ($q) use ($store) {
                        $q->where( "store", $store);
                        $q->where( "emp_type", 'M');
                    })
            ),
            Field::inst( "voids_input.reason" ),
            Field::inst( "voids_input.user_id" )
    )
    ->leftJoin( "namedel", "namedel.code", "=", "qryvoids.deletion")
    ->leftJoin( "voids_input", "voids_input.key", "=", "qryvoids.id")
    ->join(
            Mjoin::inst("empfile")
            ->link("qryvoids.id", "voids_input.key")
            ->link("empfile.emp_number", "voids_input.manager_id")
            ->fields(
                    Field::inst("emp_number")
                    ->options( Options:: inst()
                            ->table( "empfile" )
                            ->value( "emp_number" )
                            ->label( array( "first_name", "last_name" ) )
                            ->where( function ($q) use ($store) {
                                    $q->where( "store", $store);
                                    $q->where( "emp_type", 'M');
                                })
                        ),
                    Field::inst( "first_name" ),
                    Field::inst( "last_name" )
                )
        )
    ->where( function ( $q ) {
        $q->where( "qryvoids.deletion", "(0,16)", 'NOT IN', false);
    })
    ->where( "qryvoids.store", $store )
    ->where( "qryvoids.date", $date )
    ->process($_POST)
    ->json();

allan

Hi,

The issue here is that data.empfile is an array rather than an object. You need to loop over it since it is an Mjoin rather than just accessing the properties directly.

Something like:

data: 'empfile',
render: function ( data, type, row ) {
  var out = [];
  for ( var i=0, ien=data.length ; i<ien ; i++ ) {
    out.push( data[i].first_name +' '+ data[i].last_name );
  }
  return out.join( ', ' );
}

If you wanted just the last name it could be simplified to be:

data: 'empfile[, ].last_name'

And DataTables will do the loop for you. But to combine multiple fields it needs your own rendering function.

Regards,
Allan

jeq214

Thank you!