Datatable Editor node.js - How to filter on an mjoined table?

Capamania

I'm using editor with node.js. How to filter on an mjoin the joined table?

I have this mjoin:

let editor = new Editor( db, 'glossary', 'id' )
.fields(
    new Field( 'glossary.id' ),
    new Field( 'glossary.field_name' )
)    
.join(
  new Mjoin('groups')
    .link('glossary.id', 'glossary_groups.glossary_id')
    .link('groups.id', 'glossary_groups.group_id')
    .order('name asc')
    .fields(
      new Field('id')
        .options(new Options()
          .table('groups')
          .value('id')
          .label('name')
        ),
      new Field('name')
    )
)

Which renders:

data    [ {…}, {…}, {…}, … ]
    0   Object { DT_RowId: "row_51", glossary: {…}, groups: [] }
        DT_RowId    "row_51"
        glossary    Object { id: 51, … }
            id  51
            field_name  "Field Name 51"
        groups  []
    1   Object { DT_RowId: "row_226", glossary: {…}, groups: […] }
        DT_RowId    "row_226"
        glossary    Object { id: 226, … }
            id  226
            field_name  "Field Name 226"
        groups  [ {…}, {…} ]
            0   Object { id: 59, name: "Group 59", … }
            1   Object { id: 60, name: "Group 60", … }
    2   Object { DT_RowId: "row_147", glossary: {…}, groups: [] }
        DT_RowId    "row_147"
        glossary    Object { id: 147, … }
            id  147
            field_name  "Field Name 147"
        groups  []
    3   Object { DT_RowId: "row_149", glossary: {…}, groups: […] }
        DT_RowId    "row_149"
        glossary    Object { id: 149, … }
            id  149
            field_name  "Field Name 149"
        groups  [ {…} ]
            0   Object { id: 59, name: "Group 59", … }

How can I filter (OR) on the 'groups' table e.g. [ '59', '60' ]?

// getData.selectGroupID:
// [ '59', '60' ]

So result:

data    [ {…}, {…}, {…}, … ]
    1   Object { DT_RowId: "row_226", glossary: {…}, groups: […] }
        DT_RowId    "row_226"
        glossary    Object { id: 226, … }
            id  226
            field_name  "Field Name 226"
        groups  [ {…}, {…} ]
            0   Object { id: 59, name: "Group 59", … }
            1   Object { id: 60, name: "Group 60", … }
    3   Object { DT_RowId: "row_149", glossary: {…}, groups: […] }
        DT_RowId    "row_149"
        glossary    Object { id: 149, … }
            id  149
            field_name  "Field Name 149"
        groups  [ {…} ]
            0   Object { id: 59, name: "Group 59", … }

I tried:

editor.where( function () {
    this
      .orWhere( function () {
        for ( let i=0; i < getData.selectGroupID.length; i++ ){
          this.orWhere( 'groups.id', '( SELECT DISTINCT group_id FROM glossary_groups )', 'IN', false);   
        }
      })
});   

But I'm getting this error:

TypeError: The operator "( SELECT DISTINCT group_id FROM glossary_groups )" is not permitted

I also tried:

editor.where( function () {

    this
      .orWhere( function () {
        for ( let i=0; i < getData.selectGroupID.length; i++ ){
          //this.orWhere( 'groups.id', getData.selectGroupID[i] );
          //this.orWhere( 'groups.id', 'SELECT DISTINCT group_id FROM glossary_groups', '=', getData.selectGroupID[i] );
          //this.orWhere( 'glossary.id', '=', 51 );
          //this.orWhere( 'glossary.id', '( SELECT DISTINCT group_id FROM glossary_groups )', 'IN', [[59, 60]] );
          //this.orWhere( 'glossary.id', '( SELECT DISTINCT glossary_id FROM glossary_groups )', 'IN', false);        
        }
      })

});  

On this post they suggest a raw select distinct:

https://datatables.net/forums/discussion/comment/178893#Comment_178893

//check whether the respective user has any permission
    ->where( function($q) {
        $q  ->where( 'users.id', 
               '( SELECT DISTINCT user_id   
                    FROM user_permission
                  )', 'IN', false);

and I think this is a https://editor.datatables.net/manual/php/conditions#Sub-selects

But how to perform the same logic with node.js? https://editor.datatables.net/manual/nodejs/conditions

allan

What kind of filter do you want to do? Are you limiting the entire result set by what is filtered on in the m-joined table? i.e. if there is no matching record in the m-joined table, then the host row is not shown?

That is not a feature of Editor at the moment I'm afraid. It would be possible to do on the client-side with a custom filter, but that might not be good enough for you.

Could you explain a little more about your use case and also how large the data set is please?

Thanks,
Allan

Capamania

Taking your server side example: https://editor.datatables.net/examples/advanced/joinArray.html

I want to have a multiselect (http://davidstutz.github.io/bootstrap-multiselect/) filter on column 'permission':

var permission_values = [
    { label: "Accounts", value: "Accounts" },
    { label: "Desktop", value: "Desktop" },
    { label: "Printer", value: "Printer" },
    { label: "Servers", value: "Servers" },
    { label: "VMs", value: "VMs" },
    { label: "Web-site", value: "Web-site" }
];
$('.select-permission').multiselect('dataprovider', glossary_visible);

So if I apply the filter on any of those or multiple values I only want users that are in a group of the approriate applied filter parameters to be in the search result. I need it server side.

So the filter parameter sent to the server side script will be:

[ 'Accounts', 'Desktop' ]

... if I want to search for user that have the permission 'Accounts' or 'Desktop'

The table will be quite large.

'i.e. if there is no matching record in the m-joined table, then the host row is not shown?' so yes here I think

Capamania

But I think I need the same as discussed here: https://datatables.net/forums/discussion/comment/178893#Comment_178893 ... but the example is with the php libraries. With the https://editor.datatables.net/manual/php/conditions#Sub-selects logic

allan

Are you using server-side processing? If so, this is going to be really complicated I think. If you aren't, then both our SearchBuilder and SearchPane extensions for DataTables will be able to filter on array data like that.

However, if you want a more traditional dropdown, I think you'd probably need to create a custom filter that will operate on the array of data for each cell there. Specifically, on input call draw() which in turn triggers any [custom search functions](https://datatables.net/manual/plug-ins/search_ you have.

Long story short - we need a bit more information, but unless you are using SearchPanes or SearchBuilder, this is going to involve you writing some code for it to work.

Allan