Auto filter a relationship?

[Shawn Adams]

I've got the following models and I'd like to automatically filter out  ReservationPackages that contain a "soft deleted" package.  A package is "soft deleted" if deleted != null. I've been struggling with this for a few days now. The problem goes away if I don't use the proxy but then I cannot model duplicate packages on my reservation.

class ReservationPackage(db.Model):
    """
    We must use the association pattern to support duplicate packages on a
    reservation
    """
    __tablename__ = "reservation_packages"

    id = db.Column(db.Integer, primary_key=True)
    reservation_id = db.Column(db.Integer, db.ForeignKey('reservations.id'))
    package_id = db.Column(db.Integer, db.ForeignKey('packages.id'))

    package = db.relationship("Package")

    def __init__(self, package):
        self.package = package


class Reservation(db.Model):
    """
    Reservation represents a contract to rent something to a User
    """
    __tablename__ = "reservations"

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

    # We unfortunately need to use an association like this to
    # support the ability to have duplicate packages.
    _package_rel = db.relationship(ReservationPackage, lazy="joined")

    packages = association_proxy('_package_rel', 'package')


class Package(db.Model):

  __tablename__ = "packages"

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

I could create a read only property that contains something like

return object_session(self).query(ReservationPackage) \
    .filter_by(reservation_id=self.id) \
    .join("package"). \
    filter(text("packages.deleted != null")).all()

but I'd really like to be able to manipulate the packages list through the packages property.

thanks,

-shawn

[Jonathan Vanasco]

    packages = sa.orm.relationship("Package", primaryjoin="and_(ReservationPackage.package_id==Package.id, Package.deleted IS NOT NULL)")

[Jonathan Vanasco]

I hit send early.  That form should/will work for reading - i use it heavily.  I'm not sure how/if it will interact with the collections management system (backref/backpopulates, etc).  

[Shawn Adams]

Jonathan, Are you suggesting I use this instead of the association_proxy on Reservation? Doing so will not let me have duplicate packages in the collections list. sqa filters them out. To get around that I had to use the association_proxy.

On Thu, Sep 24, 2015 at 1:37 PM, Jonathan Vanasco <jona...@findmeon.com> wrote:

I hit send early.  That form should/will work for reading - i use it heavily.  I'm not sure how/if it will interact with the collections management system (backref/backpopulates, etc).  

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/1Hf6wTv0PfU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Your use of association_proxy seems odd to me.  That plugin is normally used to "cut out" the middleman when using a join with an association pattern and avoid a hop/remount a relationship from one object onto another.

With my understanding and use of that plugin, you're basically setting the following shortcut, and most other behaviors are a side-effect.

Reservation.packages == Reservation._packages_rel.package

Since ReservationPackage has a primary key on `id`, it should be able to handle non-unique combinations.

These 2 both jumps out to me as potential problems :

ReservationPackage-

   package - there is no backref or back-populates.

   package - should this have a uselist=False setting?  

   __init__ - this handles the package, but not the reservation ?

Reservation-

    package_rel - no backref or back-populates

In my experience, the bulk of problems with 'collections' have happened because of incomplete or improper relationships.  I'd try setting up everything with fully declared relationships (ie,  back_populates) and see how that affects the design of your relations and how the code runs.

You can pretty much expect the collections to not work properly unless you have backref/back-populates declared.  That's the biggest issue and warning sign to me -- sqlalchemy only knows half of your relationship info.

[Shawn Adams]

Reservation -> packages is just a many-to-many relationship with the caveat that a reservation can have the same package more than once. Sqlalchemy will automatically dedube the packages collections which is not what I want. In order to avoid this I had to use reservation_packages as an association table instead. The proxy is needed to hide this detail. So those relationships work fine.

Everything is done through reservation.packages so I don't need all those extra backrefs. ReservationPackage.__init__ must accept a package object so that appending to the packages collection works.

--