CODEWITHSUNDEEP
pythonjqueryflaskdatatablessqlalchemy
Chockomonkey
Chockomonkey

Reputation: 4135

Is there a way modify table data using sqlalchemy-datatables?

I spent some time playing around with sqlalchemy-datatables today. My goals were to simply achieve ajax-loading of query data into datatables as well as pagination. It took care of these very easily.

The biggest roadblock so far has been the lack of documentation even though there seems to be rather decent usage of this module. As an aside, if anyone knows of some documentation, please share!

In spite of this, and with some trial and error, I was able to get the below code working (I've intentionally left out my models for brevity).

route from views

@app.route('/accounts/query/<path:select>/', methods=['GET'])
@roles_accepted('admin', 'copy', 'client')
def query_accounts(select):
    if select == 'all':

        client = db.aliased(User)
        csr = db.aliased(User)

        columns = [
            ColumnDT(Account.id),
            ColumnDT(Account.name),
            ColumnDT(client.fullname),
            ColumnDT(csr.fullname),
            ColumnDT(client.current_login_at)
        ]

        query = db.session.query() \
                    .select_from(Account) \
                    .outerjoin(client, Account.owner) \
                    .outerjoin(csr, Account.copywriter)

        params = request.args.to_dict()
        rowTable = DataTables(params, query, columns)

        return jsonify(rowTable.output_result())

    return abort(404)

html from template

<table class="table" id="datatable">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Client</th>
      <th>CSR</th>
      <th>Last Access</th>
    </tr>
  </thead>
  <tbody></tbody>
</table>

javascript from template

$(document).ready(function () {
  $('table.table').dataTable({
    "processing": true,
    "serverSide": true,
    "ajax":"{{ url_for('query_accounts', select='all') }}",
    "order": [1, 'asc']
  });
});

One of my few points of contention left is this: Since all of the datatable information is being drawn by the jquery-datatables module, and sqlalchemy-datatables is supplying that information, I can't see a way to be able to customize the data within the table as I have typically done in nearly every other datatable I've used (see below for an example of creating a link within a row to a specific user record).

<table class="table" id="datatable">
  <thead>
    <tr>
      <th>Email</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Business Name</th>
      <th>Last Login</th>
      <th>Roles</th>
    </tr>
  </thead>
  <tbody>
    {% for user in users %}
    <tr>
      <td><a href="{{ url_for('users', id=user.id) }}">{{ user.email }}</a></td>
      <td>{{ user.first_name }}</td>
      <td>{{ user.last_name }}</td>
      <td>{{ user.business_name }}</td>
      <td>{{ user.last_login_at|local_datetime }}</td>
      <td>{{ user.roles|rolesformat }}</td>
    </tr>
    {% endfor %}
  </tbody>
</table>

Is there a way to create links like this in the tables created by sqlalchemy-datatables?

Upvotes: 0

Views: 815

Answers (1)

Chockomonkey
Chockomonkey

Reputation: 4135

As often happens, a night's sleep and a fresh look at the problem will yield results.

This question actually had nothing to do with sqlalchemy-datatables, but instead was was more about datatables itself. I found my answer here: https://datatables.net/manual/data/renderers which discusses how to modify table data as it's being rendered.

Here's the working javascript to convert the name data in my table into links using the id.

$(document).ready(function () {
    var table = $('table.table').dataTable( {
        "processing": true,
        "serverSide": true,
        "ajax":"{{ url_for('query_accounts', select='all') }}",
        "order": [1, 'asc'],
        /* render name column strings as links */
        "columnDefs": [ {
            "targets": 1,
            "data": null,
            "render": function (data, type, row, meta) {
                return "<a href='{{ url_for('accounts') }}" + data[0] + "/'>" + data[1] + "</a>";
            }
        } ]
    } );
});

Upvotes: 2

Related Questions