2

I have tables similar to the following:

class User(Base):
    id    = Column(Integer, primary_key=True)
    name  = Column(String(255, unique=True)

class Document(Base):
    id    = Column(Integer, primary_key=True)
    name  = Column(String(255, unique=True)
    customer_id = Column(ForeignKey('User.id'))

I would like to list Users in Flask-Admin's ModelView (sqla) together with number of documents they have created (func.count(Document)). Is there some possibility to achieve it using SqlAlchemy and standard Flask-Admin ModelView, without specifying custom SQL query?

claperius
  • 311
  • 7
  • 16
  • 1
    I do not think there is a way to achieve that without using a custom query. See http://stackoverflow.com/a/21454133/99594 for almost the same question where instead of `min` you should use `count`. – van Oct 07 '14 at 09:29
  • you can add the relationship. explained as [this](http://stackoverflow.com/questions/16160507/flask-admin-not-showing-foreignkey-columns) – stamaimer Apr 18 '16 at 16:39
  • If anyone else comes across this like me, looking for how to add custom fields that don't require custom database queries, the example in [this question](https://stackoverflow.com/questions/31801770/how-to-set-the-value-of-an-extra-field-in-the-list-view) should help. – Sam Jan 29 '19 at 08:37

1 Answers1

3

You can do this by overriding the ModelView class' get_query() function. This will create a view of the query you write, as opposed to the schema defined.

You might also need a user_document_table as a join in your query.

For example:

class UserView(ModelView):

column_list = (
    "user_name",
    "n_documents"
)

def get_query(self):
    return (
        self.session.query(
            User.name.label("user_name"), 
            func.count(Document.id).label("n_documents")
        )
        .join(user_document_table)
        .join(Document)
        .group_by(User.id)
    )
Karim Tabet
  • 1,789
  • 1
  • 14
  • 35
  • Only problem here is that if the count is 0, the row won't show. Change that to outerjoin and you'll get rows with count 0. – Ken Kinder Jun 16 '17 at 22:41