Okey doke, here is an MCVE of your model and your query which passes.
Please modify it to look more like your actual code until you find
what is causing it to fail, which should reveal what's going on.
thanks!
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()
class PlateWell(Base):
__tablename__ = "plate_wells"
id = Column(Integer, primary_key=True)
parent_well_id = Column(Integer, ForeignKey("plate_wells.id"), index=True)
root_well_id = Column(Integer, ForeignKey("plate_wells.id"), index=True)
parent_well = relationship(
"PlateWell",
uselist=False,
remote_side=[id],
foreign_keys=[parent_well_id],
)
root_well = relationship(
"PlateWell",
uselist=False,
remote_side=[id],
foreign_keys=[root_well_id],
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
well = PlateWell(parent_well=PlateWell(), root_well=PlateWell())
s.add(well)
s.commit()
well_id = well.id
s.close()
well = s.query(PlateWell).filter(PlateWell.id == well_id).one()
assert well.parent_well is not None
assert well.root_well is not None
On Tue, Jan 15, 2019 at 2:02 PM <[email protected]> wrote:
>
> Thank you for the response! Unfortunately, that did not resolve the issue.
> I tried both removing the keyword and replacing it with options 'select' and
> 'joined' to no avail.
>
>
>
> On Monday, January 14, 2019 at 4:22:43 PM UTC-8, Mike Bayer wrote:
>>
>> On Mon, Jan 14, 2019 at 2:22 PM <[email protected]> wrote:
>> >
>> > Hello,
>> >
>> > Our data model for a structure named PlateWell in our software utilizes a
>> > singly linked-list structure with a field for a parent PlateWell. We are
>> > adding another field pointing to the root PlateWell of the link list to
>> > improve runtime of certain important operations, however the relationships
>> > are not loading. Here is the abridged model.
>> >
>> > class PlateWell(db.Model):
>> > __tablename__ = 'plate_wells'
>> >
>> > id = db.Column(db.Integer, primary_key=True)
>> > parent_well_id = db.Column(db.Integer,
>> > db.ForeignKey('plate_wells.id'), index=True)
>> > root_well_id = db.Column(db.Integer, db.ForeignKey('plate_wells.id'),
>> > index=True)
>> >
>> > parent_well = db.relationship(
>> > 'PlateWell', uselist=False, lazy='noload', remote_side=[id],
>> > foreign_keys=[parent_well_id])
>> > root_well = db.relationship(
>> > 'PlateWell', uselist=False, lazy='noload', remote_side=[id],
>> > foreign_keys=[root_well_id])
>> >
>> > At the root entry, the root_well_id is equal to id and parent_well_id is
>> > set to None.
>> >
>> > Prior to adding the root relationship, the model loaded the parent_well
>> > relationship without the foreign_keys kwarg. This relationship was fully
>> > functional.
>> >
>> > Now, any time a plate well is loaded via a query like
>> >
>> > well = PlateWell.query.filter(PlateWell.id == well.plate_well_id).one()
>> >
>> > both parent_well and root_well are set to None, despite both foreign key
>> > id fields containing an id that does relate to another entry. We've
>> > attempted adding explicit primaryjoin kwargs, expiring the session to
>> > clear the cache, but neither seems to work. Is there a trick to loading
>> > multiple self-referential relationships or some other change we can make
>> > to properly load these relationships?
>>
>> I haven't tried your model but the "lazy='noload'" doesn't seem like
>> what you want to be doing there, that loader option explicitly
>> indicates you want nothing to load so that would likely be where
>> "None" is coming from. Removing that option will likely allow it to
>> work.
>>
>>
>>
>> >
>> > 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 [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.
>
>
> 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 [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.
