Column Filters Removed?

[SeanPollock]

Hi I tried to update the library today.

I used to use filters on some columns, eg.

columns.append(ColumnDT('colour', filter=colour))

Now I am not able to

columns.append(ColumnDT('shape', filter=shape_filter))
TypeError: __new__() got an unexpected keyword argument 'filter'

It seems the ColumnDT constructor had the filter option removed.

Is there an alternate way to do filters? I use them to format the data before sending it to the frontend.

Eg. if in the database "shape" was either 1, or 2- with 1 = Square, 2 = Circle (Just an example, I dont actually use it that way), the filter would translate that before sending it through.

The docs still reflect the old method, ie using filter=method.

[SeanPollock]

I see on the main README what the new format looks like. There is one example of an 'explicit cast to string' but it is using a database function. At this point maybe I don't understand enough about SQL alchemy and there is an easy way so hopefully you can help.

My situation:

• I store things in my database (eg. shape, colour, various other properties) using integers so that they are easy to compare an filter.
• These object need to be mapped (eg. using a dict) form an integer value to a string value (eg. {1: 'round'})
• I also display some columns on the frontend that are a combo of two columns. An equivalent example is if I had a db column for User.firstname and User.lastname. I only want one column on the frontend "name" which is a combination of the two.
• I used to use filter for the first case and an old feature you had, filterarg for the second case.

[tdamsma]

For the situation you are describing (only storing integers in the database and then mapping them via a dict to strings), there are better solutions. Common practice would be to use an extra table with a foreign key to do this mapping. This way you have a single source of truth (the database), instead of scattering dicts all over the application logic to assign meaning to a bunch of integers.

If you use that pattern, then you can present and search for the shape by name by simply joining a Shapes table with the Things table.

If you have no control over the database, an alternative would be to map the integers to descriptions via a switch/case query. Add that query as a column_property in your model, and then you can query for and search by shape name instead of some integer.

To join two columns, you can use the same technique, in pseudocode:

class User(Base):
    firstname = column(String)
    lastname = column(String)
    fullname = column_property(firstname + ' ' + lastname)

query = session.query()
columns = [ColumnsDT(User.fullname)]

Though not needed in your case, perhaps it would be nice to have a way to pass a custom filter function to the ColumnDT. I think currently you can monkey-patch it in something like this (untested):

def myFilterFunc(expr, value)
    pass

import search_methods from sqlalchemy_datatables

search_methods['myFilterFunc'] = myFilterFunc

columns = [ColumnDT(User.id, search_method='myFilterFunc')]
...

[ghukill]

I encountered this as well today after rebuilding a system that was is pulling most recent version of sqlalchemy-datatables from pypi. We used the filter paramter for handy little presentation-based functions such as:

def boolean(input):     
    if input == "1":
        return "<span style='color:green;'>True</span>"
    else:
        return "<span style='color:red;'>False</span>"

There certainly are workarounds, but it was a handy convention to have.

[tdamsma]

@ghukill, the way I would implement this would probably be in JavaScript. Using the columnDefs option, you can set a custom render function that takes (data, type, row) as arguments. It feels cleaner to me to do these little presentation things in the presentation layer. This should also keep the .json lighter.

If you do however want to add these things to the data, you can use SQLAlchemy to have your DB return exactly the data you want. Supporting the full power of SQLAlchemy was one of the reasons to propose the update which turned into 1.0.0.

Can you live with this, or do you feel it would be better to re-implement the filter function as a middle-ware between the actual query result and the json that is sent to the client?

[ghukill]

@tdamsma apologies for the delay in responding, just saw this now. I think you're absolutely right about anything that is presentation related. Using the columnDefs is a much better way to style results for the table.

I can imagine a world where it could be handy, server-side, to take results from the DB and transform them based on server-side information using the filter function. For instance, we might store a document identifier in the DB, then ping another database and get the human name of the document to display in the table. But you could (and perhaps, should) just store that information in SQL as opposed to deriving it on-the-fly for display. In our particular use case mentioned above, the DB is fairly flat, so we used the filter functions as shims to render data in a form we'd like client-side.

BUT, I think that all supports what you're saying about leveraging SQLAlchemy to the fullest, and pushing presentation logic client-side. For the time being, as our system has a foreseeable end date, we've just pinned our build to sqlalchemy-datatables 0.3.0.

Thanks for an incredibly handy library!

[louking]

Another technique is described in https://github.com/Pegase745/sqlalchemy-datatables/issues/119#issuecomment-510984437