pegase745/sqlalchemy-datatables

SQLAlchemy integration of jQuery DataTables >= 1.10.x (Pyramid and Flask examples)

sqlalchemy-datatables

sqlalchemy-datatables is a framework agnostic library providing an SQLAlchemy integration of jQuery DataTables >= 1.10, and helping you manage server side requests in your application.

Installation

To install via pip:

Python 3

$ pip3 install sqlalchemy-datatables

To install from source:

$ git clone [email protected]:Pegase745/sqlalchemy-datatables.git
$ cd sqlalchemy-datatables
$ pip3 install .

To contribute:

In a virtualenv
$ git clone [email protected]:Pegase745/sqlalchemy-datatables.git
$ cd sqlalchemy-datatables
$ make all

Usage

@view_config(route_name='data', renderer='json')
def data(request):
    """Return server side data."""
    # defining columns
    #  - explicitly cast date to string, so string searching the date
    #    will search a date formatted equal to how it is presented
    #    in the table
    columns = [
        ColumnDT(User.id),
        ColumnDT(User.name),
        ColumnDT(Address.description),
        ColumnDT(func.strftime('%d-%m-%Y', User.birthday)),
        ColumnDT(User.age)
    ]

    # defining the initial query depending on your purpose
    #  - don't include any columns
    #  - if you need a join, also include a 'select_from'
    query = DBSession.query().\
        select_from(User).\
        join(Address).\
        filter(Address.id > 4)

    # instantiating a DataTable for the query and table needed
    rowTable = DataTables(request.GET, query, columns)

    # returns what is needed by DataTable
    return rowTable.output_result()

Examples

You can find working examples in the repository, including an integration with the yadcf plugin:

• Pyramid example
• Flask example

Changelog

All notable changes to this project will be documented in this section.

This project adheres to Semantic Versioning and Keep A Changelog.

License

The project is licensed under the MIT license.

sqlalchemy-datatables's Issues

Sort expressions and user table name

Hi!

Great library! I have an issue with sorts because I have a table called "user" (in retrospect maybe it could of been called "user_account" given "user"'s reserved word status).

When the query is generated the user in the sort expression is not surrounded by quotes, for example:

ORDER BY user.email 

Which fails and I get an error, where it really should be

ORDER BY "user".email 

This seems like something easily resolved - any tips? I'm not familiar with the sqlalchemy api.

Cheers,
Sean

Filters run twice?

def format_status(status):
    return  '<span>{status}</span>'.format(status=status)

Using this filter gives this:

<span><span>status</span></span>

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis

Thanks for your package. I'm using it with flask and I'm getting the message in the title with this setup / configuration.

This is my flask view function:

from sqlalchemy.orm import mapper

@theapp.route('/preview')
def preview():

    columns = []
    columns.append(ColumnDT('part_no'))
    columns.append(ColumnDT('part_nm'))
    columns.append(ColumnDT('material_spec'))
    columns.append(ColumnDT('thickness'))

    m = db.MetaData()
    ff = db.Table('fabs_features', m, autoload = True, autoload_with = db.engine)

    class Fabsfeatures(object):
        def __init__(self, part_no, part_nm, material_spec, thickness):
            self.part_no = part_no
            self.part_nm = part_nm
            self.material_spec = material_spec
            self.thickness = thickness

    mapper(Fabsfeatures, ff)

    qry = db.session.query(Fabsfeatures)

    rowTable = DataTables(request.args, Fabsfeatures, qry, columns)
    return jsonify(rowTable.output_result())

index.html

{% extends "base.html" %}

{% block styles %}
<link rel="stylesheet" href="{{url_for('.static', filename='jquery.dataTables.min.css')}}">
{{super()}}
{% endblock %}

{% block scripts %}
  {{super()}}
  <script src="{{url_for('.static', filename='jquery.dataTables.min.js')}}"> </script>
  <script type="text/javascript">
        $(document).ready(function() {
          $('#previewTable').DataTable( {
          "processing": true,
          "serverSide": true,
          "ajax": "{{ url_for('preview') }}"
          });
      });
  </script>
{% endblock %}

{% block content %}
<div class="container">
    <h3>Index Page</h3>
    <table id="previewTable" class="display" cellspacing="0" width="50%">

      <thead>
                <tr>
                    <th>part_no</th>
                    <th>part_nm</th>
                    <th>material_spec</th>
                    <th>thickness</th>
                </tr>
      </thead>
      <tbody>
      </tbody>

    </table>

</div>
{% endblock %}

With this setup, the ajax request fails with a 500 response. In the response body, the stack trace is:

Traceback (most recent call last):
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\okyernb\pythonprojects\datatables\app\views.py", line 51, in preview
    rowTable = DataTables(request.args, Fabsfeatures, qry, columns)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 101, in __init__
    self.run()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 152, in run
    self.filtering()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 265, in filtering
    self.cardinality_filtered = self.query.count()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2790, in count
    return self.from_self(col).scalar()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2559, in scalar
    ret = self.one()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2528, in one
    ret = list(self)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2571, in __iter__
    return self._execute_and_instances(context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2586, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
    context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\util\compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\util\compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context
    context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis
 [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT fabs_features.id AS fabs_features_id, fabs_features.part_no AS fabs_features_part_no, fabs_features.part_nm AS fabs_features_part_nm, fabs_features.material_spec AS fabs_features_material_s_1, fabs_features.thickness AS fabs_features_thickness \nFROM fabs_features \nWHERE lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.material_spec AS VARCHAR2)) LIKE lower(:param_3) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.material_spec AS VARCHAR2)) LIKE lower(:param_3) AND lower(CAST(fabs_features.thickness AS VARCHAR2)) LIKE lower(:param_4)) anon_1'] [parameters: {'param_3': '%%', 'param_1': '%%', 'param_2': '%%', 'param_4': '%%'}]

I've tried to mimic the flask example in the documentation but still this is what I get. Am I doing something wrong or this is a bug in the library? Thank you.

Individual column search option

Could you explainn the search_like option for columns? As writed in datatables.py:47 the type of this argument is string, but in code (datatables.py:213) it is only checked for boolean.
Is the argument 'search_like' really boolean type? Or may code haven't yet completed with that feature?

Travis tests sometimes failing for no apparent reason

Perhaps because I added an extra test, there seems to be a race condition for the sqlite3 database where some of the tests fail due to columns existing from a previous test. Not entirely sure how to solve this, perhaps move all tests to one script. @Pegase745, if you manually ask Travis to rebuild, it will probably be ok. Perhaps this is an issue with the coveralls test runner, you could try to switch to py.test with --cov flag, perhaps that would solve it.

Flask example view function error

For some reason, flask_tut has view function error:

AssertionError: View function mapping is overwriting an existing endpoint function: home

This error typically happens when views have the same name but none of the views share the same name. In fact, I noticed that the server won't start with any view added in __init__.py.

I tried changing changing the names of view but the server only runs when all the views are commented out.

Any tips?

When iDisplayLength or length equals -1 paging() method thows error

datatables set length/iDisplayLength to -1 if it wants to load all data at once.
the sqlalchemy-datatables throws:

TypeError: unsupported operand type(s) for +: 'property' and 'property'

instead of loading all data from the query

yadcf use case: range of dates / numbers not handled

yadcf provides columns[n][search][value] containing start-yadcf_delim-end for start, end range filtering. I need to fix this and will supply pull request tailored for yadcf, but not sure if there are other filtering add-ons out there which could also be supported.

Configure SQL ORDER BY null value behavior

Hi, I'm not sure if this is the right place, but after some hours of digging in the DT and SQLA docs, I think it is.

I want to configure the behavior when ordering a column that contains NULL values. For example, when ordering a year column, I want them to be displayed at the end. With DT's server side processing, this is not possible to configure in the JS source after what I read in the docs.

As per SQLA this should be done with calls to nullsfirst() and nullslast() around the asc() and desc() methods (see SQLElement).

In init.py#L332 the sort calls are made, so I think the null options should be included there. What do you think?

Backreferences in defining colums

First, thank you so much for your work. It is great!
I'm trying to manage a table having m:m relationship mapped with a backref. Something similar to the Parent:Child scheme as described in the sqlalchemy tutorial (http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many).
By defining the columns for the Parent class, for instance, if I use the backreference Parent.children this message appears: " DataTables warning: table id=maindata - Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Parent.children has an attribute 'cast' ".
In sum, this code does not work as I expect (but I'm a newbie):

columns = [
...
ColumnDT(Parent.id, search_method='yadcf_range_number_slider'),
ColumnDT(Parent.children, search_method='yadcf_text'),
...
]

Is there any workaround to solve such a case?

multiple level support

model:

roles_users = db.Table('roles_users',
                       db.Column('user_id', db.Integer(), db.ForeignKey('users.id')),
                       db.Column('role_id', db.Integer(), db.ForeignKey('roles.id')))
#Base = declarative_base()


# Role model for Flask-Security
class Roles(db.Model, RoleMixin):
    #__tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(255))


# User model for Flask-Security
class Users(db.Model, UserMixin):
    #__tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    username = db.Column(db.String(100), unique=True)
    confirmed_at = db.Column(db.DateTime)
    last_login_at = db.Column(db.DateTime)
    current_login_at = db.Column(db.DateTime)
    last_login_ip = db.Column(db.String(50))
    current_login_ip = db.Column(db.String(50))
    login_count = db.Column(db.Integer)

    roles = db.relationship('Roles', secondary=roles_users, backref=db.backref('users', lazy='dynamic'))
    orders = db.relationship('Orders', backref='users')


class Orders(db.Model):
    #__tablename__ = 'order'

    order_id = db.Column(db.Integer, primary_key=True)
    #user_name = db.Column(db.String(30), nullable=False)
    order_date = db.Column(db.DateTime, nullable=False, default=datetime.now())
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship(Users, backref='user_order')
    order_items = db.relationship("OrderItem", cascade="all, delete-orphan", backref='orders')
    items = association_proxy("order_items", "item")


class Item(db.Model):
    #__tablename__ = 'item'
    item_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String(30), nullable=False)
    price = db.Column(db.Float, nullable=False)

    def __init__(self, description, price):
        self.description = description
        self.price = price

    def __repr__(self):
        return 'Item(%r, %r)' % (
            self.description, self.price
        )


class OrderItem(db.Model):
    #__tablename__ = 'orderitem'
    order_id = db.Column(db.Integer, db.ForeignKey('orders.order_id'), primary_key=True)
    item_id = db.Column(db.Integer, db.ForeignKey('item.item_id'), primary_key=True)
    price = db.Column(db.Float, nullable=False)

    def __init__(self, item, price=None):
        self.item = item
        self.price = price or item.price

    item = db.relationship(Item, lazy='joined')

In [10]: a.order_items
Out[10]:
[<test.OrderItem at 0x7fe7be15c150>,
<test.OrderItem at 0x7fe7be185690>,
<test.OrderItem at 0x7fe7be185890>]

a.order_items[0].item.price

'Legace datatables not supported, upgrade to >=1.10' using the version 10.10.15

I get a 'legacy datatables' error, but I'm using the version 10.10.15.

The library checks if the params include the 'sEcho' argument. The problem is that the argument seems to be present in the construction of the URL for the version 10.10.15

URL:
sEcho=1&iColumns=8&sColumns=%2C%2C%2C%2C%2C%2C%2C&iDisplayStart=0&iDisplayLength=10&mDataProp_0=0&sSearch_0=&bRegex_0=false&bSearchable_0=false&bSortable_0=true&mDataProp_1=1&sSearch_1=&bRegex_1=false&bSearchable_1=true&bSortable_1=

The code seems to be this:

  if 'sEcho' in self.params:
            raise ValueError(
                'Legace datatables not supported, upgrade to >=1.10')

Version:

<script type="text/javascript"
        src="https://cdn.datatables.net/v/bs-3.3.7/jq-2.2.4/dt-1.10.15/b-1.3.1/b-colvis-1.3.1/r-2.1.1/se-1.2.2/datatables.min.js"></script>

postgresql schema name in sort_name

Hi,
I use the code in flask sqlalchemy postgresql, and my tables are in schemas

class User(db.Model, UserMixin):
    __tablename__ = 'user'
    __table_args__ = {'schema': 'usr'}

when i want to order by a column by clicking i will recive a postgresql error.
in the postgresql select the order_by the sort_name has not schema name in front

How can I use "count" for another relevant table?

I have the following schema for my ORM:

Table Member

class CourseMember(Base):
    __tablename__ = 'member'

    member_id = Column(BigInteger, primary_key=True)
    team_id = Column(ForeignKey(team.team_id', ondelete=u'CASCADE', onupdate=u'CASCADE'), nullable=False)
    # more attributes....
    team = relationship('Team')

Table Team

class Team(Base):
    __tablename__ = 'team'

    team_id = Column(BigInteger, primary_key=True)
    name = Column(String(64), nullable=False)
    # more attributes

I am wondering how to build a dataTable using datatable-sqlachemy to get the flowing items:

(team_id, team_name, team_member_count)

I do not know how to count the members with only one class int session.query(Team).cascaded_operations()

Is there a possible way for this usage? Thank you very much.

Not working for flask

I tried with existing flask tables (postgres database) but got error below. Then I copied over the user and addresses models in flask_tut examples & populated database with dummy records but still same error.

Error received:
"Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Address.id has an attribute 'cast'"}

The issue seems to be with the exact data returned as the other parameters in results appear fine:
result: {'recordsTotal': '13', 'recordsFiltered': '0', 'draw': '1', 'error': "Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Address.id has an attribute 'cast'"}

This error is typically an sqlalchemy error that typically happens with tables that have relationships with other tables. However, I still get this error with a simple db.model that has only two attributes and no relationships with other tables.

I looked through the datatables src and I was wondering if this project is only supposed to work with sqlite database?

Any tips would be appreciated.

How to deal with aggregated columns like sum and count?

I have a table Timepass which has two columns user_name and Income.

username --- income
saif---1000
saif---2000
zaib--1000
saif---1000
zaib---500

I want datatable output as
username -- sum -- count
saif----4000 -- 3
zaib----1500 -- 2

My server side code is....

columns = [
ColumnDT(Timepass.user_name),
ColumnDT(literal_column("'sum'")),
ColumnDT(literal_column("'count'"))
]
results = db.session.query(Timepass.user_name,func.sum(Timepass.income),func.count(Timepass.user_name)).group_by(Timepass.user_name)
params = request.args.to_dict()
rowTable = DataTables(params, results, columns)
return jsonify(rowTable.output_result())

I get an error when I click the sum or count column on frontend datatable.

$(document).ready(function(){
var table = $('#example').DataTable({
"processing": true,
"serverSide": true,
"ajax": "{{ url_for('home.data') }}",
"columns":[
{ data: "0" },
{ data: "1" },
{ data: "2" }
]
});

Name	Sum	count

Can you please help?

Unable to sort by hybrid properties

I have a basic setup

    columns = []
    columns.append(ColumnDT('id'))
    columns.append(ColumnDT('name'))
    columns.append(ColumnDT('address'))
    columns.append(ColumnDT('child.date'))
    columns.append(ColumnDT('created_at'))

When trying to sort on name or address which is a hybrid property, I get

OperationalError: (OperationalError) (1054, "Unknown column 'obj.address' in 'order clause'")

How the hybrid property is defined

    @hybrid_property
    def name(self):
        return utils.join([self.first_name, self.last_name])

    @name.expression
    def name(cls):
        return cls.first_name + ' ' + ls.last_name

clean_regex fails with int and bool values

Hi,
The clean_regex function added in the last commit expects only a str. But searchValue can be either a bool, a int or a str. A naive fix would be

if isinstance(searchValue, str):
  regex = clean_regex(searchValue)
else:
  regex = searchValue

Optimized Datatable

Hi,

Thank you so much. Everything is working fine.

I had one question. If I had 10000 rows. Is it fetching and loading all rows at one time or it is optimized?

Thank you.

Best Regards,
Saif

Use of group_by in query doesn't work with yadcf

In the function _set_yadcf_data min/max values are determined for the 'yadcf_range_number_slider' columns. Selecting the min/max of a query with a group_by clause returns more than one row.

• if using Group_by is actually a good idea then this should be supported
• otherwise it is better to disallow use of group_by in the query altogether

Any opinions on this?

The filtering method is wrong

Might the datatables.py:209 has the wrong test for the search request parameter? In current version no data is displayed, filtered by column search with empty string.
The request has always contained the 'sSearch_i' parameters with empty string. The check now test it in according to 'None' but the result is always 'not None'.
The code introduced by @tdamsma in the commit 3a99611.

I suggest to use the implicit check: "if search_value2:"

use regex for data retrieval?

as of sqlalchemy-datatables 0.2.1 queries from multi-select field type are not supported (e.g., from yadcf, see http://stackoverflow.com/questions/32855901/yadcf-with-select2-puts-empty-selected-option)

the actual "non-support" is for columns where regex is True, coming from dataTables client

regex is supported by sqlalchemy for some dialects, e.g., see http://stackoverflow.com/questions/27228635/how-can-a-sqlalchemy-query-use-mysqls-regexp-operator

not sure if it would be sufficient to support | (OR) operator through hard coding, which will allow multi-selects

for my current need, hard coded support of OR operator, or mysql support of regex would be sufficient, but that is not a complete solution

Flask not working

I tried to use this in a flask application but am getting a server 500 error. I noticed in the README you say 'it is currently not working with flask'. Do you have a hunch on what the problem is? Maybe there is a way I can help? I would love to get this working.

is it possible to return cell data based on other data in the row?

I have a join among several tables. I want to return a calculated value which is based on different tables, but the following raises exception

ColumnDT('race.date',filter=lambda c: timeu.age(c,runner.dob)),

In this case I want to calculate a runner's age on race date, but runner is not defined, getting

NameError: free variable 'runner' referenced before assignment in enclosing scope

Is there some technique I can use to achieve my goal?

how to sorting by id

how to sorting by id
id is int

Datatables crashes when has no "draw" param

If i do not pass "draw" param in get request in flask example i get a 500 error.
I can make a PR to fix it something like this:

    def output_result(self):
        """Output results in the format needed by DataTables."""
        output = {}
        try:
            output['draw'] = str(int(self.params['draw']))
        except Exception as exc:
            self.error = str(exc)

        output['recordsTotal'] = str(self.cardinality)
        output['recordsFiltered'] = str(self.cardinality_filtered)
        if self.error:
            output['error'] = self.error
            return output

        output['data'] = self.results
        for k, v in self.yadcf_params:
            output[k] = v
        return output

https://github.com/Pegase745/sqlalchemy-datatables/blob/master/datatables/__init__.py#L241

Should i make it?

Can't search with cyrillic character ( Unicode )

I'm try to search unicode character in DataTables and no result founded.
Bytheway, I rewrite source query with the same ( "like" ) filter and filter is working.

Get a child element as table column

Model:

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    child = db.relationship('Key', backref='parent')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))
	price = db.Column(db.Integer)

columns = [
	ColumnDT(Parent.id, mData='1'),
	ColumnDT(func.count(Parent.child), mData='2', global_search=False), # it's works
	ColumnDT(Parent.child[0].id, mData='3'), # get id 1st child, does not work
	ColumnDT(Parent.child[1].id, mData='4'), # get id 2nd child, does not work

]

query = db.session.query().select_from(Parent).join(Child).group_by(Parent.id)

Example:
Parent_1.id - Parent_1.countChild - Parent_1.Child_1.id - Parent_1.Child_2.id
Parent_2.id - Parent_2.countChild - Parent_2.Child_1.id - Parent_2.Child_2.id
Parent_3.id - Parent_3.countChild - Parent_3.Child_1.id - Parent_3.Child_2.id

Data show only first record

Hello!

This package is very useful, but now I have one problem with data showing it's show only one first record.

Here is my script:

$("#data_list").dataTable({
                "serverSide": true,
                "processing": true,
                "ajax": "{{ url_for('getAppData') }}?start=1&length=1&draw=1&model=Table&fields=field_list"
            });

Any solution here?
Thanks in advance...

Querying Many-To-Many relationships

Hello!

Could you please help me with many-to-many related tables?

Relation set in models:

class Asset(db.Model):
...
users = db.relationship('User', secondary=ext_vs_users, backref='extensions', lazy='dynamic')

Query given to datatable:

data_table = DataTables(request.args, Asset, query, columns)

Columns set:

columns.append(ColumnDT('number'))
columns.append(ColumnDT('location'))
columns.append(ColumnDT('display_name'))
columns.append(ColumnDT('users.uid'))

There can be both single and multiple users per asset. But for every row I get "None" in users.uid column. Is it possible to somehow retreive data in this case?

P.S. Thanks for a great package! :)

Table name ans search functionality errors?

Hi,

I tried using your code in a flask sqlalchemy/postgresql setup in python 3.4 and in order to function I had change starting with row287:

           sort_name = '%s.%s' % (tablename, sort_name)

replaced with

           sort_name = '"%s".%s' % (tablename, sort_name)

as I was getting postgresql syntax errors regarding the sorting table name.

Also, in order to have a functional search function I changed:
from row225:

                if condition is not None:
                    condition = and_(condition, and_(*conditions))
                else:
                    condition = and_(*conditions)

with

                if condition is not None:
                    condition = or_(condition, or_(*conditions))
                else:
                    condition = or_(*conditions)

Do you think there was another problem on my side or should I make a pull request?
Thanks for your nice repo.

regex use case: with yadcf exact match select filters regex is garbled

When using https://github.com/vedmack/yadcf select filter filter_match_mode = 'exact', clean_regex is escaping the beginning and ending anchors, as well as outside parenthesis within those anchors. This causes the filter not to work.

Pull request is pending, want to soak test some before release. Still have no way to write unit tests for these because of mysql requirement.

Reimplement api to rely on SQLALchemy core functions

I am a big fan of sqlalchemy-datatables, but I often run into problems with how sqlalchemy-datatables tries to reimplement some of SQLAlchemy for dealing with joins etc. Complicated queries, column_properties, of ad-hoc func() statements applied to columns can easily break sqlalchemy-datatables.

I forked this repo and came up with a way to deal with this here. The main idea is that instead of passing an sqla_object, query, and columns, only pass the query and the columns. And instead of passing the columns by name, one can pass any SQLAlchemy column like expression, like func.upper(User.name).

An example:

Without datatables, a query can look like this:

query = self.session.query(
        User.id,
        User.name,
        User.birthday,
        func.date_part('year', func.age(User.birthday)),
    ).filter(User.id > 10)

result = query.all()

Currently, with sqlalchemy datatables there is no way to calculate the age on the fly using sql statements. In my implementation, you have to split the query and the columns and pass those to DataTables:

query = self.session.query().filter(User.id > 10)
columns = [
        ColumnDT(User.id),
        ColumnDT(User.name),
        ColumnDT(User.birthday),
        ColumnDT(func.date_part('year', func.age(User.birthday))),
        ]
rowTable = DataTables(request, query, columns)        

If you check the code, you can see it is much cleaner, as dealing with joins, properties etc are made by sqlachemy. The result of the query is determined operating on the sqlalchmey expressions.

So, I am quite happy with this change, but as it is an api change, I was wondering if you are interested in merging these changes into this repo. If so, I can prepare a PR.

There a re a few rough edges with my current implementation though:

• I changed the search, as I have no need for regex searches. I do however need things like >12 to search for numbers greater than 12 (this is implemented)
• I didn't implement the 'search all columns' option

I would love to hear your thoughts

Not compatible with the new version DataTables

Specification of the API has changed in the new version of Datatable(1.10+)

https://datatables.net/manual/server-side

columns using object methods

Hi, nice library, thank you! From the examples, I see that the columns can be defined either using a reference to the model attribute or a function; is it possible to define the column as a reference to an object method? IE:

columns = [ ColumnDT(User.name), #model attribute
                    func.strftime('%d-%m-%Y', User.birthday), #function 
                    user.get_edit_url() ] #method call on instance of user class

In this case, the third column is a url to the user's edit functionality, constructed on the fly.

Cheers

Filter based on another columns value?

Hello.

Thanks for the lib! It's very helpful.

Is there a way that I can access another columns value in the filter function? Eg.

columns.append(ColumnDT('cert_type'))
columns.append(ColumnDT('cert_num'), filter=some_filter))

So that some_filter, I can change the display depending on cert_type?

def some_filter(cert_num):
     if cert_type == "Authority1":
             return "(Authority 1) " + cert_num
     elif cert_type == "Authority 2":
            return "(Authority 2) " + cert_num

How to append custom column?

Hello

This plugin is very useful! I'm using it with flask and sqlalchemy, but I've a question; how can I put a custom text (in this case, a link) as a column?

For example, I've defined

columns = [
    ColumnDT(Location.id),
    ColumnDT(Location.name),
    ColumnDT(Location.location),
    ColumnDT(Location.phone),
    ColumnDT('CUSTOM URL')
]

"Custom URL" is any url generated by flask (edit, more info of current row, etc)

I know that I can personalize the rows using javascript, but certain urls are better if rendered by flask directly.

Any thought or ideas?

Thanks a lot!

INVALID JSON using sqlalchemy Datatable

I am using the same template and example which exists in sqlalchemy Datatable sample in: http://sqlalchemy-datatables.readthedocs.org/en/latest/ my code is the following, my problem is invalid json, that I could not find any problems in it. any helps? The output of rowTable.output_result() is:

{'aaData': [{'1': 'DOC - 1457715381', '0': '60352794'}, {'1': 'DOC - 1457715381', '0': '2768077336'}, {'1': 'DOC - 1457715381', '0': '6247239243'}, {'1': 'DOC - 1457715381', '0': '8257884017'}, {'1': 'DOC - 1457715381', '0': '8508822379'}], 'iTotalRecords': '5', 'sEcho': '1', 'iTotalDisplayRecords': '5'}

ERROR: DataTables warning: table id=mytable - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

in HTML Script tag:
$(document).ready(function(){ $('#mytable').dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": "${request.route_path('doclist')}" });

MAKO:
@view_config(route_name='doclist', request_method='GET', renderer='service:/templates/partials/doclist.mako', permission = 'login') def viewListDocuments(self): r = self.r creator_id = 18 columns = [] columns.append(ColumnDT('doccode')) columns.append(ColumnDT('doctitle')) query = DBSession.query(Document).filter(Document.creator_id == creator_id) rowTable = DataTables(r.GET, Document, query, columns) return rowTable.output_result()

Column Filters Removed?

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.

Flask-Alchemy

Heya,

I've been trying to use your plugin with flask-alchemy with the following code (following the example):

request.GET = request.args.get
# defining columns
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('username', filter=_upper))
columns.append(ColumnDT('userid')) # where address is an SQLAlchemy Relation
columns.append(ColumnDT('password', filter=str))

query = User.query.all()
rowTable = DataTables(request, User, query, columns)

It keeps erroring with
TypeError: count() takes exactly one argument (0 given)

from the line:
self.cardinality = self.query.count()

Any idea of the changes I need to make to get the plugin to work with flask-alchemy?

Many thanks!

pip install doesn't work for version 0.1.7

I try to install
pip install --allow-all-external --pre sqlalchemy-datatables==0.1.7
and I receive Could not find a version that satisfies the requirement sqlalchemy-datatables==0.1.7 (from versions: 0.1.0, 0.1.1, 0.1.2, 0.1.3, 0.1.4, 0.1.5, 0.1.6) error

@inklesspen at the pyramid irc conference adviced me just do pip install https://github.com/Pegase745/sqlalchemy-datatables/archive/v0.1.7.zip but this is a temporary solution which complicates deployment of projects depending on your module.

Custom sort option

First of all let me say that this is not an issue, but just a question.
My case: I need to order a table throught four different db columns in order to obtain a 'natural sort', but of course if I add an order by clause to the definition of the initial query this propagates to any further attempt of sorting.
i.e.:
query = db.session.query().select_from(MyTable).\ order_by(MyTable.col1, MyTable.col2, MyTable.col3, MyTable.col4)
At a first glance it works, but at the same time it prevent any other attempt of dynamic sorting of the table.
So what I'm asking for is:
How to define a custom sort option for one of the columns? Is it possible to attache a keyword argument to the column definition in order to obtain my need?
As far as I can understand from the source code I cannot figure it out if it is possible... sorry I'm not a true programmer!

Many thanks in advance

what is best way to configure sqlalchemy-datatables for columns which are not in database?

Using 0.4.0 sqlalchemy-datatables, from client I am using Editor, and datatables sends the "select" column with a blank name. I suspect this is causing an IndexError. What is best way to configure ColumnDT for the case when column is not in database, but generated at client?

Current configuration

        columns = [
            ColumnDT('',                    mData='',                   searchable=False),
            ColumnDT('place',               mData='place'), 
            ColumnDT('name',                mData='resultname'),
            ColumnDT('gender',              mData='gender',             searchable=False),
            ColumnDT('age',                 mData='age',                searchable=False,   filterarg='row', filter=renderage),
            ColumnDT('initialdisposition',  mData='membertype'),
            ColumnDT('confirmed',           mData='confirm',          searchable=False),
            ColumnDT('runnerid',            mData='runnerid'),
            ColumnDT('hometown',            mData='hometown'),
            ColumnDT('club',                mData='club'),
            ColumnDT('time',                mData='time',               searchable=False,   filter=lambda c: render.rendertime(c, 0)),  # TODO: get precision
        ]

I am seeing request from datatables client (unquoted and '&' split for convenience and readability, note column [0])

['/_editparticipants/171?draw=2',
 'columns[0][data]=',
 'columns[0][name]=',
 'columns[0][searchable]=true',
 'columns[0][orderable]=false',
 'columns[0][search][value]=',
 'columns[0][search][regex]=false',
 'columns[1][data]=place',
 'columns[1][name]=place',
 'columns[1][searchable]=true',
 'columns[1][orderable]=true',
 'columns[1][search][value]=',
 'columns[1][search][regex]=false',
 'columns[2][data]=resultname',
 'columns[2][name]=resultname',
 'columns[2][searchable]=true',
 'columns[2][orderable]=true',
 'columns[2][search][value]=',
 'columns[2][search][regex]=false',
 'columns[3][data]=gender',
 'columns[3][name]=gender',
 'columns[3][searchable]=true',
 'columns[3][orderable]=true',
 'columns[3][search][value]=',
 'columns[3][search][regex]=false',
 'columns[4][data]=age',
 'columns[4][name]=age',
 'columns[4][searchable]=true',
 'columns[4][orderable]=true',
 'columns[4][search][value]=',
 'columns[4][search][regex]=false',
 'columns[5][data]=disposition',
 'columns[5][name]=disposition',
 'columns[5][searchable]=true',
 'columns[5][orderable]=true',
 'columns[5][search][value]=',
 'columns[5][search][regex]=false',
 'columns[6][data]=membertype',
 'columns[6][name]=membertype',
 'columns[6][searchable]=true',
 'columns[6][orderable]=true',
 'columns[6][search][value]=',
 'columns[6][search][regex]=false',
 'columns[7][data]=confirm',
 'columns[7][name]=confirm',
 'columns[7][searchable]=true',
 'columns[7][orderable]=true',
 'columns[7][search][value]=',
 'columns[7][search][regex]=false',
 'columns[8][data]=runnerid',
 'columns[8][name]=runnerid',
 'columns[8][searchable]=true',
 'columns[8][orderable]=true',
 'columns[8][search][value]=',
 'columns[8][search][regex]=false',
 'columns[9][data]=hometown',
 'columns[9][name]=hometown',
 'columns[9][searchable]=true',
 'columns[9][orderable]=true',
 'columns[9][search][value]=',
 'columns[9][search][regex]=false',
 'columns[10][data]=club',
 'columns[10][name]=club',
 'columns[10][searchable]=true',
 'columns[10][orderable]=true',
 'columns[10][search][value]=',
 'columns[10][search][regex]=false',
 'columns[11][data]=time',
 'columns[11][name]=time',
 'columns[11][searchable]=true',
 'columns[11][orderable]=true',
 'columns[11][search][value]=',
 'columns[11][search][regex]=false',
 'order[0][column]=11',
 'order[0][dir]=asc',
 'start=0',
 'length=10',
 'search[value]=',
 'search[regex]=false',
 '_=1468056202626']

I see the following error

    rowTable = DataTables(request.args, ManagedResult, ManagedResult.query.filter_by(club_id=club_id,raceid=raceid), columns, dialect='mysql')
  File "C:\Users\Lou\Documents\Lou's Software\projects\rrwebapp\rrwebapp\lib\site-packages\datatables\__init__.py", line 172, in __init__
    self.run()
  File "C:\Users\Lou\Documents\Lou's Software\projects\rrwebapp\rrwebapp\lib\site-packages\datatables\__init__.py", line 226, in run
    self.sorting()
  File "C:\Users\Lou\Documents\Lou's Software\projects\rrwebapp\rrwebapp\lib\site-packages\datatables\__init__.py", line 398, in sorting
    int(self.request_values[columnOrder % i])].column_name,
IndexError: list index out of range

displaying custom column from sqlalchemy into datatable

I have a front end datatable that has 6 columns( ID, first_name, last_name, value, full_name, multiply)

Suppose I have a model user and it has following data.

ID first_name last_name value
1 zack miller 10
2 ryan mint 20
5 jack thomas 30
4 john mac 10

I would like to have another two column generated on server side for the frontend datatable.
1)"full_name" which is first_name+"-"+last_name.
2)"multiply" which is ID*value

My datatable should be displayed as mentioned below:-

ID first_name last_name value full_name multiply
1 zack miller 10 zack-miller 10
2 ryan mint 20 ryan-mint 40
5 jack thomas 30 jack-thomas 150
4 john mac 10 john-mac 40

How should I define ColumnDT() of these two columns?
How should I write my query in sqlalchemy which I can pass to DataTables() query parameter?
(select user.id,user.first_name,user.last_name,user.value,user, user.first_name+"-"+user.last_name as 'full_name', user.ID+user.value as 'multiply' from user)

Thank you.

TypeError: int() argument must be a string or a number, not 'list' - line 245

Hi,

I dowloaded latest version 1.1.0 and got the error when render the table:

File "/home/vagrant/pycharm_proj/arp_norm/venv/lib/python2.7/site-packages/datatables/init.py", line 245, in output_result
output['draw'] = str(int(self.params['draw']))
TypeError: int() argument must be a string or a number, not 'list'

Datatable crud operations

My Datatable has 4 columns. id, name, email, edit/delete.

On server side, I have defined my columns as (User is my model)
columns = [
ColumnDT(User.id),
ColumnDT(User.username),
ColumnDT(User.email)
]
query = User.query.with_entities(User.id, User.username, User.email)
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
return jsonify(rowTable.output_result())

returned data contains only 3 columns but my datatable has 4 columns. So I am getting an error. What should I do?

Error while using outerjoin in query

I'm using several "outerjoin"s in the query I use for DataTables.
This results in the following error:

  File "venv/lib/python2.7/site-packages/datatables/__init__.py", line 126, in __init__
    self.run()
  File "venv/lib/python2.7/site-packages/datatables/__init__.py", line 196, in run
    tmp_row = get_attr(self.results[i], col.column_name)
  File "venv/lib/python2.7/site-packages/datatables/__init__.py", line 37, in get_attr
    output = getattr(output, x)
AttributeError: 'NoneType' object has no attribute 'Name'

The fix is very simple:
replace line 37 in init.py
output = getattr(output, x)
with

if hasattr(output, x):
    output = getattr(output, x)
else:
    output = None

What is the best way to get this fixed, do I create a pull request myself?

Flask example broken due to updated API

I have never used flask, perhaps someone else is willing to update the example in a similar way as the pyramid example?

Pyramid example with YADCF plugin could use some work

I more or less of hacked in the yadcf plugin, by copying the .js and .css dependencies straight from the http://yadcf-showcase.appspot.com/ examples. This works, but not sure if the demo really needs to rely on so many js and css files. I would greatly appreciate it if someone with a bit more front-end experience could:

• clean up js and css dependencies
• make the table look a bit less hideous

When instantiating a DataTable, tables w/ tablename that need double-quotes aren't supported

I'm using a database model not created by me so I'm hesitant to change it.
I tried with a table where the tablename is not case-sensitive and it works fine.

Modifying from the example:

view.py
columns = []
columns.append(ColumnDT('id'))
query = db.session.query(Test).filter(Test.id < 100)
rowTable = DataTables(request.args, Test, query, columns)

model.py
class Test(Base)
__tablename__= 'Test'
id = Column(Integer, primary_key=True)
# columns

I get this error:

ProgrammingError: (psycopg2.ProgrammingError) missing FROM-clause entry for table "test"
LINE 3: WHERE "Test".id < 100 ORDER BY Test.id ASC