Hello there,
In a mapped object, is there any way to map a scalar attribute to an
arbitrary selectable/subquery?
Jonathan Ellis demonstrated how to do that for relations on this page:
http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
I'd like to do that for scalars.
I've thought about using a property returning a query, but this still
generates one query per user (my mapped object) and I need to do
everything in one pass.
See attached file for an example of what I'd like to do.
I've the feeling it might already be possible but I don't see how. If
it's not possible yet, do you have any pointer how I could implement
that?
--
Gaëtan de Menten
http://openhex.org
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16)),
)
tags_table = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey("users.id")),
Column('score1', Float),
Column('score2', Float)
)
metadata.connect('sqlite:///')
metadata.create_all()
class User(object):
def __init__(self, name):
self.name = name
@property
def score_v1(self):
return sum(tag.score for tag in self.tags)
@property
def score_v2(self):
tags = object_session(self).query(Tag).filter_by(user=self)
return tags.sum(tags_table.c.score1 * tags_table.c.score2)
class Tag(object):
def __init__(self, score1, score2):
self.score1 = score1
self.score2 = score2
@property
def score(self):
return self.score1 * self.score2
mapper(Tag, tags_table)
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user'),
})
u1 = User('joe')
t1 = Tag(5.0, 3.0)
t2 = Tag(55.0, 1.0)
u1.tags = [t1, t2]
u2 = User('bar')
t3 = Tag(5.0, 4.0)
t4 = Tag(50.0, 1.0)
t5 = Tag(15.0, 2.0)
u2.tags = [t3, t4, t5]
session = create_session()
session.save(u1)
session.save(u2)
session.flush()
session.clear()
metadata.engine.echo = True
print "---v1---"
# generates 1 query per user and loads tag objects
users = session.query(User).select()
for user in users:
print user.name, user.score_v1
session.clear()
print "---v2---"
# generates 1 query per user but doesn't load tag objects
users = session.query(User).select()
for user in users:
print user.name, user.score_v2
# I'd like to do the whole thing in 1 query for all users (and without loading
# tag objects)
#mapper(tag, tags_table, properties={
# 'score': special_column(
# tags_table.c.score1 * tags_table.c.score2)})
#ideally, reuse subtotal
#score_by_user = select([func.sum(Tag.c.subtotal).label('score')],
#score_by_user = select([func.sum(tags_table.c.score1 *
# tags_table.c.score2).label('score')],
# group_by=[tags_table.c.user_id])
#mapper(User, users_table, properties={
# 'tags': relation(tag, backref='user'),
# 'score': special_column(tags_value_by_user),
#})
# I also need to be able to defer/undefer those special columns
