Support searching and sorting on rendered field values containing joined data

[KovacicB]

When I try to search for some particular content in datatable, the search doesn't really work. To give you an example:

X = new orion.collection('x', {
...
tabular: {
  columns: [
    { data: "id", title: "ID" },
    { data: "creditor", title: "Creditor", render: function (val, type, doc) {
            var x = Creditors.findOne(val);
            if(!x)
                 return val + " not found";
            return x.name;
      }
    },
  ]
}

TABLE:
ID -> 1010
C --> Tom Jones

SEARCH RESULTS:
"10" -> nothing
"1010" -> returns actual row
"Tom" --> nothing
"Tom Jones" --> nothing

So where ever I have render property, search returns nothing but also where I don't have render, search only returns result if input text is complete value, not a substring of table column content. I am subscribed to Creditors so that's not the problem.

[aldeed]

Because tabular uses a server-side MongoDB search in order to support large datasets, it only works with text that's actually in the data field. For anything else you'll have to create your own filter lists.

In the future, possibly it could support custom server-side join-like queries.

[etcook]

@aldeed what are you referring to when you say "create your own filter lists?" Thanks!

[TomFreudenberg]

+1 for tips on "your own filter lists"

[KyleKing]

+1 as well for tips on "filter lists"

[aldeed]

By filter list, I mean you can put buttons, check boxes, drop downs, or your own search box outside of the table, and use those to reactively update the selector attribute on the table.

But I would ideally like to come up with a simple way of supporting searching on joined collections.

[Ajaxsoap]

+1 I would love this feature to be added soon, as I am desperate to have a simple filtering solution.

[KyleKing]

@Ajaxsoap I wrote a reusable hack based on this sample code which looks like this when done.

1. This function is the reusable part. TabularSelectorInit creates a reactive variable and attaches it to the window namespace for global access. TabularSelectorMain inserts input forms for the user, which it watches for a keyup event. On user input, the reactive variable is updated with the new string. TabularSelectorHelper is one example of how to update the selector. This example searches each column with a regex; however, this would be a good starting point to filter between two values (I'll have more time this week to try a solution). You might want to exchange $regex for a combination of $gte and/or $lte to filter between two input forms.

# If you're not a coffeescript person, this site is really useful: http://js2.coffee/
#
# Create the reactive variable with this structure:
# window.TabularSelector:
#                      [template name (i.e. table ID)]:
#                                    titles: ['name of each column for later reference']
#                                    other search criteria
# The [template name] allows for multiple tables in the same template and thus easy reusability
@TabularSelectorInit = (template) ->
  if isUndefined window.TabularSelector
    window.TabularSelector = new ReactiveVar({})
  sel = window.TabularSelector.get()
  sel[template] = {}
  sel[template].titles = []
  window.TabularSelector.set sel

# Create input boxes and attach event listener on keyup
# Collect any values into the above-mentioned reactive var
@TabularSelectorMain = (template) ->
  SelectedTable = '#' + template + ' thead th'
  $(SelectedTable).each ->
    title = $(SelectedTable).eq($(this).index()).text()
    # Collect list of titles to allow multi-column filter
    sel = window.TabularSelector.get()
    # console.log 'Overall sel - line 15'
    # console.log sel
    sel = sel[template]

    if isUndefined _.findWhere(sel, title)
      sel.titles.push(title)
    # Get specific data as set in Tabular Tables definition (i.e. class = 'profile.name')
    ThisClass = $(SelectedTable).eq($(this).index()).attr('class')
    # Remove excess sorting, sorting_asc class etc.
    ThisClass = ThisClass.replace(/(sortin)\w+/gi, '').trim()
    # console.log ThisClass
    unless isUndefined(ThisClass) or ThisClass is ''
      # Create input text input
      # $input = $('<br><input type="text" placeholder="Search ' + title + '"' + 'class="' + ThisClass + '"/>')
      $input = $('<input type="text" placeholder="Search ' + title + '"' + 'class="' + ThisClass + '"/>')
      # $(this).append $input
      $(this).html $input
      # Prevent sorting on click of input box
      $input.on 'click', (e) ->
        e.stopPropagation()
      # Capture events on typing
      $input.on 'keyup', (e) ->
        console.log 'searching: ' + title + ' and ThisClass: ' + ThisClass
        sel = window.TabularSelector.get()
        # rename sel to template object
        sel = sel[template]
        sel[title] = {}
        sel[title].search = ThisClass
        if @value
          # sel['profile.name'] =
          sel[title].value =
            $regex: @value
            $options: 'i'
        else
          delete sel[title]
          # delete sel['profile.name']
        # need new variable to encompass to template-level object
        overall = window.TabularSelector.get()
        overall[template] = sel
        # console.log 'OVERALL:'
        # console.log overall
        window.TabularSelector.set overall

# I had some issues with reactivity and found this to be the best way
# This is one example of restructuring the reactive variable into a selector that MongoDB understands
@TabularSelectorHelper = (template) ->
  # console.log 'Current Selector'
  sel = window.TabularSelector.get()
  sel = sel[template]
  # console.log 'Template sel - line 62'
  # console.log sel

  ReactiveTest = {}
  _.each sel.titles, (title) ->
    unless isUndefined sel[title]
      ReactiveTest[sel[title].search] = sel[title].value
  ReactiveTest

1. Update the Tabular Tables configuration with classes

TabularTables.ManageUsers = new (Tabular.Table)(
  name: 'ManageUsers'
  collection: Meteor.users
  autoWidth: false
  columns: [
    { data: 'profile.name', title: 'Name', class: "profile.name" }
    { data: 'emails.0.address', title: 'Email', class: "emails.0.address" }
    { data: 'emails.0.verified', title: 'Verified?'} # note: no "class:"
    { data: 'roles', title: 'Roles', class: 'roles'  }
  ])

1. Updated the TabularTables reference with an ID and a reactive Selector:

{{> tabular table=TabularTables.ManageUsers class="table table-striped table-hover table-bordered table-condensed" selector=currentSelector id="ManageUsers"}}

1. Use the ID set in step 3 and call each of the three functions. For each successive table, even in the same template you only need to repeat steps 2-4

Template.ManageUsers.created = ->
  TabularSelectorInit('ManageUsers')

Template.ManageUsers.rendered = ->
  TabularSelectorMain('ManageUsers')

Template.ManageUsers.helpers
  currentSelector: ->
    TabularSelectorHelper('ManageUsers')

So far this only works with the basic regexp example, but I am working on a few things.

1. I would like it to work with the bootstrap slider library for a demo like example 2, where a user could input a range and limit the table (like this issue).
2. I also want to make this work for radio buttons to help search true/false values
3. Find a way to support client-side searches? Probably it would limit what the template helper would combine (i.e. if you use meteor-aggregate to combine first and last name, the regexp would limit what names were combined in the helper function somehow)
4. Possibly submit this as a PR, if all else seems promising.

What do you think?

[Ajaxsoap]

Hi @KyleKing ,

Thank you very much for huge effort! Forgive me for my delayed response as I am pre-occupied finding solutions to my other challenges.

Your solution looks promising, I will try your solution and will let you know if it works!

[thebarty]

Hi guys,

+1. :+1: plus one for this one - (right now) especially for the sorting support.

I just ran into a similar use-case where I actually need SORTING for a data='helperFunction()'.

    {
      title: 'myTitle',
      // BUG: ``data: 'helperFunction'`` works for displaying only
      //  BUT NOT for sorting (sorting is inactive) NOR for searching
      //  ... so will I end up using a direct schema.field instead
      data: 'customHelper()',
      tmpl: Meteor.isClient && Template.myTemplateCell,
      tmplContext: function (rowData) {
        return {
          doc: rowData,
        };
      }
    },

[brylie]

What is the status of this feature request? Has a PR been made based on @KyleKing's suggestion?

[KyleKing]

Hey @brylie, thanks for the interest! I haven't had time to keep working on this demo, but I would like to revisit it. I saw this as more of an example code and not necessarily a PR, but it might be useful as a separate package? I should probably put together a demo application that could have its own repository or be part of an /examples folder here {@aldeed, would you be interested in a set of DataTables examples?}. What do you think is the best way to make sorting easier?

Also, @Ajaxsoap, did the sample code help with your project?

[brylie]

@KyleKing good idea for the examples folder. It might, however, eventually be useful for Tabular to support sorting and searching in collection helpers. E.g. we are using aslagle:reactive-table in another project, since it has this crucial functionality.

[KyleKing]

@brylie I have a working example of my above code at https://redbarbikes.com/admincompilation (login info U: admin@example.com P: 'password'). You can type your query (numbers don't work yet) or click the column to sort, which works without collection helpers. Do you think helpers would add a distinct advantage? What other features do you need?

[brylie]

We are using collection helpers to traverse collections in order to create relationships between collections. E.g. Book.author() looks in the Authors collection for an ID stored in the Book.authorId field. In the previous example, a Bookshelf table might contain the book title, author, ratings, etc., and many of the attributes could be stored, or derived from data, in separate collections. To the end-user, the data structure will not matter. They will see a table with inconsistent column behaviour.

[KyleKing]

@brylie you're totally right. There might be a way to toggle from the meteor-tabular default behaviour to one that searches only the content within the table, but you would still have to change much of the backend of the package. I'll have some time this weekend and can try to put together a PR. Do you have some sample code? Like what you db structure is and how you want it displayed with meteor-tabular (or in your existing project with reactive-tables)?

[brylie]

I have a PR open in my work project where we are showing user ratings in a table. The ratings come from a collection helper. I have currently disabled sorting on the Rating column by removing the data attribute from the column configuration object.

[pmoulos]

:+1: for urgent feature implementation (sorting based on collection helper, at least from within the same collection, i.e. no composite publications). My application is quite addicted to tabular! :)

[Custos]

:+1: Ditto to @pmoulos

[lukastheiler]

Sorting does not work either btw: It sorts by the Creditors's ID instead of rendered value.

[KyleKing]

I made some progress over break and am planning on putting together a PR, but I haven't finalized the configuration and there is still code to finish.

The version with my edits + per column sorting: http://tabularwith.meteor.com/ Without any edits to the Tabular package: http://tabularwithout.meteor.com/

I'll update this issue when I finish my updates (hopefully by Monday). Let me know what you think! The sample configuration file is here: https://github.com/KyleKing/meteor-tabular/blob/feature/mutli_field/examples/collection_helpers/both/tabular_init.coffee

Unfortunately sort will never really work for a collection helper. It would need to access client-side collections to work.

[macrozone]

I general, you can't automatically search and sort by computed fields. If you would want to, you would have to define the "reverse-function" of the render (or the helper in case of collection-helpers) that returns a mongo-selector for a given input string (in case of search).

I think it is best to persist computed fields in the collection if you want to search or sort by it. You can use collection2's autoValue or collection-hooks for that.

[maka-io]

+1

[VansonLeung]

+1, I am looking to the reactive-aggregate plugin & trying to make a new Datatables plugin for that

[Pushplaybang]

a simpler hack, seeing as we're in mongo land anyway, can be to denormalise your data into a field on the collection you wish to sort on, while this won't suit most of our needs for a clean implementation, it'll get the job done.

[cjbrunnen]

+1