Request parameters from DataTables

[darkopetrovic]

Something I don't understand. DataTables sends parameters like iSortCol_0=0&sSortDir_0=asc via GET method (example for ordering in this case) but in your code you expect that we create ourself the parameter with (?):

order=[{"column": 1, "dir": "desc"}]

    for i, item in enumerate(order or []):
        for key, value in item.items():
            x["order[{}][{}]".format(i, key)] = str(value)

before passing it to the DataTable object as first parameter. You mention that we can pass the request parameter but I've seen nothing in your code that deal with the request parameters from DataTables. Am I missing something?

Thanks for you help.

[orf]

Hey there,
The iSortCol_0 syntax for requests is a legacy format, this library uses the new format, so the syntax for ordering is "order[i][column]".

If you look in the readme.md file you can see an example of the DataTables constructor, specifically the first parameter should be a dictionary-like object. So for example you can do table = DataTable(request.GET, ..), passing the GET parameters (which actually a MultiDict or something like that). Or you can construct a dictionary of options manually and pass it.

Hope this helps!

[darkopetrovic]

Yes that helped! I used sAjaxSource instead of ajax to specify the url. Now every things is ok. Thanks you.

In the meantime just one more thing. I noticed that when we specify a combined model_name for example like table.field, a join is automatically added to the query. That's fine and awesome, but I faced the problem that in my particular case I needed an outerjoin instead. So I tricked a little bit the code for my needs. This could be an idea in order to complete the package for a future release to allow the user to choose which type of join to perform, maybe in the column definition.

[orf]

Yeah that is a good idea, did you just change Line 53? Shouldn't be hard to just make that configurable, thanks for the feedback :)

[darkopetrovic]

As I understand now, we shouldn't even have to use the .join() method on the query. When we specify the relationship with lazy='joined', the query is automatically created with the JOIN or LEFT OUTER JOIN. Whether the join is “outer” or not is determined by the innerjoin parameter. So it is better I guess to let the user choose what to do at the creation of the relationship.

I removed the completely the lines 55 and 56. But we need to rewrite the get_value method in order to avoid an exception in case of outer join when there is null values. I did it like that:

   def get_value(self, key, instance):
        attr = key.model_name
        if "." in attr:
            subkey, attr = attr.split(".", 1)
            if getattr(instance, subkey) is not None:
                instance = getattr(instance, subkey)

        if key.filter is not None:
            r = key.filter(instance)
        else:
            try:
                r = getattr(instance, attr)
            except:
                r = None

        return r() if inspect.isroutine(r) else r

The records are returned correctly. But the ordering of a joined field doesn't work any more because we removed the .join(). I guess, because of the call to query.order_by(), the relationship isn't considered any more when we create the results with query.all().

It must be a way to add the JOIN to the query with the information contained in th relationship without explicitly calling the .join() method on the query. Because what I did previously was:

    if model_name[0] == '!':
        model_name = model_name[1:]
        outerjoin = True

    d = DataColumn(name=name, model_name=model_name, filter=filter_func)
    self.columns.append(d)
    self.columns_dict[d.name] = d

for column in (col for col in self.columns if "." in col.model_name):
    if outerjoin:
        self.query = self.query.outerjoin(column.model_name.split(".")[0])
    else:
        self.query = self.query.join(column.model_name.split(".")[0])

and defining the column with ('something', '!table.field').

It would be more logic to let the creation of the query by the system with the relation than ourself by choosing like I did above between a .join() or .outerjoin().

[darkopetrovic]

It seems that we need to create the join explicitly if we want the order_by to work on the query. But to let the user choose between an inner or outer join ONLY in the relationship (with innerjoin parameter), I wrote this code to get from this parameter (to replace the line 55 and 56):

from sqlalchemy.inspection import inspect as sqlinspect

relationships = sqlinspect(self.model).relationships
for column in (col for col in self.columns if "." in col.model_name):
    # get the type of relationship with the column
    model_attribute = column.model_name.split(".")[0]
    if model_attribute in relationships:
        relation_attribute = relationships[model_attribute]
        if relation_attribute.innerjoin is True:
            self.query = self.query.join( model_attribute )
        else:
            self.query = self.query.outerjoin( model_attribute )