Support SearchPane extension

[VUnguryan]

Thank you for your work, this is great!

Do you plan support for the SearchPane extension?

Maybe there are ideas how to extend the DataTablesInput and DataTablesOtput classes?

[darrachequesne]

@VUnguryan Hi! That sounds interesting, it should be possible to convert the searchPanes attribute into some GROUP BY / COUNT queries, and add the results to the output.

I won't have much time right now though. Would you have time to implement it yourself?

[VUnguryan]

@darrachequesne I can try. Until I found your masterpiece, I almost made my implementation :)

But I need help converting example input and output json to classes.

"searchPanes":{
    "options":{
        "users.first_name":[
            {
               "label":"Aaron",
               "total":"1",
               "value":"Aaron",
               "count":"1"
            },
            {
               "label":"Alex",
               "total":"1",
               "value":"Alex",
               "count":0
            },
            {
               "label":"Alexa",
               "total":"1",
               "value":"Alexa",
               "count":0
            },
            ...
        ]
    }
},

[darrachequesne]

The following should work I think:

@Data
public static class SearchPanes {
    private Map<String, List<Item>> options;
}

@Data
public static class Item {
    private String label;
    private long total;
    private String value;
    private long count;
}

[VUnguryan]

Thanks it works. Now I can not figure out the input format :(

When you select a search filter in the controller comes in this form:

...
&search[regex]=false
&searchPanes[level][0]=0
&searchPanes[level][1]=1
&searchPanes[level][2]=2
&searchPanes[level][3]=3
&searchPanes[level][4]=4

Unfortunately this does not work.

public class SearchPanesInput extends DataTablesInput{
    private List<Map<String, String>> searchPanes;
}

[darrachequesne]

The mapping of the input is indeed a bit harder...

With the default GET request, the param is added as &searchPanes.firstName.0=value1&searchPanes.firstName.1=value2, which does not play well with Spring @RequestParam handing...

Besides, it seems the searchPanes attribute is added after the data method here, so we can't reformat it to our will:

function flatten(params) {
  // params.searchPanes is undefined here

  return params;
}

$('table#sample').DataTable({
  'ajax': {
    'url': '/data/users',
    'type': 'GET',
    'data': flatten
  }
})

@RequestParam Map<String, String> does work (the map contains searchPanes.firstName.0 => value1), but it does feel like a hack...

public DataTablesOutput<Employee> list(@Valid DataTablesInput input, @RequestParam Map<String, String> params) {
  // ...
}

Have you found a better solution?

[VUnguryan]

Thanks for the idea, but it does not work for me. I am unfortunately very weak in JS :(

The frontend for DataTables now looks like this for me:

        var table = $('#example').DataTable( {
              searchPanes: {
                  dtOpts: { searching: false },
                  columns: [0, 1],
                },
            dom: 'Pfrtip',
            iDisplayLength: 25,
            serverSide : true,
            ajax : {
                              contentType: 'application/json',
                              url: '/spells',
                              type: 'POST',
                              data: function (d) {
                                 return JSON.stringify(d);
                             }
            },

Input json for searchPanes

{"draw":15,"columns":
[{"data":"level","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}},{"data":"name","name":"","searchable":true,"orderable":true,"search":{"value":"","regex":false}}],"
order":[{"column":0,"dir":"asc"}],"start":0,"length":10,"search":{"value":"","regex":false},
"searchPanes":{"level":{"0":"3","1":"4"},"name":{}}}

`Thanks again for your help, it is invaluable.

[VUnguryan]

Hooray! It works! http://dnd5.club/hero/spells/table

[darrachequesne]

Awesome! Could you please explain how you achieve this result?

[VUnguryan]

I redid from POST to GET and used the workaround that you suggested.

It certainly looks awful and clumsy. But it works! I’ll think about the beauty of the code later.

@GetMapping("/spells")
public DataTablesOutput<SpellDto> getData(@Valid DataTablesInput input, @RequestParam Map<String, String> searchPanes) {
    Setting setting = (Setting) session.getAttribute(SettingRestController.HOME_RULE);
    List<Integer> filterLevels = new ArrayList<Integer>();
    for (int j = 0; j <= 9; j++) {
        String level = searchPanes.get("searchPanes.level." + j);
        if (level != null) {
            filterLevels.add(Integer.valueOf(level));
        }
    }

Once again I want to thank you for your help and support.

[BarGrave]

Hi! Thanks for your great work. I simplified my life quite a lot :-)

I am quite new to java, datatables, ... so please excuse my dumb question.

I also try to use serverside searchPanes but I cannot figure out how to return the searchPane-JSON

"searchPanes":{`
    "options":{
        "users.first_name":[
            {
               "label":"Aaron",
               "total":"1",
               "value":"Aaron",
               "count":"1"
            },
            {
               "label":"Alex",
               "total":"1",
               "value":"Alex",
               "count":0
            },
            {
               "label":"Alexa",
               "total":"1",
               "value":"Alexa",
               "count":0
            },
            ...
        ]
    }
},

to dataTables. So far I am using your 'standard' GetMapping

public DataTablesOutput<Employee> list(@Valid DataTablesInput input, @RequestParam Map<String, String> params) {
  // ...
}

Any help will be greatly appreciated ... Thx :-)

[darrachequesne]

I think you'll have to extend the DataTablesOutput class, in order to add a searchPanes attribute (see the SearchPanes class above).

If that's a common use case, it might make sense to properly include it in the library though.

[BarGrave]

Thanks for your input. I will give it a try.

A properly inclusion in the library would be really great. I think SearchPanes are a quite cool feature.

For the future: They have another awesome tool: SearchBuilder. But SearchBuilder doesn't yet support serverside processing.

[darrachequesne]

Update: I have implemented basic support for the Search Panes extension in https://github.com/darrachequesne/spring-data-jpa-datatables/commit/16803f9d1e4f8c8c7b128a55b0be96d8cec36382 (included in 5.1.0)

Syntax:

@RequestMapping(value = "/data/users", method = RequestMethod.GET)
public DataTablesOutput<User> getUsers(@Valid DataTablesInput input, @RequestParam Map<String, String> queryParams) {
  input.parseSearchPanesFromQueryParams(queryParams, Arrays.asList("position", "status"));
  return userRepository.findAll(input);
}

(I wasn't able to parse the query param in the input directly...)

It works quite well, but there is an annoying issue with multi panes selection (the filter disappears):

I have updated the sample project here: https://github.com/darrachequesne/spring-data-jpa-datatables-sample/

Feedback is welcome!

[darrachequesne]

Update: it seems the issue above was fixed in newer versions of the SearchPane extension :rocket: