I will start by noting that we are using an api framework in which all
operations (view, create, delete, etc) are done on a primary object,
specified in the configuartion of each resource, this is important because
it prevents "the easy way" of getting the results i want, so I am looking
for another way.
Basically, there are 3 classes: call them User, Theme, and UserTheme.
UserTheme is an association table between the 2 other classes, with some
additional columns on it for extra user-specific data.
Due to our api framework, queries must return Theme objects (not UserTheme
objects). The extra data contained on the association records is needed as
well. So the basic query (which does not provide the extra data) is:
session.query(Theme)
.select_from(UserTheme)
.join(UserTheme.theme)
.filter(UserTheme.user_id==1)
I am wondering how to get the association record along with the Theme,
without returning a tuple of multiple objects that need to then be further
processed. Adding a join from the theme to the userthemes doesn't work
because multiple records are returned. Is it possible to somehow get the
UserTheme record attached to the Theme via some clever use of
query_expression/with_expression?
Ideally, the query should return a list of Theme instances, with an
arbitrary attribute which contains the association record that was used to
join against the Theme. It would also be okay to repurpose the relationship
to contain only the record that was used in the join (via some use of
contains eager or somesuch), although i would much prefer a scalar, rather
than a list containing one record.
I included a stripped-down test file to show what I'm trying to accomplish.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, scoped_session, sessionmaker,
with_expression, query_expression, contains_eager)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
user_themes = relationship('UserTheme')
def __str__(self):
return '<User %s>' % self.name
class Theme(Base):
__tablename__ = 'themes'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
user_themes = relationship('UserTheme')
def __str__(self):
return '<Theme %s>' % self.name
class UserTheme(Base):
__tablename__ = 'user_themes'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
theme_id = Column(Integer, ForeignKey(Theme.id), primary_key=True)
extra = Column(String)
user = relationship(User, back_populates='user_themes')
theme = relationship(Theme, back_populates='user_themes')
def populate(Session):
user1 = User(name='user 1')
user2 = User(name='user 2')
theme1 = Theme(name='theme1')
theme2 = Theme(name='theme2')
assoc1 = UserTheme(user=user1, theme=theme1, extra='a')
assoc2 = UserTheme(user=user1, theme=theme2, extra='b')
assoc3 = UserTheme(user=user2, theme=theme1, extra='777')
assoc4 = UserTheme(user=user2, theme=theme2, extra='888')
session.add_all([user1, user2, theme1, theme2,
assoc1, assoc2, assoc3, assoc4])
session.commit()
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
populate(Session)
query = (session.query(Theme)
.select_from(UserTheme)
.join(UserTheme.theme)
.filter(UserTheme.user_id==1)
)
for row in query:
print row, row.user_themes
