Select type multiple

th3t1ck

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/

allan

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

th3t1ck

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

colin

Hi @th3t1ck ,

The JSON , please, so what the server returns.

Cheers,

Colin

th3t1ck

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.

th3t1ck

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

allan

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

th3t1ck

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.

th3t1ck

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

allan

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

th3t1ck

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.

allan

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

th3t1ck

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.

th3t1ck

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

allan

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

th3t1ck

So much to learn for an old dude

th3t1ck

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