Id column values empty on datatable.js

Stepan123

i got some issue showing one of my database column which is id. For whatever reason all my values are empty as shown on screenshoot. Does anyone knows what could be the reason? Below related code:
**
DataTable js script:**

$(document).ready(function () {
  var table = $("#dataTb").DataTable({
    scrollY: "300px",
    scrollCollapse: true,
    paging: true,
    ajax: "/api/data",
    serverSide: true,
    success: function (ajax) {
      console.log("test.js >> DataTable ajax >>>" + JSON.stringify(ajax));
    },
    columns: [
      { data: "id", searchable: false },
      { data: "first_name" },
      { data: "email", searchable: false },
    ],
    columnDefs: [
      {
        defaultContent: "-",
        targets: "_all",
      },
    ],
  });

View's related api call:

@views.route('/api/data')
def data():
    flash('/api/data', category='success')
    query = User.query
    print(query)
    # search filter
    search = request.args.get('search[value]')
    if search:
        query = query.filter(db.or_(
            User.first_name.like(f'%{search}%'),
            User.email.like(f'%{search}%')
        ))
    total_filtered = query.count()

    # sorting
    order = []
    i = 0
    while True:
        col_index = request.args.get(f'order[{i}][column]')
        if col_index is None:
            break
        col_name = request.args.get(f'columns[{col_index}][data]')
        if col_name not in ['id', 'first_name', 'email']:
            col_name = 'first_name'
        descending = request.args.get(f'order[{i}][dir]') == 'desc'
        col = getattr(User, col_name)
        if descending:
            col = col.desc()
        order.append(col)
        i += 1
    if order:
        query = query.order_by(*order)

    # pagination
    start = request.args.get('start', type=int)
    length = request.args.get('length', type=int)
    query = query.offset(start).limit(length)

    for i in query:
        print(str(i.id) + i.first_name)

    # response
    return {
        'data': [user.to_dict() for user in query],
        'recordsFiltered': total_filtered,
        'recordsTotal': User.query.count(),
        'draw': request.args.get('draw', type=int)
    }

HTML:

<div class="container">
  <div class="row">
    <div class="col-xl-12">
      <button id="buttonDelete">Delete selected row</button>
      <table id="dataTb" class="table table-striped table-hover">
        <thead>
          <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
          </tr>
        </thead>
        <tbody></tbody>
      </table>
    </div>
  </div>

Screenshoot (see highlited on red):

I know for sure after debug in def data(): at this point:

for i in query:
print(str(i.id) + i.first_name)

all ids are filled out

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Stepan123

Apologize bad formatted post nevertheless not able to find edit option.

kthorngren

Start by looking at the Ajax response by using the browser's network inspector tool.

You have:

columnDefs: [
{
defaultContent: "-",
targets: "_all",
},

So if the returned data doesn't have an id object then the columns.defaultContent value will be used.

I don't see anything specific in your Python code that would be a problem.
Let us know what you find in the ajax response. Please post an example of the response. Sanitize the first_name and email columns if you need.

Use Markdown to format the code:

Kevin

Stepan123

Not sure but i think thats whats i see in network: No ID column ?

Stepan123

what i do also not understand is why i see nothing in the console when my datatable js function is called. On this part of code. I even added error one but none of them seems to be called. Any thoughts?

success: function (ajax) {
      console.log("test.js >> DataTable ajax >>>" + JSON.stringify(ajax));
    },
error: function (err) {
      console.log("my message" + err);
    },

colin

Yep, the server isn't sending back an ID value so

      { data: "id", searchable: false },

will fail as it doesn't exist. You'll need to look into the server-side scripts to see why that is the case, or just remove the column from the table if it isn't required.

For your other question, see ajax, it states:

success - Must not be overridden as it is used internally in DataTables. To manipulate / transform the data returned by the server use

Colin

Stepan123

how exactly should i check it on server side then?

if success cannot be used, what shjould i use then?

kthorngren

how exactly should i check it on server side then?

Something in your DB query is not fetching the id. Look at your code to see what its doing.

if success cannot be used, what shjould i use then?

Take a look at the ajax docs Colin linked to. Here is some additional information:

success - Must not be overridden as it is used internally in DataTables. To manipulate / transform the data returned by the server use ajax.dataSrc (above), or use ajax as a function (below).

Looks like Colin may have not copied everything for the docs snippet.

Kevin

Stepan123

It is taken. Look at my main post where i put section of "View's related api call:". Before sending return i print out what's there. Ids are there, look:

Stepan123

Ok git it. user.to_dict() in my model, i missed id there def to_dict(self):

        return {
            'id': self.id, // <<<<< was missing.
            'email': self.email,
            'first_name': self.first_name
        }

So u saying that ajax.dataSrc can be used for success/error cases?

Thanks to all in advance for all ur help here !