On Thu, Jan 23, 2014 at 7:46 PM, Michael Bayer <[email protected]> wrote:
>
> On Jan 23, 2014, at 2:07 PM, Simon King <[email protected]> wrote:
>
>>
>> That's fantastic, thanks so much. I feel bad that my silly use case
>> has caused so much work for you and grown the docs even more (perhaps
>> you need a separate "Tricks for People who Should Know Better"
>> section)
>
> oh but this use case comes up all the time, it's now that SQLA can really
> write a decent SELECT for this kind of thing that I felt it was time to make
> it an official use case on the site.
>
>>
>> If I understand what you've written correctly, the non-primary-mapper
>> version is the only one that will meet my needs. It seems to be
>> working well, both lazy and eager loading, and the extra properties
>> appearing on the class aren't an issue (I'm marking a lot of them as
>> deferred so as not to load too much from the database).
>
> Right, I think the differentiating factor when NP is needed is that you're
> joining A->B, there's any number of C, D in the middle, but also A and B have
> some direct foreign keys as well. is that the case for you?
>
> the non_primary use case could still benefit from some cleanup; the column
> naming issue as well as the fact that you get those extra columns in your
> results.
For anyone interested, here is roughly what I ended up with (full test
script attached):
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Terms(Base):
__tablename__ = 'terms'
id = sa.Column(sa.Integer(), primary_key=True)
version = sa.Column(sa.Integer(), default=1)
terms = sa.Column(sa.UnicodeText())
class Company(Base):
__tablename__ = 'company'
id = sa.Column(sa.Integer(), primary_key=True)
name = sa.Column(sa.Unicode(60))
termsid = sa.Column(sa.Integer, sa.ForeignKey(Terms.id),
nullable=False)
terms = saorm.relationship(Terms)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer(), primary_key=True)
name = sa.Column(sa.Unicode(60))
companyid = sa.Column(sa.Integer, sa.ForeignKey(Company.id),
nullable=False)
company = saorm.relationship(Company, backref='members')
class Acceptance(Base):
__tablename__ = 'acceptance'
userid = sa.Column(sa.Integer(), sa.ForeignKey(User.id),
primary_key=True)
termsid = sa.Column(sa.Integer(), sa.ForeignKey(Terms.id),
primary_key=True)
termsversion = sa.Column(sa.Integer(), nullable=False)
user = saorm.relationship(User)
terms = saorm.relationship(Terms)
j = (saorm.join(Company, Terms)
.join(Acceptance,
sa.and_(Acceptance.termsid == Terms.id,
Acceptance.termsversion == Terms.version)
)
)
currentacceptance = saorm.mapper(
Acceptance,
j,
properties={
'companyid': j.c.company_id,
'termsid': [j.c.terms_id,
j.c.company_termsid,
j.c.acceptance_termsid],
},
non_primary=True)
User.currentacceptance = saorm.relationship(
currentacceptance,
primaryjoin=sa.and_(
User.id == saorm.foreign(j.c.acceptance_userid),
User.companyid == j.c.company_id,
),
viewonly=True,
uselist=False)
Thanks again to Mike for all your help,
Simon
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.event import listen
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Terms(Base):
__tablename__ = 'terms'
id = sa.Column(sa.Integer(), primary_key=True)
version = sa.Column(sa.Integer(), default=1)
terms = sa.Column(sa.UnicodeText())
class Company(Base):
__tablename__ = 'company'
id = sa.Column(sa.Integer(), primary_key=True)
name = sa.Column(sa.Unicode(60))
termsid = sa.Column(sa.Integer, sa.ForeignKey(Terms.id),
nullable=False)
terms = saorm.relationship(Terms)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer(), primary_key=True)
name = sa.Column(sa.Unicode(60))
companyid = sa.Column(sa.Integer, sa.ForeignKey(Company.id),
nullable=False)
company = saorm.relationship(Company, backref='members')
class Acceptance(Base):
__tablename__ = 'acceptance'
userid = sa.Column(sa.Integer(), sa.ForeignKey(User.id),
primary_key=True)
termsid = sa.Column(sa.Integer(), sa.ForeignKey(Terms.id),
primary_key=True)
termsversion = sa.Column(sa.Integer(), nullable=False)
user = saorm.relationship(User)
terms = saorm.relationship(Terms)
j = (saorm.join(Company, Terms)
.join(Acceptance,
sa.and_(Acceptance.termsid == Terms.id,
Acceptance.termsversion == Terms.version)
)
)
currentacceptance = saorm.mapper(
Acceptance,
j,
properties={
'companyid': j.c.company_id,
'termsid': [j.c.terms_id,
j.c.company_termsid,
j.c.acceptance_termsid],
},
non_primary=True)
User.currentacceptance = saorm.relationship(
currentacceptance,
primaryjoin=sa.and_(
User.id == saorm.foreign(j.c.acceptance_userid),
User.companyid == j.c.company_id,
),
viewonly=True,
uselist=False)
def setup(session):
terms = [
Terms(version=1),
Terms(version=1),
]
session.add_all(terms)
companies = [
Company(name=u'Company 1', terms=terms[0]),
Company(name=u'Company 2', terms=terms[0]),
Company(name=u'Company 3', terms=terms[1]),
]
session.add_all(companies)
for i in range(9):
company = companies[i%3]
user = User(name=u'User %s' % (i + 1),
company=company)
session.add(user)
# some users should have accepted the terms
if i % 3:
acceptance = Acceptance(user=user,
terms=company.terms,
termsversion=company.terms.version)
session.add(acceptance)
def display(loadtype, title, session):
print
print title
print '=' * len(title)
q = (session.query(User)
.options(saorm.joinedload_all('company.terms')))
if loadtype == 'joined':
q = q.options(saorm.joinedload('currentacceptance'))
elif loadtype == 'subquery':
q = q.options(saorm.subqueryload('currentacceptance'))
for user in q:
print ('%s of %s: %s'
% (user.name,
user.company.name,
user.currentacceptance and 'accepted' or 'not accepted'))
def test():
from optparse import OptionParser
parser = OptionParser()
parser.add_option('-v', '--verbose', action='count',
default=0)
parser.add_option('-t', '--loadtype',
type='choice',
choices=['lazy', 'joined', 'subquery'],
default='joined')
options, args = parser.parse_args()
echo = {0: False,
1: True}.get(options.verbose, 'debug')
engine = sa.create_engine('sqlite:///:memory:', echo=echo)
queries = [0]
def before_cursor_execute(conn, cursor, statement, *args):
if statement.startswith('SELECT'):
queries[0] += 1
listen(engine, 'before_cursor_execute', before_cursor_execute)
Session = saorm.sessionmaker(bind=engine)
Base.metadata.create_all(engine)
session = Session()
setup(session)
session.flush()
display(options.loadtype,
'Initial Setup',
session)
session.commit()
t1 = session.query(Terms).get(1)
t1.version += 1
session.flush()
display(options.loadtype,
'After bumping version of terms',
session)
session.commit()
c3 = session.query(Company).get(3)
c3.termsid = 1
session.flush()
display(options.loadtype,
'After pointing company 3 at different terms',
session)
print
print '%s queries' % queries[0]
if __name__ == '__main__':
test()
