Class.somerelation.using(some_alias) ? similar to of_type but with more control

[Mike Bayer]

see the example of:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Job(Base):
    __tablename__ = "job"

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

class DataContainer(Base):
    __tablename__ = "data_container"

    id = Column(Integer, primary_key=True)
    jobs = relationship(Job)

query:

SELECT * FROM job JOIN 
datacontainer ON datacontainer.id = job.data_container_id 
WHERE NOT (EXISTS (SELECT 1 FROM job AS other_job 
WHERE other_job.data_container_id = datacontainer.id 
AND other_job.id < job.id))

using any(). The feature would allow us to say:

Job_A = aliased(Job)
DataContainer.jobs.using(Job_A).any(Job_A.id < Job.id)

naturally it would work elsewhere, though this wouldn't be the main usage:

query(DataContainer).join(DataContainer.jobs.using(Job_A))

[Mike Bayer]

step 1, is correlate_except above. this allows us more flexibility in how the any()/has() can be rendered.

[Mike Bayer]

OK here's the test for the above:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Job(Base):
    __tablename__ = "job"

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

class DataContainer(Base):
    __tablename__ = "data_container"

    id = Column(Integer, primary_key=True)
    jobs = relationship(Job)

Job_A = aliased(Job)
s = Session()
print s.query(Job).join(DataContainer.jobs).filter(DataContainer.jobs.of_type(Job_A).any(Job_A.id < Job.id))

[Mike Bayer]

then patch attached, which works with the above test, and seems to pass the "hard" tests in orm so far. but this would need a crapload more tests. note we're just expanding out of_type() itself. we should also see if a with_polymorphic() can go in there.

[Mike Bayer]

it passes all ORM tests, wow

[Mike Bayer]

join seems to just work so far. this is weird huh. these all seem to do the right thing. let me jinx this totally, can it be that the aliasing architecture is finally approaching enough consistency that new usages just work with almost no changes ?

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Job(Base):
    __tablename__ = "job"

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

class SubJob(Job):
    __tablename__ = 'subjob'
    id = Column(Integer, ForeignKey('job.id'), primary_key=True)

class DataContainer(Base):
    __tablename__ = "data_container"

    id = Column(Integer, primary_key=True)
    jobs = relationship(Job)

s = Session()

Job_A = aliased(Job)
Job_P = with_polymorphic(Job, SubJob, aliased=True)

print s.query(Job).join(DataContainer.jobs).filter(DataContainer.jobs.of_type(Job_A).any(Job_A.id < Job.id)), "\n"

print s.query(Job).join(DataContainer.jobs).filter(DataContainer.jobs.of_type(Job_P).any(Job_P.id < Job.id)), "\n"

print s.query(DataContainer).join(DataContainer.jobs.of_type(Job_A)), "\n"

print s.query(DataContainer).join(DataContainer.jobs.of_type(with_polymorphic(Job, SubJob))), "\n"

print s.query(DataContainer).join(DataContainer.jobs.of_type(Job_P)), "\n"
print s.query(DataContainer).join(with_polymorphic(Job, SubJob, aliased=True), DataContainer.jobs), "\n"

[Mike Bayer]

bb repo at https://bitbucket.org/zzzeek/sqla_2438

[Mike Bayer]

see #1106, getting at least contains_eager on board.

[Mike Bayer]

more tests needed, this is #1106

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)

s.add_all([   DataContainer(jobs=[
        SubJob(attr="s1"),
        SubJob(attr="s2")
    ](
))
])
s.commit()

Job_P = with_polymorphic(Job, SubJob, aliased=True)

for dc in s.query(DataContainer).\
            join(DataContainer.jobs.of_type(Job_P)).\
                options(contains_eager(DataContainer.jobs.of_type(Job_P))):
    for job in dc.jobs:
        print job.attr
s.close()

for dc in s.query(DataContainer).\
                options(joinedload(DataContainer.jobs.of_type(Job_P))):
    for job in dc.jobs:
        print job.attr

[Mike Bayer]

3dd536ac06808adcf9c10707dbf2ebb6e3842be7

[Mike Bayer]

tests in 4a4afca9595c9b4b10e6557c6ee819ae386c477a

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)