Flask-admin: Dynamic filter on foreignkey

[MaasErwin]

I am using Flask-admin and SQLalchemy
I want to limit the choices of a foreign key based on a choice of the foreign key of the parent tabeld.

class City(Base):
   __tablename__ = 'city'
   id = Column(Integer, primary_key=True)
   name = Column(String, nullable=False)

   def __unicode__(self):
      return self.name

class Street(Base):
   __tablename__ = 'street'
   id = Column(Integer, primary_key=True)
   name = Column(String, nullable=False)
   city = Column(Integer, ForeignKey(City.id), nullable=False)
   city_ref = relationship(City) 

   def __unicode__(self):
      return self.name

class Adress(Base):
   __tablename__ = 'adress'
   id = Column(Integer, primary_key=True)
   familiyname = Column(String, nullable=False)
   street = Column(Integer, ForeignKey(Street.id), nullable=False)
   street_ref = relationship(Street)
   city_ref = relationship("City", 
              secondary="join(Street,City,Street.city==City.id)",
              primaryjoin="and_(Adress.street==Street.id)",
              secondaryjoin="City.id == Street.city") 

   def __unicode__(self):
      return self.name

Now i want to add admin model to add an adress for a family. but first i want to select the city and based on that choice i want to filter the available streets
How can this be done?

admin.add_view(sqla.ModelView(Adress, db.session)) 

This also shows the City but the street column is not filtered when a city is chosen.

[pawl]

Is this helpful?: https://github.com/mrjoes/flask-admin/issues/219

Here's the code example: https://gist.github.com/mrjoes/5521548

[MaasErwin]

that helped me on my way, yes thank you.

But I don't know how to get the selected value from the city_ref

I create a ModelView class like this

class AdressView(sqla.Modelview):

    #hook form creation methods
    def create_form(self):
        return self._use_filtered_street(super(AdressView, self).create_form())

    def edit_form(self, obj):
        return self._use_filtered_street(super(AdressView, self).edit_form(obj))

    # Logic
    def _use_filtered_street(self, form):
        form.spec.query_factory = self._get_street_list
        return form

    def _get_street_list(self):
        return self.session.query(Street).filter_by(city = '2').all()

the '2' in the filter_by should retrieve the city_ref value. what is the best way to do this?
thanks in andvance

Although I don't believe this will be dynamic. How can the filtering be triggerd when selecting a city?

[MaasErwin]

I also tried the following:

class AdressView(sqla.ModelView):
      def scaffold_form(self):
          form_class = super(AdressView, self).scaffold_form()
          form_class.extra = sqla.fields.QuerySelectedField(Street.city_ref)
          return form_class

I thought maybe if I could add the city_ref as extra field it will filter Street

But I can't get this to work. I get TypeError:'NoneType' object is not callable
I also tried the field InlineModelFormList() but I don't totally get what it wants

[xqliu]

I have found a solution to this by using on_form_prefill together with query_factory, here are the steps

1. In the admin definition, override the default implementation of on_form_prefill, and in that method, you can get the current object being edited, so you can define the query_factory of another field based on the current defined field, code shown below:

class ReceivingAdmin(ModelView):
        def on_form_prefill(self, form, id):

        # Get field(purchase_order_id) from the current object being edited via form._obj.purchase_order_id
        if form is not None and form._obj is not None and form._obj.purchase_order_id is not None:
            po_id = form._obj.purchase_order_id
            # Define a dynamic query factory based on current data.
            # Please notice since the po_id parameter need to be passed to the function,
            # So functools.partial is used
            form.lines.form.purchase_order_line.kwargs['query_factory'] =\
                partial(PurchaseOrderLine.header_filter, po_id)

And here is the definition of the query factory in the model:

class PurchaseOrderLine(db.Model):
    @staticmethod
    def header_filter(po_id):
        return AppInfo.get_db().session.query(PurchaseOrderLine).filter_by(purchase_order_id=po_id)

By this way, we could control which record will be shown in the purchase order line list based on parameter po_id, and the value of po_id is passed to the query factory function in on_form_prefill.

[orenyomtov]

I found a more elegant way to achieve the same goal:

form_args = {
        'street': {
            'query_factory': lambda: db.session.query(Street).filter_by(city = '2')
        }
    }

I hope it helps the next person who find this issue on Google.

[asadmahdi]

Thaksn Oren. That was helpful

[v-ken]

This doesn't work when creating a new object does it? (as opposed to editing an object)