Select type multiple

Select type multiple

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

I have a select field with multiple set to true. When the user makes multiple selections the index for those selections are stored in the table being queried. I also have a left join so the user sees the full name and not the index number. I see the comma separated values in the database but I cannot figure out how to display it in the table when there are multiple selections.

I tried the following...

columns: [
   { 
      data: 'tbl_fullname.full_name',
      render: '[, ].support_requests.assigned'
   }
]

Am I even on the right track?
live.datatables.net/risefota/1/

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    I presume you are loading the table's data via Ajax on your actual page? Could you show me the JSON structure you are using please?

    Thanks,
    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Did you want the actual JSON data or this?


    <?php // DataTables PHP library include( "php/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\Upload, DataTables\Editor\Validate; /* * Example PHP implementation */ $out=Editor::inst( $db, 'support_requests' ) ->field( Field::inst( 'support_requests.date' ), Field::inst( 'support_requests.after_hours' ), Field::inst( 'support_requests.rdo' ), Field::inst( 'support_requests.category' ) ->options( 'lk_categories', 'lk_categories.id', 'lk_categories.category' ) ->validator( 'Validate::dbValues' ), Field::inst( 'lk_categories.category' ), Field::inst( 'support_requests.rank' ) ->options( 'lk_chp_ranks', 'lk_chp_ranks.id', 'lk_chp_ranks.rank' ) ->validator( 'Validate::dbValues' ), Field::inst( 'lk_chp_ranks.rank' ), Field::inst( 'support_requests.division' ) ->options( 'lk_chp_divisions', 'lk_chp_divisions.id', 'lk_chp_divisions.division' ) ->validator( 'Validate::dbValues' ), Field::inst( 'lk_chp_divisions.division' ), Field::inst( 'support_requests.summary' ), Field::inst( 'support_requests.assigned' ) ->options( 'oesa_users', 'oesa_users.id', 'oesa_users.full_name' ) ->validator( 'Validate::dbValues' ), Field::inst( 'tbl_fullname.full_name' ), Field::inst( 'support_requests.user_id' ) ->options( 'oesa_users', 'oesa_users.id', 'oesa_users.user_id' ) ->validator( 'Validate::dbValues' ), Field::inst( 'tbl_userid.user_id' ) ) ->leftJoin( 'lk_chp_ranks', 'lk_chp_ranks.id', '=', 'support_requests.rank' ) ->leftJoin( 'lk_chp_divisions', 'lk_chp_divisions.id', '=', 'support_requests.division' ) ->leftJoin( 'lk_categories', 'lk_categories.id', '=', 'support_requests.category' ) ->leftJoin( 'oesa_users as tbl_userid', 'tbl_userid.id', '=', 'support_requests.user_id' ) ->leftJoin( 'oesa_users as tbl_fullname', 'tbl_fullname.id', '=', 'support_requests.assigned' ) ->process($_POST) ->json();
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @th3t1ck ,

    The JSON , please, so what the server returns.

    Cheers,

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    There are over 700 entries. Here is one example.

    "}},{"DT_RowId":"row_629","support_requests":{
    "date":"2019-04-11",
    "after_hours":"No",
    "rdo":"No",
    "peer_time":null,
    "travel_time":null,
    "category":"11",
    "rank":"12",
    "division":"1",
    "summary":"FSA",
    "assigned":"7,15",
    "user_id":"7,15"
    },"

    So a user can select multiple employees (assign and user_id). The values are written to the database but only the first employee is show in the Datatable.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    And I need all the assigned and user_id's to show up in the datatable.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The [] syntax in render is used for an array of data. In the above you just have nested objects with string properties, so using the following should do it:

    columns: [
      { data: 'support_requests.assigned' },
      { data: 'support_requests.user_id' }
    ]
    

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I see I didn't explain this very well. Let me try again.

    In Editor I have the following.

    fields: [{
       label: 'Assigned Employee ID:',
       name: 'support_requests.user_id',
       type: 'select',
       multiple: true,
       seperator: ',',
       }, {
       label: 'Assigned Employee Name',
       name: 'support_requests.assigned',
       multiple: true,
       type: 'hidden',
       }
    ]...
    

    For the datatable I have this.

    columns: [
       { data: 'tbl_userid.user_id' },
       { data: 'tbl_fullname.full_name' }
    ],...
    

    For my editor instance I have the following.

    ->field(
       Field::inst( 'support_requests.assigned' )
          ->options( 'oesa_users', 'oesa_users.id', 'oesa_users.full_name' )
             ->validator( 'Validate::dbValues' ),
       Field::inst( 'tbl_fullname.full_name' ),
       Field::inst( 'support_requests.user_id' )
          ->options( 'oesa_users', 'oesa_users.id', 'oesa_users.user_id' )
             ->validator( 'Validate::dbValues' ),
       Field::inst( 'tbl_user_id.user_id' )
    )
    
       ->leftJoin( 'oesa_users as tbl_userid', 'tbl_userid.id', '=', 'support_requests.user_id' )
       ->leftJoin( 'oesa_users as tbl_fullname', 'tbl_fullname.id', '=', 'support_requests.assigned' )
    ...
    

    In the JSON data you can see two entries for assigned and user_id at the bottom.

    "}},{"DT_RowId":"row_629","support_requests":{
    "date":"2019-04-11",
    "after_hours":"No",
    "rdo":"No",
    "peer_time":null,
    "travel_time":null,
    "category":"11",
    "rank":"12",
    "division":"1",
    "summary":"FSA",
    "assigned":"7,15",
    "user_id":"7,15"

    },"

    However the datatable only shows the first entry and not multiples. I hope I did a better job explaining it this time.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    When I say the datatable is only showing the first entry I am talking about the 7. 15 does not show.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You should be able to use data: 'support_requests.assigned' / data: 'support_requests.user_id'. That should just show the plain text from those two properties.

    Do you have a renderer for the column perhaps? Remove that if so.

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    No render. I did as you suggested and it does show both index numbers now.

    But it should look like this but with 2 entries for each column.

    The entries are in another table so I'm doing some leftJoins to present the data but writing the index number to the table.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Ah! I see - thanks for the further explanation of what you are looking for.

    The best way to do this is with an Mjoin here rather than a left join, since you can have multiple assignee's.

    However, is support_requests.assigned in the database a text field? Rather than using a link table for referential integrity? If so, then you'll need to have a list of all assignee names and use columns.render to look up each id (splitting the string into id's).

    It really depends on your db structure!

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Cool. I'll try the Mjoin. The support_requests.assigned in an INT field that holds the index id of the employee and support_requests.user_id is the same setup. I may also look at using a link table. Might be easier to implement.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I was playing around with cross joins last night in some php code. Just wondering, does datatables support cross joins?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    The libraries we provide don't I'm afraid. They do a left join only.

    That said, you can use a VIEW which you could setup with your cross join and then read from that.

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    So much to learn for an old dude :)

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited May 2019

    I'll check that out. As always great from hearing from you and your colleges. Thank you.

This discussion has been closed.