Hello,
First of all, I'll apologize if this is a really basic question. I've not been
using SQLAlchemy for long, and I've only very recently picked up Python. Even
though I've looked everywhere I can think of for an answer to this question,
I'm almost certain that it is not a difficult one. I'm happy to read through
any documentation you can point me to, but I've not been able to see what might
be relevant to this particular question.
That said, here goes. Here's the relevant bit of code:
-- BEGIN CODE --
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
uid = Column(Integer, unique=True)
def __init__(self, uid):
self.uid = uid
def __repr__(self):
return("<User('uid: %d')>") % (self.uid)
class Filesystem(Base):
__tablename__ = 'filesystem'
id = Column(Integer, primary_key=True)
filesystem = Column(String(255))
mountpoint = Column(String(255))
def __init__(self, filesystem, mountpoint):
self.filesystem = filesystem
self.mountpoint = mountpoint
def __repr__(self):
return("<Filesystem('%s', '%s')>") % (self.filesystem, self.mountpoint)
class Usage(Base):
__tablename__ = 'usage_data'
id = Column(Integer, primary_key=True)
fs_id = Column(None, ForeignKey('filesystem.id'))
user_id = Column(None, ForeignKey('user.id'))
datetime = Column(DateTime)
inodes = Column(MSBigInteger, nullable=False)
kbytes = Column(MSBigInteger, nullable=False)
user = relation(User, backref=backref('usage_data', order_by=datetime))
fs = relation(Filesystem, backref=backref('usage_data', order_by=datetime))
def __init__(self, user, fs, datetime, inodes, kbytes):
self.user = user
self.fs = fs
self.datetime = datetime
self.inodes = inodes
self.kbytes = kbytes
def __repr__(self):
return("<Usage('%s', '%s', '%s', %d:%d KB)>") % (self.user, self.fs,
self.datetime, self.inodes, self.kbytes)
-- END CODE --
So you can kind of see what it does. This bit is a rudimentary filesystem space
tracking application. You have a list of users in the 'user' table, a list of
filesystems in the 'filesystem' table, and then you have an application that is
periodically inserting records into the 'usage_data' table. Each record
includes a user reference, a filesystem reference, and some data.
The backrefs from Usage objects work perfectly. When I have a User object, say
"user_obj", I can access user_obj.usage_data and get a datetime-ordered list of
all Usage objects associated with this particular user.
What I want is an easy way to access a user's n most recent Usage objects for
each filesystem. (For the purpose of this e-mail, we can take n=1.) It would be
easy enough for me to just take the last m records in the user_obj.usage_data
list (where m is the number of filesystems for which this user has records),
but that's not really what I want. One filesystem might be storing records
every hour, and another might be storing them every day. In that case, it would
be hard to know how many records I would need to take from user_obj.usage_data
to have the most recent record from each filesystem.
It wouldn't be hard to actually use a session object to build a query for this,
but I'd really like to have this all taken care of in the
objects/maps/relations/whatever themselves. I'd like to have some attribute in
the User object that's like user_obj.usage_data, but instead gives me a list of
only the most recent Usage object from each filesystem, so I'd see something
like:
>>> user_obj.most_recent_usage
[<Usage('user', 'fs1', 'some-date', foo:bar)>,
<Usage('user', 'fs2', 'some-other-date', f0o:bAr)>,
<Usage('user', 'fs3', 'some-third-date', fo0:b4r)>]
I hope I was clear in my description. If I've left anything out, I'll be happy
to clarify.
Thanks!!
David
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
