Conditionally ordered nested relationships

[Daniel Cardin]

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?

[Mike Bayer]

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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Daniel Cardin]

So "ParentFeature.shows" is there for the case where I query `pg.query(Parent)`, I want it to be ordered ascending.

The only reason i have "ParentFeature.unordered_shows" is because if I try to apply an `order_by` to `Thing.name` in a query (like my query example), it emits "ORDER BY thing.name DESC, thing.name" (e.g. the order_by on the relationship is still applied, despite my sort). If I can override that, i have no need for both relationships

To answer your other question. I only have the join+joinedloads from following https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=joinedload#the-zen-of-joined-eager-loading.

The query I included was only to show my attempt at writing a query which:

1. query ParentFeature
2. only get the 1 ParentFeature with a specific Parent
3. apply the sort to ParentFeature.things, so the "result.one().things" is sorted ascending/descending

[Mike Bayer]

On Fri, Jun 8, 2018 at 2:08 PM, Daniel Cardin <d.d.c...@gmail.com> wrote:
> So "ParentFeature.shows" is there for the case where I query
> `pg.query(Parent)`, I want it to be ordered ascending.
>
> The only reason i have "ParentFeature.unordered_shows" is because if I try

noting that, I don't know what unordered_shows is, your mapping has no
such attribute

> to apply an `order_by` to `Thing.name` in a query (like my query example),
> it emits "ORDER BY thing.name DESC, thing.name" (e.g. the order_by on the
> relationship is still applied, despite my sort). If I can override that, i
> have no need for both relationships

I see an order_by on ParentFeature.things, which doesn't seem to be in
your query, but if it were join eager loaded, it would be aliased, so
I don't see the problem nor have you showed anything that would do
this so I can't help without that

>
> To answer your other question. I only have the join+joinedloads from
> following
> https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=joinedload#the-zen-of-joined-eager-loading.

I know why one would use joinedload() in general. The question was,
if they are removed, do you still have the problem?

>
> The query I included was only to show my attempt at writing a query which:
>
> query ParentFeature
> only get the 1 ParentFeature with a specific Parent
> apply the sort to ParentFeature.things, so the "result.one().things" is
> sorted ascending/descending

ParentFeature.things is not in the query you illustrated so I'm not following

guidance on MCVE: http://stackoverflow.com/help/mcve

[Daniel Cardin]

Aha i didn't realize I had a mismatch of relationship names

Below I included a full example that fails an assert where I'm having the issue.

• The only reason I have "unordered_things" and "things" relationships is because I wasn't sure how to get it to only include the query's sort
• The only "requirement" I have of the 2nd query is that is the same for both asc and desc sorting. Everything else was just my best attempt at getting it to work
  

from sqlalchemy import asc, Column, create_engine, desc, ForeignKey, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload, relationship, sessionmaker

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(types.Integer, primary_key=True, autoincrement=True)

    parent_feature = relationship('ParentFeature', lazy='joined', uselist=False)

class Thing(Base):
    __tablename__ = 'thing'
    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'), nullable=False, index=True)

    parent = relationship('Parent', uselist=False)

    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'), primary_key=True)
    thing_id = Column(ForeignKey('thing.id'), primary_key=True)

engine = create_engine('sqlite:///')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

models = [
    Parent(id=1),
    Parent(id=2),
    Thing(id=1, name='aaaa'),
    Thing(id=2, name='bbbb'),
    Thing(id=3, name='cccc'),
    ParentFeature(id=1, parent_id=1),
    ParentFeature(id=2, parent_id=2),
    ParentFeatureThingPivot(parent_feature_id=2, thing_id=1),
    ParentFeatureThingPivot(parent_feature_id=2, thing_id=2),
    ParentFeatureThingPivot(parent_feature_id=2, thing_id=3),
]
for model in models:
    session.add(model)
session.flush()

parent = session.query(Parent).all()[1]
things = [thing.name for thing in parent.parent_feature.things]
things_check = ['aaaa', 'bbbb', 'cccc']
print('{} == {}'.format(things, things_check))
assert things == things_check

for direction in (asc, desc):
    things_check = ['aaaa', 'bbbb', 'cccc']
    if direction == desc:
        things_check = list(reversed(things_check))

    parent_feature = (
        session.query(ParentFeature)
        .join(ParentFeature.parent, ParentFeature.unordered_things)
        .options(
            joinedload(ParentFeature.parent),
            joinedload(ParentFeature.unordered_things),
        )
        .filter(Parent.id == 2)
        .order_by(asc(Thing.name))
        .one()
    )
    things = [thing.name for thing in parent_feature.things]
    print('{} == {}'.format(things, things_check))
    assert things == things_check

[Mike Bayer]

On Fri, Jun 8, 2018 at 7:13 PM, Daniel Cardin <d.d.c...@gmail.com> wrote:
> Aha i didn't realize I had a mismatch of relationship names
>
> Below I included a full example that fails an assert where I'm having the
> issue.
>
> The only reason I have "unordered_things" and "things" relationships is
> because I wasn't sure how to get it to only include the query's sort
> The only "requirement" I have of the 2nd query is that is the same for both
> asc and desc sorting. Everything else was just my best attempt at getting it
> to work

OK....this is much more helpful, and I can make this do whatever you
want, but you have this:

for direction in (asc, desc):
things_check = ['aaaa', 'bbbb', 'cccc']
if direction == desc:
things_check = list(reversed(things_check))

but then you have this:

.order_by(asc(Thing.name))

and then this:

assert things == things_check

the fact that you are changing the order of things_check seems to
imply you want the order of parent_feature.things to change also....
but you're not changing the query.

It seems like you mean to have this?

.order_by(direction(Thing.name))


in which case you would use contains_eager(), also you need to expire
the session for each run so the collections are re-loaded (or use
populate_existing()):

for direction in (asc, desc):
things_check = ['aaaa', 'bbbb', 'cccc']
if direction == desc:
things_check = list(reversed(things_check))

session.expire_all()

print("-----------------------------------------------")
parent_feature = (
session.query(ParentFeature)
.join(ParentFeature.parent, ParentFeature.things)
.options(
contains_eager(ParentFeature.parent),
contains_eager(ParentFeature.things),
)
.filter(Parent.id == 2)
.order_by(direction(Thing.name))
.one()
)
print("-----------------------------------------------")

[Daniel Cardin]

the fact that you are changing the order of things_check seems to
imply you want the order of parent_feature.things to change also....
but you're not changing the query.

 

Ugh, woops again. Yes that's exactly what i meant!

in which case you would use contains_eager()

Wow okay, that exactly works.

So I guess i didnt/dont understand understand at all what contains_eager is meant to do even after reading the documentation. The join beforehand is what generates the sql which ultimately allows contains_eager to load the relationship. But how is that different from how it would have worked with a normal joinedload (i mean i can see the difference in the generated sql, but for me to know that I should have been using contains_eager in the first place)?

Also, (not that I actually want to in this case) but would it be possible to do the same thing with a different relationship loading mechanishm? Like suppose this was typically a `selectin` loaded relationship with a default order_by on it. Is it possible to have it do the same thing it would have done for selectin anyway, order *that* query configurably, and get the same end-result?

Anyways, thanks so much. I was banging my head against this for far too long!

[Mike Bayer]

On Mon, Jun 11, 2018 at 8:17 AM, Daniel Cardin <d.d.c...@gmail.com> wrote:
>> the fact that you are changing the order of things_check seems to
>> imply you want the order of parent_feature.things to change also....
>> but you're not changing the query.
>
>
> Ugh, woops again. Yes that's exactly what i meant!
>
>> in which case you would use contains_eager()
>
>
> Wow okay, that exactly works.
>
> So I guess i didnt/dont understand understand at all what contains_eager is
> meant to do even after reading the documentation. The join beforehand is
> what generates the sql which ultimately allows contains_eager to load the
> relationship. But how is that different from how it would have worked with a
> normal joinedload (i mean i can see the difference in the generated sql, but
> for me to know that I should have been using contains_eager in the first
> place)?

joinedload:

- generates a JOIN against the target entity, after applying an alias
to it that ensures nothing else in the query will refer towards it
- adds columns, derived from that anonymously aliased table, to the
columns clause of the SELECT which the ORM knows how to link to the
entity collection to be loaded

contains_eager:

- adds columns, derived from the base entity or a user-defined alias
that it is assumed the user has manually placed into the query
already, to the columns clause of the SELECT which the ORM knows how
to link to the entity collection to be loaded


so the rule is, if are not calling query.join(), use joinedload(). if
you *are* calling query.join(), and that's how you want the
relationship()s to be loaded, use contains_eager().

>
> Also, (not that I actually want to in this case) but would it be possible to
> do the same thing with a different relationship loading mechanishm? Like
> suppose this was typically a `selectin` loaded relationship with a default
> order_by on it. Is it possible to have it do the same thing it would have
> done for selectin anyway, order *that* query configurably, and get the same
> end-result?
>
> Anyways, thanks so much. I was banging my head against this for far too
> long!
>

[Mike Bayer]

On Mon, Jun 11, 2018 at 8:17 AM, Daniel Cardin <d.d.c...@gmail.com> wrote:
>
> Also, (not that I actually want to in this case) but would it be possible to
> do the same thing with a different relationship loading mechanishm? Like
> suppose this was typically a `selectin` loaded relationship with a default
> order_by on it. Is it possible to have it do the same thing it would have
> done for selectin anyway, order *that* query configurably, and get the same
> end-result?
>

you can apply query.join() + contains_eager() to those relationships
as well but that will override the "selectin" loader from being used.
that is, no, there's no way to make "selectin" itself run with a
different join unless you make a separate relationship.

> Anyways, thanks so much. I was banging my head against this for far too
> long!
>