On Tue, May 1, 2018 at 4:34 PM, Gerald Thibault <[email protected]> wrote:
> 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).
that requirement is very disturbing to me, but I'm hoping I don't have
to get into why that is.
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?
just use contains_eager(), with a new relationship that only loads for
this operation if you'd like to make it a scalar, it's in your imports
so I guess you tried:
class Theme(Base):
__tablename__ = 'themes'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
user_themes = relationship('UserTheme')
user_theme = relationship('UserTheme', lazy=None, viewonly=True,
uselist=False)
s.query(Theme).select_from(UserTheme).join(UserTheme.theme).options(contains_eager(Theme.user_theme)).filter(...).first()
>
> 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.
--
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.
