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

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

Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1
edited April 18 in Editor

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.

Answers

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406

    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']);
            } )
        ),            
    
  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406
    edited April 18

    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); 
        } )
    
  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

    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

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406
    edited April 18

    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.

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

    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') ) )
  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1
    edited April 19

    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')
                )
        )
    
  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

    Any updates on this?

    Thanks in advance

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406
    edited April 22

    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.

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

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

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1
    edited April 22

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

    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}]}]}"

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1
    edited April 22

    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.

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406

    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.

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

    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')
                )
        )
    
    
  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Cool - good to know you got it going!

    Allan

Sign In or Register to comment.