How to display orthognal data

system.bonn@gmail.com

Hi, I have a many to many relationship like so

tables user, roles, role_user, where user belongsToMany roles.

Ajax request responses as follows (shortened):

{
"DT_RowId" : "7",
"name" : "IntSystem",
"roles" : [
            {
               "created_at" : "2019-10-26 17:26:19",
               "id" : "8",
               "name" : "IntSystem",
               "pivot" : {
                  "role_id" : "8",
                  "user_id" : "7"
               },
               "updated_at" : "2019-10-26 17:26:19"
            },
            {
               "created_at" : "2019-10-26 17:26:19",
               "id" : "1",
               "name" : "SysAdmin",
               "pivot" : {
                  "role_id" : "1",
                  "user_id" : "7"
               },
               "updated_at" : "2019-10-26 17:26:19"
            }
         ],
         "updated_at" : "2019-08-29 13:17:14"
}

To display the roles I use:

            columns: [
                {
                  data: 'roles[, ].name',
                  name: 'roles',
                  render: function (data, type, row) {
                            switch (type) {
                                case 'filter':
                                    data = 'foobar';
                                    break;
                            }
                            return data;
                          },
                orderData: 0 ,
                // searchable: false
                },

I added a filter input to the top of the rows:

            'initComplete': function() {
                                this.api().columns().every(function() {
                                    var column = this;
                                    var input = document.createElement('input');
                                    input.setAttribute('type', 'search');
                                    input.setAttribute('class', 'form-control form-control-sm');
                                    $(input).appendTo($(column.header())).on('keyup search', function() {
                                        column.search($(this).val(), false, false, true).draw();
                                    });
                                });
                            },

The display is fine, it renders the given row's role column as 'IntSystem, SysAdmin'.

However, when I try to filter for that colum, I get:

Unknown column 'users.roles'

which makes sense, since there is no 'users.role'. How can i make the filter work on the ouput, i.e. ''IntSystem, SysAdmin''.

I tried:

case 'filter':
     data = roles[, ].name;
     break;

This doesn't render at all and

case 'filter':
     data = 'roles[, ].name';
     break;

which gives

Unknown column 'users.roles'

again. So I changed to:

            columns: [
                {
                  data: 'roles[, ].name',
                  render: function (data, type, row) {
                            switch (type) {
                                case 'filter':
                                    data = 'foobar';
                                    break;
                            }
                            return data;
                          },
                orderData: 0 ,
                // searchable: false
                },

which gives:

Unknown column 'roles[, ].name'

Any idea how i could get this to work?

tia & cu,
ada

PS. What am I doing wrong with the code sections?

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

allan

I'm going to guess you are using server-side processing. Is that correct? If so, I'm afraid that Mjoin does not support filtering on orthogonal data at this time.

You would need to use client-side processing for this to work. How large is your data set?

Allan

system.bonn@gmail.com

Thank you very much for your reply! Yes, you guessed right, it's server-side processing. I estimate the possible size of the datataset to consist of up to a few thousand rows. What makes things worse, we prefer using the datatable's scroller feature.