just an FYI its often easier to just use the actual objects in the
primary/secondary join instead of the strings. the string thing is just so
that order of declaration is not an issue.
On Mar 3, 2011, at 8:39 PM, Doug wrote:
> Thanks for the tip Michael, that solved my problem! It now looks like:
>
> category_association = Table('BankCategoryMap',
> Base.metadata,
> Column("ParentCategoryID", Integer,
> ForeignKey('BankCategories.ID'), primary_key=True),
> Column("ChildCategoryID", Integer,
> ForeignKey('BankCategories.ID'), primary_key=True))
>
> class Category(Base):
> __tablename__ = 'BankCategories'
>
> id = Column("ID", Integer, primary_key=True)
> name = Column("Name", String)
> description = Column("Description", String)
> active = Column("Active", SmallInteger)
>
>
> parents = relationship("Category",
> secondary=category_association,
> primaryjoin="Category.id ==
> BankCategoryMap.c.ChildCategoryID",
>
> secondaryjoin="BankCategoryMap.c.ParentCategoryID == Category.id",
> backref='children')
>
> Which works just dandy.
>
>
> Thanks again,
> Doug
>
>
> On Mar 3, 3:07 pm, Michael Bayer <[email protected]> wrote:
>> On Mar 3, 2011, at 5:11 PM, Doug wrote:
>>
>>
>>
>>> Hello all,
>>
>>> I've just started using SQLAlchemy recently (and it's been great) but
>>> I have run into a problem recently. I am trying to map a table back to
>>> itself via a second many-to-many table in a declarative fashion. I
>>> have this working as so (mapper configuration):
>>
>>> However, when I try it the Declarative way:
>>
>>> category_association = Table('BankCategoryMap',
>>> Base.metadata,
>>> Column("ParentCategoryID", Integer,
>>> ForeignKey('BankCategories.ID'), primary_key=True),
>>> Column("ChildCategoryID", Integer,
>>> ForeignKey('BankCategories.ID'), primary_key=True))
>>
>>> class Category(Base):
>>> __tablename__ = 'BankCategories'
>>
>>> id = Column("ID", Integer, primary_key=True)
>>> name = Column("Name", String)
>>> description = Column("Description", String)
>>> active = Column("Active", SmallInteger)
>>
>>> parents = relationship("Category",
>>> secondary=category_association,
>>> primaryjoin="BankCategories.ID" ==
>>> category_association.c.ChildCategoryID,
>>
>>> secondaryjoin=category_association.c.ParentCategoryID ==
>>> "BankCategories.ID",
>>> backref='children')
>>
>> You need to use the actual attributes, or a full string, when using the
>> primaryjoin and secondaryjoin arguments. You can't mix and match operators
>> with strings. Some FAQ on a similar issue is here:
>> http://www.sqlalchemy.org/trac/wiki/FAQ#ImusingDeclarativeandsettingp....
>>
>>
>>
>>> This does not work and I get the following error:
>>
>>> File '/home/doug/pyenv_svn/CreateService/surveyservice/surveyservice/
>>> controllers/error.py', line 25 in document
>>> cat.parents
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/attributes.py', line 170 in
>>> __get__
>>> instance_dict(instance))
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/attributes.py', line 390 in
>>> get
>>> value = callable_(passive=passive)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/strategies.py', line 670 in
>>> __call__
>>> result = q.all()
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1576 in all
>>> return list(self)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1688 in
>>> __iter__
>>> return self._execute_and_instances(context)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1693 in
>>> _execute_and_instances
>>> mapper=self._mapper_zero_or_none())
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/session.py', line 729 in
>>> execute
>>> clause, params or {})
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1191 in
>>> execute
>>> params)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1269 in
>>> _execute_clauseelement
>>> parameters=params
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1377 in
>>> __create_execution_context
>>> connection=self, **kwargs)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/default.py', line 388 in
>>> __init__
>>> grp,m in enumerate(parameters)]
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/sql/compiler.py', line 291 in
>>> construct_params
>>> pd[self.bind_names[bindparam]] = bindparam.value()
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/strategies.py', line 447 in
>>> <lambda>
>>> state, dict_, bind_to_col[bindparam.key])
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/mapper.py', line 1303 in
>>> _get_state_attr_by_column
>>> return self._columntoproperty[column]._getattr(state, dict_, column,
>>> passive=passive)
>>> File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
>>> SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/mapper.py', line 2510 in
>>> __missing__
>>> (column, self.mapper))
>>> UnmappedColumnError: No column :ChildCategoryID_1 is configured on
>>> mapper Mapper|Category|BankCategories...
>>
>>> Any idea/help on why I'm getting the UnmappedColumnError?
>>
>>> Thanks,
>>> Doug
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group
>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
