On Fri, Oct 26, 2018 at 6:03 PM <phil.nac...@freenome.com> wrote:
>
> That didn't fix it.
>
> Why should noload not be used? We use it quite often to have more granular
> control over which tables get joined. Otherwise, some queries can become
> unnecessarily slow
attributes don't load unless you access them. if you access a
"noload", you are getting meaningless state. Use the "raiseload"
strategy instead so that you know you aren't accessing meaningless
state. "noload" should be deprecated.
your example doesn't have enough detail for me to know what it does
and what you'd expect so please provide a complete and succinct MCVE
case.
>
> On Friday, October 26, 2018 at 11:45:29 AM UTC-7, Mike Bayer wrote:
>>
>> please remove those "noload" options, which really should never be
>> used, and see if that fixes your problem
>> On Fri, Oct 26, 2018 at 2:20 PM <phil....@freenome.com> wrote:
>> >
>> > I have the following three models (with other columns omitted):
>> >
>> >
>> > class ContainerSlot(db.Model):
>> > __tablename__ = 'container_slots'
>> >
>> > id = db.Column(db.Integer, primary_key=True)
>> > tube_id = db.Column(db.Integer, db.ForeignKey('tubes.id'),
>> > unique=True, index=True)
>> > container_id = db.Column(db.Integer, db.ForeignKey('containers.id'),
>> > nullable=False, index=True)
>> >
>> > tube = db.relationship('Tube', uselist=False,
>> > back_populates='container_slot', lazy='noload')
>> > container = db.relationship('Container', uselist=False,
>> > innerjoin=True, back_populates='container_slots', lazy='joined')
>> >
>> >
>> >
>> > class Container(db.Model):
>> > __tablename__ = 'containers'
>> >
>> > id = db.Column(db.Integer, primary_key=True)
>> > flag = db.Column(db.Boolean)
>> > container_slots = db.relationship('ContainerSlot', lazy='noload',
>> > back_populates='container', order_by='ContainerSlot.id')
>> >
>> >
>> > class Tube(db.Model):
>> > __tablename__ = 'tubes'
>> >
>> > id = db.Column(db.Integer, primary_key=True)
>> > flag = db.Column(db.Boolean)
>> > container_slot = db.relationship('ContainerSlot', uselist=False,
>> > back_populates='tube', lazy='joined')
>> >
>> >
>> > A container has many container slots, and a container slot has a tube. A
>> > container slot can be "empty" (it doesn't have a tube), so its tube_id is
>> > nullable.
>> >
>> > I'm trying to validate that the flag is consistent between a container and
>> > its tubes. That is, if a tube is in a container (i.e there is a container
>> > slot with its ID as tube_id), then the tube's flag must match the value of
>> > its container's flag.
>> >
>> > Ideally, the only way to update these fields without failing the
>> > validation is through endpoints which perform updates across tables in a
>> > single transaction, ensuring that the state is consistent.
>> >
>> > For example, I have an endpoint which sets the flag to false for a list of
>> > tube IDs, and removes them from their container
>> >
>> > tube_ids = args['tube_ids']
>> >
>> > load_options = [db.noload('*'),
>> > db.joinedload(Tube.container_slot).noload('*')]
>> > tubes =
>> > Tube.query.options(load_options).filter(Tube.id.in_(tube_ids)).all()
>> > container_slots = [t.container_slot for t in tubes if t.container_slot]
>> > for tube in tubes:
>> > tube.flag = False
>> >
>> > for cs in container_slots:
>> > cs.tube_id = None
>> >
>> > db.session.bulk_save_objects(tubes)
>> > db.session.bulk_save_objects(container_slots)
>> > db.session.commit()
>> >
>> > Here is a validation which is failing
>> >
>> > @event.listens_for(Tube, 'before_insert')
>> > @event.listens_for(Tube, 'before_update')
>> > def validate_tube(mapper, connection, tube):
>> > if tube.flag is None:
>> > return
>> >
>> > row = (db.session.query(Container.flag, Container.name)
>> > .join(Container.container_slots)
>> > .join(ContainerSlot.tube)
>> > .filter(Tube.id == tube.id)
>> > .one_or_none())
>> >
>> > (container_flag, name) = row if row else (None, None)
>> >
>> > if tube.flag is True and container_flag is False:
>> > raise BadRequest('...')
>> >
>> > if tube.flag is False and container_flag is True:
>> > raise BadRequest('...')
>> >
>> > I would expect that row is None here, because the tube was removed from
>> > its container slot, so the tube doesn't have a container
>> > However, row returns the container that the tube was previously in, and
>> > since the container's flag is true, the validation fails.
>> >
>> > Some things I've already tried which didn't work are:
>> >
>> > Setting the container slot's tube field to None, instead of setting
>> > tube_id to None
>> > Setting the tube's container_slot field to None, instead of setting the
>> > container slot's tube or tube_id to None
>> > Using db.session.add instead of db.session.bulk_save_objects
>> >
>> >
>> > I'm using SQLAlchemy 1.1.18 and Flask-SQLAlchemy 2.0.6
>> >
>> > Thank you!
>> >
>> > This e-mail is private and confidential and is for the addressee only. If
>> > misdirected, please notify us by telephone, confirming that it has been
>> > deleted from your system and any hard copies destroyed. You are strictly
>> > prohibited from using, printing, distributing or disseminating it or any
>> > information contained in it save to the intended recipient.
>> >
>> > --
>> > 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.
>
>
> This e-mail is private and confidential and is for the addressee only. If
> misdirected, please notify us by telephone, confirming that it has been
> deleted from your system and any hard copies destroyed. You are strictly
> prohibited from using, printing, distributing or disseminating it or any
> information contained in it save to the intended recipient.
>
> --
> 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.
