On Fri, Jun 8, 2018 at 11:40 AM, Daniel Cardin <d.d.car...@gmail.com> wrote:
> I'm attempting to a sort of complex relationship filter/sort operation. I
> need to filter results by the id of a parent relationship, and then sort a
> nested relationship by one of its attributes
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(types.Integer, primary_key=True, autoincrement=True)
> name = Column(types.Unicode, nullable=False)
>
> class ParentFeature(Base):
> __tablename__ = 'parent_feature'
> id = Column(types.Integer, primary_key=True, autoincrement=True)
> parent_id = Column(ForeignKey('parent.id', ondelete='CASCADE'),
> nullable=False, index=True)
>
> parent = relationship('Parent')
> unordered_things = relationship('Thing',
> secondary='parent_feature_thing')
> things = relationship('Thing', secondary='parent_feature_thing',
> order_by=Thing.name, viewonly=True)
>
> class ParentFeatureThingPivot(Base):
> __tablename__ = 'parent_feature_thing'
> parent_feature_id = Column(ForeignKey('parent_feature.id',
> ondelete='CASCADE'), primary_key=True)
> thing_id = Column(ForeignKey('thing_id.id', ondelete='CASCADE'),
> primary_key=True)
>
> class Thing(Base):
> __tablename__ = 'thing'
> id = Column(types.Integer, primary_key=True, autoincrement=True)
> name = Column(types.Unicode, nullable=False)
>
> Ideally on ParentFeature I would only need "things" and not
> "unordered_things", but sometimes I query
> directly against Parent, in which case it should always order_by ascending.
> Anyways that works.
>
> The actual issue, is that sometimes I need to be able to conditionally order
> "things" on queries against
> ParentFeature ascending or descending, and apparently(?) I can't use the
> "things" relationship because
> it is undconditionally applied to uses of that relationship
>
> The query that I think should be working, but is not is like this:
>
> direction = desc # or asc
> result = (
> pg.query(ParentFeature)
> .join(ParentFeature.parent, ParentFeature.unordered_shows)
> .options(
> joinedload(ParentFeature.parent),
> joinedload(ParentFeature.unordered_shows),
> )
> .filter(Parent.id == 3)
> .order_by(direction(Thing.name))
> )
>
>
> except it still orders the final query ascending (or unordered? but
> definitely not descending)
>
> any ideas?
I can't quite follow this because you haven't listed what
"ParentFeature.unordered_shows" is. Also, are you trying to sort the
items inside the joinedloaded relationships, or just the overall
result? are the joinedload() options important here at all or can
they be removed to illustrate the problem?
need more specifics and fewer extraneous details, e.g. MCVE thanks!
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
