Datatable Editor node.js - How to filter on an mjoined table?
Datatable Editor node.js - How to filter on an mjoined table?
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
This discussion has been closed.
Answers
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
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':
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:
... 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
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
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
inputcalldraw()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