Subclass not respecting base class' relationship eager loading options

[Carson Ip]

Hi Mike,

I have having issues letting a subclass load a parent's relationship that configured lazy="joined".

Let's say Engineer is a subclass of Person, and Person stores "company_id" and has a relationship called "company" that is lazy="joined".

Although I don't have a minimal reproducible example now, but in my setup, let's say:

engineer = session.query(Engineer).get(1)  # emits 1 query
engineer.company  # emits another query

I suspect that the subclass does not respect parent class' relationship lazy/eager loading options.

Also I bump into this issue when identity map remembers the id's polymorphic type. Then when I query on the base class, it emits the the subclass query and does not load the parent class' relationship with eager load options, which is the problem I mentioned.

engineer = session.query(Engineer).get(1)
session.commit()
person = session.query(Person).get(1)  # emits 1 query
person.company  # emits another query

Is this by design? If so, is there any way to make it respect parent class' relationship "lazy" option?

I am not 100% certain this is reproducible in a simple example. There may be problems in my setup that would cause this. Please let me know if this is the case.

Thanks in advance.

[Carson Ip]

Also, there are some restrictions in my setup such that I am not able to use eager load like session.query(Engineer).options(joinedload(...)).get(). I retrieve the engineer object using another relationship.

[Mike Bayer]

On Thu, Nov 28, 2019, at 4:45 AM, Carson Ip wrote:

Hi Mike,

I have having issues letting a subclass load a parent's relationship that configured lazy="joined".

Let's say Engineer is a subclass of Person, and Person stores "company_id" and has a relationship called "company" that is lazy="joined".

Although I don't have a minimal reproducible example now, but in my setup, let's say:

engineer = session.query(Engineer).get(1)  # emits 1 query
engineer.company  # emits another query

I suspect that the subclass does not respect parent class' relationship lazy/eager loading options.

nope, it does, no issue there so you need an MCVE

Also I bump into this issue when identity map remembers the id's polymorphic type. Then when I query on the base class, it emits the the subclass query and does not load the parent class' relationship with eager load options, which is the problem I mentioned.

engineer = session.query(Engineer).get(1)
session.commit()
person = session.query(Person).get(1)  # emits 1 query
person.company  # emits another query

Is this by design?

nope, those are the same object, if it is loading two different objects for the same id, need an MCVE, sounds like your polymorphic_discriminator is not set correctly.   the query for .company is expected there because it is expired by the commit() previously.   the refresh which occurs on the get() does not use a joined eager load until 1.4.

If so, is there any way to make it respect parent class' relationship "lazy" option?

it does, need an MCVE     (see the theme?  :) )

I am not 100% certain this is reproducible in a simple example. There may be problems in my setup that would cause this. Please let me know if this is the case.

so then you start with a simple example and then slowly modify your real program one line at a time until it looks like your simple example, each time testing to see what causes the problem.

here's all your test cases

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import ForeignKey

from sqlalchemy import Integer

from sqlalchemy import String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship

from sqlalchemy.orm import Session

Base = declarative_base()

class Company(Base):

    __tablename__ = "company"

    id = Column(Integer, primary_key=True)

    employees = relationship("Person", back_populates="company")

class Person(Base):

    __tablename__ = "people"

    id = Column(Integer, primary_key=True)

    discriminator = Column("type", String(50))

    company_id = Column(ForeignKey("company.id"))

    __mapper_args__ = {

        "polymorphic_on": discriminator,

        "polymorphic_identity": "person",

    }

    company = relationship(

        "Company", lazy="joined", back_populates="employees"

    )

class Engineer(Person):

    __tablename__ = 'engineer'

    id = Column(Integer, ForeignKey('people.id'), primary_key=True)

    primary_language = Column(String(50))

    __mapper_args__ = {

        "polymorphic_identity": "engineer",

    }

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)

s.add(Company(employees=[Engineer(primary_language="java")]))

s.commit()

s.close()

e1 = s.query(Engineer).get(1)

# close so we assert no sql in the next step

s.close()

# no query

assert e1.company is not None

engineer = s.query(Engineer).get(1)

s.commit()

person = s.query(Person).get(1) # emits 1 query

# same object

assert engineer is person

# s.close()  # you can do this if you are on 1.4.  in 1.3, the .company attr is not loaded

# emits a query in 1.3 because the attribute was expired by the commit

person.company

Thanks in advance.

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7960faaf-50a6-4edf-b35a-4b7eda89f7d0%40googlegroups.com.