On Wed, Jan 3, 2018 at 7:48 PM, Mike Bayer <[email protected]> wrote:
> didn't look close yet but you wouldn't want to have foreign() on a
> column that is a primary key column. should be on the opposite side
> of whatever refers to a primary key.
that and, when you use contains_eager you need to tell it what entity
it's looking for when it considers columns as part of a relationship:
qedge_case = s.query(Foo)\
.join(Foo_2,
Foo.id_foo_alt == Foo_2.id
)\
.filter(Foo_2.name.op('IS NOT')(None),
)\
.options(sqlalchemy.orm.contains_eager('foo_alt', alias=Foo_2))\
.order_by(Foo.id.asc())
>
> On Wed, Jan 3, 2018 at 6:28 PM, Jonathan Vanasco <[email protected]>
> wrote:
>> i've got a handful of bad data from some edge cases and have been having
>> trouble querying the data with sqlalchemy. i attached a SSCCE below.
>>
>> my cleanup script imports the model, extends `Main_Foo` with a new
>> relationship, and creates 2 aliases.
>>
>> this selects the right rows (2 and 4) but the relationship is populated with
>> the "local side" data, not the "remote side" data
>>
>> i'm sure I'm missing something obvious.
>>
>>
>>
>> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> - -
>> # Standard imports
>>
>>
>> import sqlalchemy
>> import sqlalchemy.orm
>>
>>
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import Integer, Column, Unicode
>>
>>
>> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> - -
>> # You probably don't need to overwrite this
>> Base = declarative_base()
>>
>>
>> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> - -
>> # Define some models that inherit from Base
>>
>>
>> class Main_Foo(Base):
>> __tablename__ = 'main_foo'
>> id = Column(Integer, primary_key=True)
>> id_foo_alt = Column(Integer)
>> name = Column(Unicode)
>>
>>
>>
>> # need to define these separately as they're in a new script that overwrites
>> the model
>> Main_Foo.foo_alt = sqlalchemy.orm.relationship(
>> "Main_Foo",
>> primaryjoin="Main_Foo.id_foo_alt==remote(foreign(Main_Foo.id))",
>> uselist=False,
>> )
>> Foo = sqlalchemy.orm.aliased(Main_Foo, name='foo')
>> Foo_2 = sqlalchemy.orm.aliased(Main_Foo, name='foo2')
>>
>>
>> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> - -
>> # set the engine
>>
>>
>> engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
>> Base.metadata.create_all(engine)
>>
>>
>> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> - -
>> # do a simple query to trigger the mapper error
>>
>>
>> sessionFactory = sqlalchemy.orm.sessionmaker(bind=engine)
>> s = sessionFactory()
>>
>>
>> rows = ((1, None, 'a (ab)'),
>> (2, 1, 'b (ab)'),
>> (3, None, 'c (cd)'),
>> (4, 3, 'd (cd)'),
>> (5, None, 'e'),
>> (6, 'x', 'f'),
>> (7, 'x', 'g'),
>> )
>> for row in rows:
>> f = Main_Foo()
>> f.id = row[0]
>> f.id_foo_alt = row[1]
>> f.name = row[2]
>> s.add(f)
>> s.flush()
>> s.commit()
>>
>>
>> qedge_case = s.query(Foo)\
>> .join(Foo_2,
>> Foo.id_foo_alt == Foo_2.id
>> )\
>> .filter(Foo_2.name.op('IS NOT')(None),
>> )\
>> .options(sqlalchemy.orm.contains_eager('foo_alt'))\
>> .order_by(Foo.id.asc())
>>
>>
>> for f in qedge_case.all():
>> print "---"
>> print f.id
>> print " %s %s" % (f.id, f.name)
>> print " > >"
>> print " %s %s" % (f.foo_alt.id, f.foo_alt.name)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> 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.
