turn filtered query with subquery into relationship

[dan]

Dear List,

Hoping for help with following unanswered stackoverflow question. If the approach is not the best, that feedback is good as well.

In the case below is it better to just not use relationships and just use the method with the query?

Thanks

--------------------------------------------------------------------

In the code below I want to replace *all_holdings* in Account with a property called holdings that returns the *desired_holdings* (which are the holdings representing the latest known quantity which can change over time). I'm having trouble figuring out how to construct the call to relationship.

In addition any comments on the appropriateness of the pattern (keeping historic data in a single table and using a max date subquery to get most recent), better alternatives, improvements on the query appreciated.

from sqlalchemy import Column, Integer, String, Date, DateTime, REAL, ForeignKey, func
from sqlalchemy.orm import relationship, aliased
from sqlalchemy.sql.operators import and_, eq
from sqlalchemy.ext.declarative import declarative_base
from db import session
import datetime
import string

Base = declarative_base()

class MySQLSettings(object):
    __table_args__ = {'mysql_engine':'InnoDB'}

class Account(MySQLSettings, Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    all_holdings = relationship('Holding', backref='account')

    def desired_holdings(self):
        max_date_subq = session.query(Holding.account_id.label('account_id'),
                                      Holding.stock_id.label('stock_id'),
                                      func.max(Holding.as_of).label('max_as_of')). \
                                      group_by(Holding.account_id, Holding.stock_id).subquery()

        desired_query = session.query(Holding).join(Account,
                                                    Account.id==account.id).join(max_date_subq).\
                                                    filter(max_date_subq.c.account_id==account.id).\
                                                    filter(Holding.as_of==max_date_subq.c.max_as_of).\
                                                    filter(Holding.account_id==max_date_subq.c.account_id).\
                                                    filter(Holding.stock_id==max_date_subq.c.stock_id)

        return desired_query.all()

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

class Stock(MySQLSettings, Base):
    __tablename__ = 'stock'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

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

class Holding(MySQLSettings, Base):
    __tablename__ = 'holding'
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey('account.id'), nullable=False)
    stock_id = Column(Integer, ForeignKey('stock.id'), nullable=False)
    quantity = Column(REAL)
    as_of = Column(Date)

    stock = relationship('Stock')

    def __str__(self):
        return "Holding(%f, '%s' '%s')"%(self.quantity, self.stock.name, str(self.as_of))

    def __init__(self, account, stock, quantity, as_of):
        self.account_id = account.id
        self.stock_id = stock.id
        self.quantity = quantity
        self.as_of = as_of

if __name__ == "__main__":
    ibm = Stock('ibm')
    session.add(ibm)
    account = Account('a')
    session.add(account)
    session.flush()
    session.add_all([ Holding(account, ibm, 100, datetime.date(2001, 1, 1)),
                      Holding(account, ibm, 200, datetime.date(2001, 1, 3)),
                      Holding(account, ibm, 300, datetime.date(2001, 1, 5)) ])
    session.commit()

    print "All holdings by relation:\n\t", \
        string.join([ str(h) for h in account.all_holdings ], "\n\t")

    print "Desired holdings query:\n\t", \
        string.join([ str(h) for h in account.desired_holdings() ], "\n\t")

The results when run are:

All holdings by relation:
    Holding(100.000000, 'ibm' '2001-01-01')
    Holding(200.000000, 'ibm' '2001-01-03')
    Holding(300.000000, 'ibm' '2001-01-05')
Desired holdings query:
    Holding(300.000000, 'ibm' '2001-01-05')

[Michael Bayer]

On Apr 30, 2012, at 10:54 AM, dan wrote:

Dear List,

Hoping for help with following unanswered stackoverflow question. If the approach is not the best, that feedback is good as well.

In the case below is it better to just not use relationships and just use the method with the query?

Thanks

--------------------------------------------------------------------

In the code below I want to replace *all_holdings* in Account with a property called holdings that returns the *desired_holdings* (which are the holdings representing the latest known quantity which can change over time). I'm having trouble figuring out how to construct the call to relationship.

The desired_holdings() query is pretty complicated and I'm not seeing a win by trying to get relationship() to do it.  relationship() is oriented towards maintaining the persistence between two classes, not as much a reporting technique (and anything with max()/group_by in it is referring to reporting).

I would stick @property on top of desired_holdings, use object_session(self) to get at "session", and be done.

This use case is described at http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#building-query-enabled-properties .

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/KKB3-3r5kSAJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.