related_views not loading in widgets['related_views'] but does load in related views

[jnorton2]

I am having a problem working with related views.
An Event has many Attendees. You can see a snapshot of the models here:

class Attendee(db.Model):
    __bind_key__ = 'WILL_DB'
    __tablename__ = 'attendee'

    id = Column(INTEGER, primary_key=True)
    event_id = Column(INTEGER, index=True)
    ...

    Event = relationship('Event', primaryjoin="Attendee.event_id == foreign(Event.id)")


class Event(db.Model):
    __bind_key__ = 'WILL_DB'
    __tablename__ = 'event'

    id = Column(INTEGER, primary_key=True)
    rep_id = Column(String(50), nullable=False, server_default=text("''"))
    ...

My view functions are as follows

class AttendeeView(ModelView):
    datamodel = SQLAInterface(Attendee)
    list_columns = [x.name for x in Attendee.__table__.columns]
    list_columns.remove("signature")
    list_columns.remove("hcp_data")

    show_columns = [x.name for x in Attendee.__table__.columns]
    show_columns.remove("signature")
    show_columns.remove("hcp_data")


class EventView(ModelView):
    datamodel = SQLAInterface(Event)
    related_views = [AttendeeView]
    show_template = 'appbuilder/general/model/show_cascade.html'

...and registering:

appbuilder.add_view(
    EventView,
    "Events",
    icon="fa-calendar",
    category="WILL Support",
    category_icon="fa-wheelchair"
)
appbuilder.add_view(
    AttendeeView,
    "Attendees",
    icon="fa-user",
    category="WILL Support",
    category_icon="fa-wheelchair"
)

When navigating to a /eventview/show/10 page for example, I get this output

  File "<project_root>/app/templates/appbuilder/general/model/show_cascade.html", line 15, in template
    {{ widgets.get('related_views')[loop.index - 1](pk = pk)|safe }}
TypeError: 'NoneType' object is not callable

When debugging it seems that in this block which in in the file show_cascade.html and similarly in the other show htmls files.

{% block related_views %}
    {% if related_views is defined %}
        {% for view in related_views %}
            {% call lib.accordion_tag(view.__class__.__name__,view.title, False) %}
                {{ widgets.get('related_views')[loop.index - 1](pk = pk)|safe }}
            {% endcall %}
        {% endfor %}
    {% endif %}
{% endblock related_views %}

The variable related_views contains the appropriate AttendeeView however, widgets['related_views'] has nothing.

Is there an issue with my relationship? This is an old database that doesn't have the correct key setups so I need to do the relationship this way : Event = relationship('Event', primaryjoin="Attendee.event_id == foreign(Event.id)")

Same data as this issue
#973

[dpgaspar]

Did not tested it, have you tried changing the order of the add_view

[jnorton2]

Yes. That is actually the reversed order after I tried switching it earlier today.

appbuilder.add_view(
    AttendeeView,
    "Attendees",
    icon="fa-user",
    category="WILL Support",
    category_icon="fa-wheelchair"
)
appbuilder.add_view(
    EventView,
    "Events",
    icon="fa-calendar",
    category="WILL Support",
    category_icon="fa-wheelchair"
)

This configuration results in the same error.

[jnorton2]

I am seeing this error in the console when starting the server however.
Can't find relation on related view AttendeeView

[jnorton2]

It is not recognizing the Event relation as a real relation.
flask_appbuilder/models/sqla/interface.py

        fk = related_view.datamodel.get_related_fk(self.datamodel.obj)
        if related_view.datamodel.is_relation_many_to_one(fk):
            filters.add_filter_related_view(
                fk,
                self.datamodel.FilterRelationOneToManyEqual,
                self.datamodel.get_pk_value(item),
            )
        # Check if it's a many to many model relation
        elif related_view.datamodel.is_relation_many_to_many(fk):
            filters.add_filter_related_view(
                fk,
                self.datamodel.FilterRelationManyToManyEqual,
                self.datamodel.get_pk_value(item),
            )
        else:
            if isclass(related_view) and issubclass(related_view, BaseView):
                name = related_view.__name__
            else:
                name = related_view.__class__.__name__
            log.error("Can't find relation on related view {0}".format(name))
            return None

When debugging fk:'Event'
and .is_relation_many_to_one and .is_relation_many_to_many both return False

[jnorton2]

And here is saying the direction is ONETOMANY which is why it is failing.

        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == "MANYTOONE"
        except Exception:
            return False

Does _get_related_view_widget not support ONETOMANY ?

[jnorton2]

Copying the DB and adding the ForeignKey Constraints fixed the problem.

class Attendee(db.Model):
    __bind_key__ = 'WILL_DB'
    __tablename__ = 'attendee'

    id = Column(INTEGER, primary_key=True)
    event_id = Column(ForeignKey(u'event.id'), index=True)
    ...
    Event = relationship('Event')
  

Im confused on how this is an issue though. I guess using the primaryjoin param when creating the relationship doesn't set the relationship type correctly?

[jnorton2]

Solution:
Get the DB developer to implement foreign keys correctly.

[edurbs]

Solution:
Get the DB developer to implement foreign keys correctly.

But how to do it? I did see the foreign keys, and it seems to be correctly.

This is my table on postgres:

CREATE TABLE public.cidade
(
id integer NOT NULL DEFAULT nextval('cidade_id_seq'::regclass),
nome character varying(100) NOT NULL,
cod_ibge integer,
uf_id integer NOT NULL,
CONSTRAINT cidade_pkey PRIMARY KEY (id),
CONSTRAINT cidade_uf_id_fkey FOREIGN KEY (uf_id)
REFERENCES public.uf (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE public.uf
(
id integer NOT NULL DEFAULT nextval('uf_id_seq'::regclass),
nome character varying(50) NOT NULL,
sigla character varying(2) NOT NULL,
CONSTRAINT uf_pkey PRIMARY KEY (id),
CONSTRAINT uf_nome_key UNIQUE (nome),
CONSTRAINT uf_sigla_key UNIQUE (sigla)
)

[jnorton2]

@edurbs Can you post your model and view python code as snippets? Maybe I can help

[rodrigues-pedro]

@jnorton2 i can post if you are stil willing to help.

In my case one pacient(Paciente) can have many results(Resultado), and I want to be able to show the pacient info from the results view. So is actually a Many-To-One relationship, as show here (https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#many-to-one)

Here we have the models:

class Resultado(db.Model):
    __table_args__ = {'schema': 'rgoias'}

    id = db.Column(db.Integer, primary_key=True)
    paciente_id = db.Column(db.Integer, db.ForeignKey('rgoias.paciente.id'))
    ...
    data_hora = db.Column(db.DateTime, index=True, default=datetime.now)
    paciente = db.relationship('Paciente')

class Paciente(db.Model):
    __table_args__ = {'schema': 'rgoias'}

    id = db.Column(db.Integer, primary_key=True)
    ...

Here we have the views:

class PacienteModelView(ModelView):
    route_base = "/pacientes"
    datamodel = SQLAInterface(Paciente)
    base_order = ("nome_sus", "asc")

    ... labels, titles and columns ....

class ResultadoModelView(ModelView):
    route_base = "/admin"
    datamodel = SQLAInterface(Resultado)
    related_views = [PacienteModelView]
    base_order = ("data_hora", "desc")

    ... labels, titles and columns ....

...and registering:

appbuilder.add_view(
    PacienteModelView,
    "Pacientes",
    icon = "fa-user-circle",
    category = "Resultados",
    category_icon="fa-files-o"
)
appbuilder.add_view(
    ResultadoModelView,
    "Resultados",
    icon = "fa-files-o",
    category = "Resultados"
)

I am also seeing this error in the console when starting the server however.
Can't find relation on related view PacienteModelView

But I can't figure out what I'am doing wrong!

[jnorton2]

1. Are you sure you have the foreignKeys setup in the database?
2. Try changing to this in the Resultado model
   paciente_id = db.Column(db.Integer, db.ForeignKey('paciente.id'), index=True)
3. Try specifying the relationship like this in the Resultado model
   paciente = db.relationship('Paciente', foreign_key=[paciente_id])
4. Try adding this to the PacienteModel
   resultados = db.relationship('Resultado')

[rodrigues-pedro]

I've tried all that, and it still doesn't seem to work in the way I want it to.
What is bugging me the most is that if I swap where I put the related view it works fine:

class ResultadoModelView(ModelView):
    route_base = "/admin"
    datamodel = SQLAInterface(Resultado)
    # related_views = [PacienteModelView]
    base_order = ("data_hora", "desc")
    label_columns = {'paciente_nome': 'Nome do Paciente',
                     'grau_fim': 'Grau Final',
                     'grauPCFS': 'Grau na Escala PCFS',
                     'graumMRC': 'Grau na Escala mMRC',
                     'data_hora': 'Data da autoavaliação'}

    add_title  = "Adicionar Resultado"
    edit_title = "Editar Resultado"
    show_title = "Visualizar Resultado"
    list_title = "Lista de Resultados"

    list_columns = ['paciente_nome', 'grau_fim', 'grauPCFS', 'graumMRC']
    add_columns = ['grau_fim', 'grauPCFS', 'graumMRC', 'data_hora']
    edit_columns = add_columns + ['pacientes']
    show_columns = edit_columns

class PacienteModelView(ModelView):
    route_base = "/pacientes"
    datamodel = SQLAInterface(Paciente)
    related_views = [ResultadoModelView]
    base_order = ("nome_sus", "asc")
    label_columns = {'nome': 'Nome Declarado',
                     'nome_sus': 'Nome Completo'}

    add_title  = "Adicionar Paciente"
    edit_title = "Editar Paciente"
    show_title = "Visualizar Paciente"
    list_title = "Lista de Pacientes"

    list_columns = ['nome', 'nome_sus']

However it makes less sense from the point of view of the final users of this plataform.

Now I'm wondering if FAB have a bug in terms of getting the parent model from the child one or something.

[jnorton2]

Yes you need to have the parent view first.
See this example: https://flask-appbuilder.readthedocs.io/en/latest/api.html#masterdetailview

[Campostrini]

I think I have a similar problem, yet I think I am not doing anything wrong (duh). Probably I am missing something about FAB.

Here is the models file:

class Patient(Model):
    __tablename__ = 'patient'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("ab_user.id"), nullable=False)
    extra_data = Column(String(50))
    doctor_patient_relationship = relationship("DoctorPatientRelationship", back_populates="patient")

class Doctor(Model):
    __tablename__ = 'doctor'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("ab_user.id"), nullable=False)
    doctor_patient_relationship = relationship("DoctorPatientRelationship", back_populates="doctor")

class DoctorPatientRelationship(Model):
    __tablename__ = 'doctor_patient_relationship'
    id = Column(Integer, primary_key=True)
    doctor_id = Column(Integer, ForeignKey('doctor.id')) #, primary_key=True)
    patient_id = Column(Integer, ForeignKey('patient.id')) #, primary_key=True)
    extra_data = Column(String(50))
    doctor = relationship("Doctor", back_populates="doctor_patient_relationship")
    patient = relationship("Patient", back_populates="doctor_patient_relationship")

and the views:

class DoctorView(ModelView):
    datamodel = SQLAInterface(Doctor)

    add_columns = [
        'user_id',
    ]
    list_columns = [
        'id',
        'user_id',
    ]
    edit_columns = [
        'user_id',
    ]


class PatientView(ModelView):
    datamodel = SQLAInterface(Patient)

    add_columns = [
        'extra_data',
        'user_id',
    ]
    list_columns = [
        'id',
        'user_id',
        'extra_data',
        'doctor_patient_relationship.extra_data',
    ]
    edit_columns = [
        'user_id',
        'extra_data',
    ]
    #base_filters = [[]]

class DoctorPatientRelationshipView(ModelView):
    datamodel = SQLAInterface(DoctorPatientRelationship)
    related_views = [DoctorView, PatientView]

    add_columns = [
        'patient_id',
        'doctor_id',
        'extra_data',
    ]
    list_columns = [
        'patient_id',
        'doctor_id',
        'extra_data',
        'patient.extra_data',
    ]
    edit_columns = [
        'patient_id',
        'doctor_id',
        'extra_data',
    ]

"""
    Application wide 404 error handler
"""


@appbuilder.app.errorhandler(404)
def page_not_found(e):
    return (
        render_template(
            "404.html", base_template=appbuilder.base_template, appbuilder=appbuilder
        ),
        404,
    )


db.create_all()

appbuilder.add_view(DoctorView, "Doctors", icon="fa-folder-open-o")
appbuilder.add_view(PatientView, "Patients", icon="fa-folder-open-o")
appbuilder.add_view(DoctorPatientRelationshipView, "Doctor Patient Relationships", icon="fa-folder-open-o")

and yet

    {{ widgets.get('related_views')[loop.index - 1](pk = pk)|safe }}
TypeError: 'NoneType' object is not callable

It all comes down to:

def is_relation_many_to_many(self, col_name: str) -> bool:
        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == "MANYTOMANY"
            return False
        except KeyError:
            return False

where

self.list_properties[col_name].direction.name

is "ONETOMANY"

I hope it's not a problem if I ask for your help @jnorton2

[Campostrini]

I added:

def _get_related_view_widget(
        self,
        item,
        related_view,
        order_column="",
        order_direction="",
        page=None,
        page_size=None,
    ):

        fk = related_view.datamodel.get_related_fk(self.datamodel.obj)
        filters = related_view.datamodel.get_filters()
        # Check if it's a many to one model relation
        if related_view.datamodel.is_relation_many_to_one(fk):
            filters.add_filter_related_view(
                fk,
                self.datamodel.FilterRelationOneToManyEqual,
                self.datamodel.get_pk_value(item),
            )
        # Check if it's a many to many model relation
        elif related_view.datamodel.is_relation_many_to_many(fk):
            filters.add_filter_related_view(
                fk,
                self.datamodel.FilterRelationManyToManyEqual,
                self.datamodel.get_pk_value(item),
            )
        ########## New Part I added
        elif related_view.datamodel.is_relation_one_to_many(fk):
            filters.add_filter_related_view(
                fk,
                self.datamodel.FilterRelationManyToOneEqual,
                self.datamodel.get_pk_value(item),
            )
        #########################
        else:
            if isclass(related_view) and issubclass(related_view, BaseView):
                name = related_view.__name__
            else:
                name = related_view.__class__.__name__
            log.error("Can't find relation on related view {0}".format(name))
            return None
        return related_view._get_view_widget(
            filters=filters,
            order_column=order_column,
            order_direction=order_direction,
            page=page,
            page_size=page_size,
        )

and

class FilterRelationManyToOneEqual(FilterRelation): 
    name = lazy_gettext("Filter Many To One")
    arg_name = "rel_m_o"

    def apply(self, query, value):
        query, field = get_field_setup_query(query, self.model, self.column_name)
        try:
            rel_obj = self.datamodel.get_related_obj(self.column_name, value)
        except SQLAlchemyError as exc:
            logging.warning(
                "Filter exception for %s with value %s, will not apply", field, value
            )
            self.datamodel.session.rollback()
            raise ApplyFilterException(exception=exc)
        return query.filter(field.contains(rel_obj))

and it works. Am I missing something? @dpgaspar

Maybe FilterRelationManyToOneContains is more appropriate

[jnorton2]

I just ended up using a different view / making my own.
Is the approach you have working for you?

[Campostrini]

@jnorton2 for now I'm modifying FAB. Rightly there are other parts in the code that break after the modifications.
Had to modify apply_all in models.filters.Filter

    def apply_all(self, query):
        for flt, values in zip(self.filters, self.values):
            try:
                query = flt.apply(query, values)
            except ApplyFilterException as e:
                logging.warning(
                    "Apply filter exception for %s with values %s, will unpack", flt, values
                )
                for value in values:
                    query = flt.apply(query, value)
        return query

But I'm not sure it's worth it.

What approach did you end up using?

[Campostrini]

I was wondering if there is specific reason for some combinations/cases not being supported.

[jnorton2]

Have you tried adding a related view to the patient view instead of a combined view with 2 related views? That combined view wont be very useful.

You can add the DoctorView as a related view on the PatientView. If that works we can go from there.