How can i add multiple values to a single column in datatable

Aryan1703

In editor i am usign multiple true to select different values. How do i modify my server-side script to store valules in form of arrays and get them back by joining the values of operator to users table to fetch all the values.

                {
                    label: "Operator",
                    name: 'users_enc[].id',
                    type: 'datatable',
                    multiple: true
                },

 Field::inst('R.operator', 'editoperator')
            ->options(
                Options::inst()
                    ->table('users_enc')
                    ->value('id')
                    ->label(array('lastname', 'firstname'))
                    ->render(function ($row) {
                        return $row['lastname'] . ', ' . $row['firstname'];
                    })
                    ->where(function ($q) {
                        $q->where('blocked', 0, '=');
                        $q->where('opsPDC', 1, '=');
                    })
                    ->order('lastname')
            )
            ->setFormatter(Format::ifEmpty(null))
            ->setFormatter(function ($val, $data, $opts) {
                return implode(',', $val);
            }),

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

rf1234

You need an Mjoin if you want to select and save multiple values in a link table for example.

If you just want to save multiple values in one field, e.g. separated by a comma, then you don't need an Mjoin.

This here doesn't require an Mjoin:

fields: [
      {
        label: lang === 'de' ? 'Auswahl Aufgaben-Fragen:' : 'Task Question Selection:',
        name: "vat_task.task_questions", //render vat_question.vat_number, vat_question.vat_name
        type: "select",
        multiple: true,
        separator: ', ',
        placeholder: lang === 'de' ? "Frage(n) für die Aufgaben-Erzeugung auswählen" : "Select Question(s) for Task Generation",
    }, {

on the server I save the comma separated values in one db-field, but you can also use a setFormatter and alter them before saving:

Field::inst( 'vat_task.task_questions' )
    ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
    ->options( Options::inst()
        ->table('vat_question')
        ->value('vat_question.vat_number')
        ->label( array('vat_question.vat_number', 'vat_question.vat_name') )
        ->render( function ( $row ) {               
            return $row['vat_question.vat_number'].': '.
                   $row['vat_question.vat_name'];
        } )
        ->order( 'vat_question.vat_number' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            $q ->where('vat_question.vat_subcategory_id', $_POST['parent_id']);
        } )
    ),            

allan

Some databases do support arrays in a single column (Postgres for example), but it isn't something that Editor's server-side libraries specifically support.

What is the data type of the operator field at the moment?

There are a few options, such as storing a comma separated list of the selected id's in the field, but the correct solution will depend a little on how you want to store it in the DB and what your DB is.

Allan

rf1234

Some databases do support arrays in a single column (Postgres for example), but it isn't something that Editor's server-side libraries specifically support.

So if you really want to save an array you would need a set formatter that converts e.g. a comma-separated list of values to an array. That could look like this for example (based on my example above):

Field::inst( 'yourField' )
    ->setFormatter( function ( $val, $data, $opts ) {
        return explode(', ', $val); 
    } )

Aryan1703

SO storing the data isn't a problem. Currently Operator(longtext) stores it in form "12,23,43" like this but what i want is to retrieve the values of it users by joining it with users table. I do not know if there is a way to join comma seprated values and retrieve the name

leftJoin('R.operator','users_enc.id','=');

And also i want them to be editable through datatable

rf1234

I do not know if there is a way to join comma seprated values and retrieve the name

No there isn't with Editor and if there was that wouldn't be good data modeling. You would need a link table assigning the selected users to whatever it is and the respectve Mjoin. Please consult the docs about this.

Here is an example:
https://editor.datatables.net/examples/advanced/joinArray.html

Please focus on this:

and this

"user_permission" is the link table between "user" and "permission" it consists of only two columns: "user_id" and "permission_id".

Such a link table would be required in your use case, too.

allan

Yup - 100%. Use an Mjoin. In a relational SQL database you would not easily be able to do a leftJoin on comma delimited foreign key ids like that. That's not really a limitation of Editor, but rather you need to change your data schema in the database to make better use of the database's abilities.

For example using the comma delimited values you won't be able to use referential integrity. (Actually, it is possible in some databases, again Postgres, but it isn't common in SQL).

Allan

Aryan1703

As per the need I have created a liniking table which has column(id(pkey),logID and operator).

Now it thorws me an error saying "DataTables warning: table id=runsheetTable - Join was performed on the field 'operator' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance." I have included R.operator as rregular field

Editor::inst($db, 'ops_runSheet R', 'R.logID')
    ->field(
        Field::inst('R.logID'),
        Field::inst('R.id', 'id')
            ->setFormatter(Format::ifEmpty(null)),

        Field::inst('R.operator', 'editoperator')
            ->options(
                Options::inst()
                    ->table('users_enc')
                    ->value('id')
                    ->label(array('lastname', 'firstname'))
                    ->render(function ($row) {
                        return $row['lastname'] . ', ' . $row['firstname'];
                    })
                    ->where(function ($q) {
                        $q->where('blocked', 0, '=');
                        $q->where('opsPDC', 1, '=');
                    })
                    ->order('lastname')
            )
            ->setFormatter(Format::ifEmpty(null))
            ->setFormatter(function ($val, $data, $opts) {
                return implode(',', $val);
            }),
        Field::inst('R.operator'),

    )
 ->join(
        Mjoin::inst('users_enc')
            ->link('R.operator', 'runsheetOperator.logID')
            ->link('users_enc.id', 'R.operator')
            ->order('firstname asc')
            ->validator('users_enc[].id', Validate::mjoinMaxCount(4, 'No more than four selections please'))
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(
                        Options::inst()
                            ->table('users_enc')
                            ->value('id')
                            ->label('firstname')
                    ),
                Field::inst('firstname')
            )
    )

allan

I think the SQL aliasing is confusing it. Could you try ops_runSheet as the table name, rather than aliasing it as R please?

Allan

Aryan1703

I have tried that too still has the same error.

   ->join(
        Mjoin::inst('users_enc')
            ->link('ops_runSheet.logID', 'runsheetOperator.logID')
            ->link('users_enc.id', 'runsheetOperator.Operator')
            ->order('firstname asc')
            ->validator('users_enc[].id', Validate::mjoinMaxCount(4, 'No more than four selections please'))
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(
                        Options::inst()
                            ->table('users_enc')
                            ->value('id')
                            ->label('firstname')
                    ),
                Field::inst('firstname')
            )
    )

Aryan1703

Any updates on this?

Thanks in advance

rf1234

Your link table looks wrong. It may only contain two columns
a) id of the first table (foreign key)
b) id of the second table (foreign key)

It may not have its own id.

Here is an example from my own data model:

In the example you see it as well:

The link table is called user_permission and it has precisely two columns which are both foreign keys. Please design your Mjoin exactly like this.

Aryan1703

Here i have updated my join and database for only two columns but the error still persits.

 ->join(
        Mjoin::inst('users_enc') 
            ->link('R.logID', 'runsheetOperator.log')
            ->link('users_enc.id', 'runsheetOperator.operatorId')
            ->order('firstname asc')
            ->validator('users_enc[].id', Validate::mjoinMaxCount(4, 'No more than four selections please'))
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(
                        Options::inst()
                            ->table('users_enc')
                            ->value('id')
                            ->label('firstname')
                    ),
                Field::inst('firstname')
            )
    )

allan

->link('R.logID',

You are still using an alias here. Could you remove the alias completely and then show me the full initialisation you are using for the PHP Editor instance, as well as the latest error message please?

Allan

Aryan1703

This is the updated version and the error

<?php
include_once ("../utils.php");
check_session();

// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

include ("../../lib/DataTables.php");
require_once ("../../../functions/SQLFunctions.php");

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;


Editor::inst($db, 'ops_runSheet ops_runSheet', 'ops_runSheet.logID')
    ->field(
        Field::inst('ops_runSheet.logID'),
        Field::inst('ops_runSheet.logID', 'logID'),
        Field::inst('ops_runSheet.id', 'id')
            ->setFormatter(Format::ifEmpty(null)),
        Field::inst('U.username', 'deliveredBy'),
        Field::inst('T.Track', 'track'),
        Field::inst('ops_runSheet.Direction', 'direction'),
        Field::inst('ops_runSheet.Position', 'Position')
            ->setFormatter(Format::ifEmpty(null)),

        Field::inst('ops_runSheet.adepartTime')
            ->setFormatter(Format::ifEmpty(null)),
        Field::inst('O2.Mode', 'departHandoff'),

        Field::inst('ops_runSheet.adepartTime', 'departHandoff')
            ->getFormatter(function ($val, $data, $field) {
                if ($val === null) {
                    return "Arrival Time: " . '<br>' . "Mode: " . $data["O2.Mode"];
                }
                $departTime = date('H:i', strtotime($val));
                return "Departure Time: " . $departTime . '<br>' . "Mode: " . $data["O2.Mode"];
            }),
        Field::inst('ops_runSheet.CoupledTrain')
            ->options(function () {
                $query = "SELECT Train FROM Coupled_Train ORDER BY id DESC LIMIT 1";
                $result = query($query);
                $options = [];
                while ($row = mysqli_fetch_assoc($result)) {
                    $trainData = json_decode($row['Train'], true); // Decoding JSON here
                    foreach ($trainData as $vehicleNumber => $trainNumbers) {
                        $label = implode(',', $trainNumbers); // Concatenate train numbers
                        $options[] = ['label' => $label, 'value' => $label];
                    }
                }
                return $options;
            })
            ->setFormatter(Format::ifEmpty(null)),

        Field::inst('ops_runSheet.comments'),
        Field::inst('ops_runSheet.changeOffReason'),
        Field::inst('ops_runSheet.leadMileage', 'leadMileage')
            ->setFormatter(Format::ifEmpty(null)),
        Field::inst('ops_runSheet.midMileage', 'midMileage')
            ->setFormatter(Format::ifEmpty(null)),
        Field::inst('ops_runSheet.trailMileage', 'trailMileage')
            ->setFormatter(Format::ifEmpty(null)),
        Field::inst('ops_runSheet.operator'),

    )
    ->leftJoin('users_enc U', 'U.id', '=', 'ops_runSheet.deliveredBy')
    ->leftJoin('ops_YCCtrack T', 'T.id', '=', 'ops_runSheet.Track')
    ->leftjoin('lrv_list A3', 'A3.id', '=', 'ops_runSheet.replaceLead')
    ->leftjoin('lrv_list A4', 'A4.id', '=', 'ops_runSheet.replaceTrail')
    ->leftjoin('ops_operationMode O', 'O.id', '=', 'ops_runSheet.handoffMode')
    ->leftjoin('ops_operationMode O2', 'O2.id', '=', 'ops_runSheet.departHandoff')
    ->join(
        Mjoin::inst('users_enc')
            ->link('ops_runSheet.logID', 'runsheetOperator.log')
            ->link('users_enc.id', 'runsheetOperator.operatorId')
            ->order('firstname asc')
            ->validator('users_enc[].id', Validate::mjoinMaxCount(4, 'No more than four selections please'))
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(
                        Options::inst()
                            ->table('users_enc')
                            ->value('id')
                            ->label('firstname')
                    ),
                Field::inst('firstname')
            )
    )

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


<?php
>
?>

This is the error i am getting

"{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.logID as 'dteditor_pkey', users_enc.id as 'id', users_enc.firstname ...' at line 1","data":[],"ipOpts":[],"cancelled":[],"debug":["Editor PHP libraries - version 2.2.2",{"query":"SELECT ops_runSheet.logID as 'ops_runSheet.logID', ops_runSheet.id as 'ops_runSheet.id', U.username as 'U.username', T.Track as 'T.Track', ops_runSheet.Direction as 'ops_runSheet.Direction', ops_runSheet.Position as 'ops_runSheet.Position', ops_runSheet.leadLrv as 'ops_runSheet.leadLrv', ops_runSheet.midLrv as 'ops_runSheet.midLrv', ops_runSheet.trailLrv as 'ops_runSheet.trailLrv', ops_runSheet.sdepartTime as 'ops_runSheet.sdepartTime', ops_runSheet.arrivalHandoff as 'ops_runSheet.arrivalHandoff', O.Mode as 'O.Mode', ops_runSheet.arrivalHandoff as 'ops_runSheet.arrivalHandoff', ops_runSheet.adepartTime as 'ops_runSheet.adepartTime', O2.Mode as 'O2.Mode', ops_runSheet.adepartTime as 'ops_runSheet.adepartTime', ops_runSheet.CoupledTrain as 'ops_runSheet.CoupledTrain', ops_runSheet.comments as 'ops_runSheet.comments', ops_runSheet.changeOffReason as 'ops_runSheet.changeOffReason', ops_runSheet.leadMileage as 'ops_runSheet.leadMileage', ops_runSheet.midMileage as 'ops_runSheet.midMileage', ops_runSheet.trailMileage as 'ops_runSheet.trailMileage', ops_runSheet.leadendMileage as 'ops_runSheet.leadendMileage', ops_runSheet.midendMileage as 'ops_runSheet.midendMileage', ops_runSheet.trailendMileage as 'ops_runSheet.trailendMileage', A3.lrv_no as 'A3.lrv_no', A4.lrv_no as 'A4.lrv_no', ops_runSheet.createdOn as 'ops_runSheet.createdOn', ops_runSheet.Track as 'ops_runSheet.Track', ops_runSheet.deliveredBy as 'ops_runSheet.deliveredBy', ops_runSheet.operator as 'ops_runSheet.operator', ops_runSheet.handoffMode as 'ops_runSheet.handoffMode', ops_runSheet.departHandoff as 'ops_runSheet.departHandoff', ops_runSheet.leadLRV as 'ops_runSheet.leadLRV', ops_runSheet.trailLrv as 'ops_runSheet.trailLrv', ops_runSheet.replaceLead as 'ops_runSheet.replaceLead', ops_runSheet.replaceTrail as 'ops_runSheet.replaceTrail', ops_runSheet.operator as 'ops_runSheet.operator' FROM ops_runSheet ops_runSheet LEFT JOIN users_enc U ON U.id = ops_runSheet.deliveredBy LEFT JOIN ops_YCCtrack T ON T.id = ops_runSheet.Track LEFT JOIN lrv_list A3 ON A3.id = ops_runSheet.replaceLead LEFT JOIN lrv_list A4 ON A4.id = ops_runSheet.replaceTrail LEFT JOIN ops_operationMode O ON O.id = ops_runSheet.handoffMode LEFT JOIN ops_operationMode O2 ON O2.id = ops_runSheet.departHandoff ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', Track as 'Track' FROM ops_YCCtrack ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', lastname as 'lastname', firstname as 'firstname' FROM users_enc WHERE (blocked = :where_1 AND opsPDC = :where_2 ) ORDER BY lastname ","bindings":[{"name":":where_1","value":0,"type":null},{"name":":where_2","value":1,"type":null}]},{"query":"SELECT DISTINCT id as 'id', lastname as 'lastname', firstname as 'firstname' FROM users_enc WHERE (blocked = :where_1 AND opsPDC = :where_2 ) ORDER BY lastname ","bindings":[{"name":":where_1","value":0,"type":null},{"name":":where_2","value":1,"type":null}]},{"query":"SELECT DISTINCT id as 'id', Mode as 'Mode' FROM ops_operationMode ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', Mode as 'Mode' FROM ops_operationMode ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', lrv_no as 'lrv_no' FROM lrv_list ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', lrv_no as 'lrv_no' FROM lrv_list ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', lrv_no as 'lrv_no' FROM lrv_list ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT id as 'id', lrv_no as 'lrv_no' FROM lrv_list ORDER BY id ","bindings":[]},{"query":"SELECT DISTINCT ops_runSheet ops_runSheet.logID as 'dteditor_pkey', users_enc.id as 'id', users_enc.firstname as 'firstname' FROM ops_runSheet ops_runSheet ops_runSheet ops_runSheet JOIN ops_runSheet ON ops_runSheet ops_runSheet.logID = ops_runSheet.log JOIN users_enc ON users_enc.id = ops_runSheet.operatorId WHERE ops_runSheet ops_runSheet.logID IN (:wherein1, :wherein2, :wherein3, :wherein4, :wherein5, :wherein6, :wherein7, :wherein8, :wherein9, :wherein10, :wherein11, :wherein12, :wherein13) ORDER BY firstname asc ","bindings":[{"name":":wherein1","value":874,"type":null},{"name":":wherein2","value":875,"type":null},{"name":":wherein13","value":886,"type":null}]}]}"

Aryan1703

Thank you I have made couple of change and it worked, but, another question. I have another column named delivered BY which is also a list of people so would i have to create another table in database and create another join or could we modify the code to add in the same table.

allan

What were the changes out of interest? I notice you still have an alias, but with itself as the name ('ops_runSheet ops_runSheet') which would probably still cause an issue.

Deliveredby sounds like it might be a left join off one of the tables, but I'm not sure which one?

Allan

rf1234

If "delivered by" is a list of people I guess this sounds more like an N:M relationship, but could also be 1:N. If it is N:M you would need another link table to resolve that relationship and another Mjoin, too.

If you describe the logical relationships between your tables in more detail I could give you a recommendation.

Aryan1703

NO, I totally removed the alias and just used the table name. That helped me, and talking about the "deliveredBy" i do not need it at the moment so its good.

Thanks for your help.

THe code in case you need it

 ->join(
        Mjoin::inst('users_enc')
            ->link('ops_runSheet.logID', 'ops_runsheetOperator.log')
            ->link('users_enc.id', 'ops_runsheetOperator.operatorId')
            ->validator('users_enc[].id', Validate::mjoinMaxCount(4, 'No more than four selections please'))
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(
                        Options::inst()
                            ->table('users_enc')
                            ->value('id')
                            ->label(array('lastname', 'firstname'))
                            ->render(function ($row) {
                                return $row['lastname'] . ', ' . $row['firstname'];
                            })
                            ->where(function ($q) {
                                $q->where('blocked', 0, '=');
                                $q->and_where('opsPDC', 1, '=');
                            })
                            ->order('lastname')
                    ),
                Field::inst('username')
            )
    )

allan

Cool - good to know you got it going!

Allan