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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.