Sort on Strings?

[dpgaspar]

Sort on Strings?

[dpgaspar]

You can sort on strings, but you can't sort on relations.

[ben-github]

Ahh, you are right it isn't strings but relations. Would sorting on relations be difficult to implement? If you point to the right file(s) to look at, I might take a swing at it.

[dpgaspar]

Excellent! one would have to join the models.

https://github.com/dpgaspar/Flask-AppBuilder/blob/master/flask_appbuilder/models/sqla/interface.py#L60

I was thinking (also) about accepting, multiple models on views for some time. It would 'solve' this and open different possibilities.

[ben-github]

I'm afraid I'm too knowledgeable about SQLAlchemy and I'm not following. The query function you reference does seem to have any information about table(s) in question. Is self.obj an instance of the class of the table being queried? If so, am I right that I would need to iterator over self.obj for each column and for any relationship found, add a join to the query?

[ben-github]

Digging a little more into the code, it seems that self.obj is the class with the table in it, but doing session.query() on that table does return relationships, so I don't think we need a join -- it already has the information. My guess is that somewhere downstream it doesn't know how to handle the relationship data (instead of just a column data) in order to sort, etc.

[ben-github]

Ok, more digging. It seems the sorting is down via a db query and that they query is a single table with no join. Two options as I see it:

1. Add joins to the SQL query to do the relationships. The you can do db sorts on them.
2. Do db sorts on the indexes used for the relationships. The SQL does something like "Select name,part_id from table" and then later (somehow) uses SQLAlchemy to transform part_id -> to part via the part=relationship("somethat.part_id") in the table definition. So right now you can do sorta that way by putting the column id into the URL (if you know the column name).

option 1 seems harder but better.
option 2 seems much easier as it already is working -- all we need to do is pass the reference column index to the template so it knows it can sort on it. But it isn't ideal as sorting on an id is not the same as sorting on the item. (will be weird for names). But at least it would group items that are the same.

[ben-github]

Actually after thinking about this some more, I think no sorting is pretty reasonable. When you display a relationship, you are displaying an item not defined in SQL, but in the self.repr in SQLAlchemy -- isn't even obvious that you could display a relationship, let alone sort it.

The better solution, I think, is to select data that is actually in the SQL database. Using quickhowto2 as a reference, say you have 2 tables:

class ProductManufacturer(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique = True, nullable=False)

    def __repr__(self):
        return self.name


class ProductModel(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique = True, nullable=False)
    product_manufacturer_id = Column(Integer, ForeignKey('product_manufacturer.id'), nullable=False)
    product_manufacturer = relationship("ProductManufacturer")

    def __repr__(self):
        return self.name

then if you define a view for ProductModel:

class ProductModelView(ModelView):
    datamodel = SQLAModel(ProductModel)
    list_columns = ['name','product_manufacturer']

everything works as expected where the product_manufacturer is a relationship and not sortable. That column gets filled with what the repr function returns.

But really you want to select an actual SQL column. So would it be possible to chose a column from the relationship:

class ProductModelView(ModelView):
    datamodel = SQLAModel(ProductModel)
    list_columns = ['name','product_manufacturer.name']

This way, what is displayed is a real SQL query and sortable. I believe SQLAlchemy lets you access columns from relationships via the product_manufacturer.name syntax, but when I tried that, I get an error. Is there (or should there) be a way to access columns of a relationship in a view. Supporting multiple models in a view is probably pretty complicated but the great thing about relationships in SQLAlchemy is that one model can extend pretty far with behind the scenes joins, etc.

[dpgaspar]

This can be a good idea. But i have to investigate, the best way to do it.
Feel free to dig in some more.

Right now i'm still giving priority to AUTH_USER_REGISTRATION for user db (almost there) and next to auth oid.

[ben-github]

I'm thinking of a syntax like

list_columns = ['name', ['product_manufacturer','name']]

Then when FAB gets a list for a column, it grab the relationship from the first element and iterate the second element through the same function. That way you can do deep nesting if you have linked tables.

[dpgaspar]

On SQLAlchemy you have to join the models. I've added a small change to let the framework accept dotted attrs on models to fetch values from inner models (relations). something like::

list_columns = ['name', 'personal_celphone', 'birthday', 'group.name']

(This will be available on 1.1.0).
But if you force the order_by on 'group.name' you get an error, because we need to join the models.

I will keep this open, because i think it could be an interesting feature in the future, to allow ModelView with multiple joined models.

[ben-github]

Neat. I still don't understand how FAB gets that data to display relationships since the actual SQL query isn't doing the join (does SQLAlchemy do additional sql queries to figure out that data it needs in the relationship fields?).

It would be cool if we could add the joins to the SQLAlchemy as then you could do sorts, etc, on those columns. Also I don't know if this is an easy fix, but when you do a 'table.column' in the list_columns, the display doesn't get the column name and just displays it as 'None'.

[dpgaspar]

You can "fix" this with label_columns::

label_columns = {'model.col':'Some Nice Label to the User'}

But i don't know if you have much to gain here....

PS:

SQLAlchemy: returns objects for the relations (it's an ORM), so if we define a string repr for the object we get a nice object representation. That's how FAB get's the data (no weird tricks here).

And yes doing extra joins it's a bit hard, if we want to do it right.

[ben-github]

Ok, I think I understand this a little better. I took a stab and adding joins to FAB. The problem I'm having is all the functions was an object that is a table, but I don't know how to reference other tables from inside there.

I added this to models/sqla/interface.py

    if len(order_column.split('.')) == 2:
                tabletojoin=getattr(self.obj.parent,order_column.split(".")[0])
                query=query.join(tabletojoin)               

but there is no self.obj.parent -- self.obj is the table object, but I need a way to reference another table object. Is there a (good) way to do this?

[dpgaspar]

We could use different approaches:

1 - Use automatic join, using _get_related_model for each relationship this would imply a new config attr on ModelView to switch on and off the automatic behaviour.

2 - Introduce a new attr on ModelView like datamodel_join where one would declare the joins FAB should do.

This should have careful thinking, these attr's can be introduced on ModelView, BaseModelView or BaseCRUDModelView.

[ben-github]

Ok, that's helpful. I'd like to not need a configuration option but just have FAB do a join when it is necessary. I think we can just search for columns that are associated with other tables and do a join against them using _get_related_model.We could add an attr to disable the behavior, but I'd vote to have default on.

Back to the code, I'm again working in interface.py to modifiy the query function, I did a join by adding

rel=self.list_properties[order_column.split(".")[0]]
  query=query.join(rel.table)                

and this almost works. The SQL it generates (on modified demo of contact and groups)

SELECT contact.id AS contact_id, contact.name AS contact_name, contact.address AS contact_address, contact.birthday AS contact_birthday, contact.personal_phone AS contact_personal_phone, contact.personal_celphone AS contact_personal_celphone, contact.group_id AS contact_group_id FROM contact JOIN "group" ON "group".id = contact.group_id ORDER BY group.name asc  LIMIT ? OFFSET ?' (10, 0)

The SQL syntax is right except that the join is on "group" instead of just group (note the extra quotes) while the order by is group.name (no quotes). Any idea on the mismatched use of quotes?

[ben-github]

Ahh... it is because group is a special SQL command. As long I'm doing a join to a table not named group, it works. What I have it

if len(order_column.split('.')) >= 2:
    for jointable in order_column.split('.')[:-1]:
        query=query.join(self.list_properties[jointable].table)

and this works (at least for one depth, should work for multiple, but I haven't tested) where I can sort based on a relationship column.

But I'm thinking a better approach might be

1. Move that code to work against all the columns, not the order_column. So any displays columns based on relationships get joins.
2. By doing that, all viewed tables are all in one SQL query instead of returned ORM objects that need to be evaluated. I think this should be faster but it also treats all columns, whether from joins or not equal, Any column can be sorted, has a known type (relationship now must return string), etc.

Alternative is to leave it as it is, but somehow flag colums of the form 'joinedtable.column' as sortable. And ideally get the name of those colums into FAB automatically.

Thoughts?

[dpgaspar]

I think that the best approach for now, would be to join all columns that have a dot. This would be a new non breaking feature, this way i don't see a need for a flag, since the dev who implicitly declares a dotted col on 'list_columns' would implicitly wanted to join it.

FAB is very ORM based, and makes heavy use of relations based on objects, this has some upsides and of course some downsides. This feature would lower the downsides of it.

This can be a great new feature, thanks for bringing this up!

[ben-github]

That sounds like a good approach,. I might need a little guidance on how to implement it though.

[dpgaspar]

I've got it working, using your feedback. take a look at the new branch 1.1.1.

I'm using quickhowto2 has a test example.

[ben-github]

That's excellent. I'll play with on my setup and report back.

[ben-github]

Ok, I have it working-ish. A couple comments:

1. Sorting only works if the relationship name matches the table name:

class ContactGroup(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique = True, nullable=False)

    def __repr__(self):
        return self.name
class Contact(Model):
    id = Column(Integer, primary_key=True)
    name =  Column(String(150), unique = True, nullable=False)
    address = Column(String(564))
    birthday = Column(Date, nullable=True)
    personal_phone = Column(String(20))
    personal_celphone = Column(String(20))
    contact_group_id = Column(Integer, ForeignKey('contact_group.id'), nullable=False)
    contact_group_NEWNAME = relationship("ContactGroup")
    gender_id = Column(Integer, ForeignKey('gender.id'), nullable=False)
    gender = relationship("Gender")

Will break sorting because I renamed contact_group to contact_group_NEWNAME and it now doesn't match the tale it joins to (contact_group).

1. The option to sort a column that is from a join is only available if the column is listed in the order_columns list. It would be nice for it to automatically be recognized as filterable.

[dpgaspar]

Could you please update to test it again?

Has for the order_columns i have to figure out the best way to do it.

[ben-github]

Cool. That fixes issue with naming of column not matching the table name.

One more request: if you don't use the label_columns for the joined columns, they are returned as 'None' -- it would be great if you could 'guess' the column name like the other columns do.

I don't really understand what is going on under the hood, but it seems like join columns aren't quite the same as regular columns and it would be great if they looked the same to the rest of FAB. I think if this can be done, the order_columns issue would be fixed automatically.

[dpgaspar]

I've kept the auto generation of order and labels for dotted (join) columns. They are not the same under the hood has you say (this would implicate a major change on SQLAInterface).

So now everything looks the same for dotted cols.

This will be out on 1.1.1

[ben-github]

Perfect. It works great. Thanks so much.

You can close this ticket unless you think there is anything else left to do.

[dpgaspar]

Thank you! Great working with you.