Hey folks, hit a new error message and the googling is not turning up much,
and as I'm new to postgres, I'm frankly over my head. I have a wack of
reflected tables and I'm trying to get a join working. The one I'm
ultimately after has a composite primary key of 2 different foreign keys
and an int (can be 1,3,6,12). Here's it's postgres describe:
Table "public.appurls"
Column | Type | Modifiers | Storage | Description
--------------+------------------------+-----------+----------+-------------
urlagentcode | character varying(255) | | extended |
appfee | double precision | | plain |
groupagentid | integer | not null | plain |
payterm | character varying(10) | not null | extended |
tierid | integer | not null | plain |
billingcycle | character varying(255) | | extended |
alias | character varying(255) | | extended |
fulfillment | character varying(2) | | extended |
Indexes:
"pk_appurls" PRIMARY KEY, btree (groupagentid, tierid, payterm)
"appurls_appfee_idx" btree (appfee)
"appurls_groupagentid_idx" btree (groupagentid)
"appurls_payterm_idx" btree (payterm)
"appurls_tierid_idx" btree (tierid)
Has OIDs: no
I set up my table and my mappers like so:
appurl_table = Table("appurls", metadata,
Column('tierid', Integer, ForeignKey('tier.tierid'), primary_key=True ),
Column('groupagentid', Integer, ForeignKey('groupagent.groupagentid'),
primary_key=True ),
autoload=True
)
mappers['AppUrl'] = mapper(AppUrl, appurl_table, properties={
# specify relationship to Tier
'tier': relation(Tier, backref='appurls',
primaryjoin=(appurl_table.c.tierid == tier_table.c.tierid) ),
# specify relationship to GroupAgent
'groupagent': relation(GroupAgent, backref='appurls',
primaryjoin=(appurl_table.c.groupagentid ==
groupagent_table.c.groupagentid) )
})
And then I attempt a query with a daisy chain of joins and I get the
traceback I've posted at the bottom with the message:
ArgumentError: Could not locate any simple equality expressions involving
locally mapped foreign key columns for primary join condition
'appurls.groupagentid = groupagent.groupagentid' on relationship
AppUrl.groupagent. Ensure that referencing columns are associated with a
ForeignKey or ForeignKeyConstraint, or are annotated in the join condition
with the foreign() annotation. To allow comparison operators other than
'==', the relationship can be marked as viewonly=True.
I'm just joining with
app_url = request.db.query(AppUrl
).join(Tier
).join(GroupAgent ( it goes on and on)
I *thought* I'd specified the join conditions well enough in the above, but
clearly I'm missing something, any help much appreciated!
thanks!
Iain
traceback:
File "/home/qualbe/src/PaymentProxy/paymentproxy/helpers.py", line 30, in
get_appurl
app_url = request.db.query(AppUrl
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py",
line 1107, in query
return self._query_cls(entities, self, **kwargs)
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
line 115, in __init__
self._set_entities(entities)
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
line 124, in _set_entities
self._set_entity_selectables(self._entities)
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
line 157, in _set_entity_selectables
ent.setup_entity(*d[entity])
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py",
line 2861, in setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/util/langhelpers.py",
line 612, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
line 1458, in _with_polymorphic_mappers
configure_mappers()
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
line 2153, in configure_mappers
mapper._post_configure_properties()
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
line 1275, in _post_configure_properties
prop.init()
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py",
line 231, in init
self.do_init()
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
line 1028, in do_init
self._setup_join_conditions()
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py",
line 1102, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/relationships.py",
line 119, in __init__
self._check_foreign_cols(self.primaryjoin, True)
File
"/home/qualbe/src/eggs/SQLAlchemy-0.8.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/relationships.py",
line 626, in _check_foreign_cols
raise sa_exc.ArgumentError(err)
ArgumentError: Could not locate any simple equality expressions involving
locally mapped foreign key columns for primary join condition
'appurls.groupagentid = groupagent.groupagentid' on relationship
AppUrl.groupagent. Ensure that referencing columns are associated with a
ForeignKey or ForeignKeyConstraint, or are annotated in the join condition
with the foreign() annotation. To allow comparison operators other than
'==', the relationship can be marked as viewonly=True.
--
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.
