Define a model as a subset of table data

[katie louise]

SQLAlchemy newbie here.

I'm trying to define a model (subclassed by another model) that represents a subset of table data of the parent model. Specifically, I want the subclass to map the most recent row for a given ID.

For example, suppose I have the following model:

class AddressHistory(Base):
    __table__ = 'address_table'

    date = Column(Date, index=True, nullable=False)
    id = Column(BigInteger, primary_key=True)
    street = Column(String(2000))
    city = Column(String(2000))
    state = Column(String(2000))
    zip = Column(Integer)

What I want to do is define a subclass of this model which represents the most recent address record:

class MostRecentAddress(Address):
    “””
    Represents a row in AddressHistory with the most recent date for a given id.
    ”””

Is there some sort of subquery I can pass to the mapper_args ? Maybe a polymorphic_identity? Or could I create a separate view and have the subclass read from that? 

Thanks!

[Michael Bayer]

Usually a query for “most recent X” involves grouping on a column that’s not the primary key.   But if you want the most recent address across all primary keys, that’s exactly one row.   I’m not really sure what it means to “map” to exactly one row.  

If you’re looking for some pattern that gives you some fixed criteria, just use a hybrid:

class AddressHistory(Base):

    # …

    @hybrid_property

    def max_address(cls):

        return cls.id == select([AddressHistory.id]).group_by(AddressHistory.id).having(func.max(AddressHistory.date) == AddressHistory.date)

max_address = session.query(AddressHistory).filter(AddressHistory.max_address).first()

Thanks!

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, 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]

Two things that won't answer your question:

1. I've had better luck using Mixins instead of inherited subclasses.  The main reason is maintenance -- when you end up looking at AddressHistory in 2 years, you might not remember that MostRecentAddress inherits from it.

2. Your sql doesn't make sense for the description:

    id = Column(BigInteger, primary_key=True)

  Represents a row in AddressHistory with the most recent date for a given id.

if `id` is a pkey, it must be unique. 




class _AddressMixin(object):

    date = Column(Date, index=True, nullable=False)

    id = Column(BigInteger, primary_key=True)

    street = Column(String(2000))

    city = Column(String(2000))

    state = Column(String(2000))

    zip = Column(Integer)

class AddressHistory(Base, _AddressMixin):

__table__ = 'address_table'

class MostRecentAddress(Base, _AddressMixin):

__table__ = 'address_table'