On Oct 17, 2006, at 5:38 AM, Christoph Zwerschke wrote:
> mapper(User, pg_user, properties={
> 'user_id': pg_user.c.usesysid,
> 'user_name': pg_user.c.usename,
> 'is_super': pg_user.c.usesuper,
> 'groups': relation(Group, viewonly=True,
> primaryjoin=pg_user.c.usesysid==func.any
> (pg_group.c.grolist))})
>
> I get this error:
>
> sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't
> figure
> out which side is the foreign key for join condition
> 'pg_user.usesysid =
> any(pg_group.grolist)'. Specify the 'foreignkey' argument to the
> relation.
>
> Shouldn't it be clear what the foreign key is in this situation?
to a human, maybe. to a python interpreter the right side of the
binary '==' expression is just a sqlalchemy.sql.Function, which looks
nothing like the sqlalchemy.schema.Column type which it expects to
locate as a foreign key.
> Now when I explicitly specify the foreign key (as pg_group.grosysid or
> pg_group.grolist, doesn't matter),
>
> mapper(User, pg_user, properties={
> 'user_id': pg_user.c.usesysid,
> 'user_name': pg_user.c.usename,
> 'is_super': pg_user.c.usesuper,
> 'groups': relation(Group, viewonly=True,
> primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> foreignkey=pg_group.c.grosysid)})
>
> Then the groups property returns all existing groups, not the
> groups of
> the corresponding user (the following query is echoed by the engine):
>
> SELECT pg_group.grolist, pg_group.grosysid, pg_group.groname
> FROM pg_user, pg_group
> WHERE pg_user.usesysid = any(pg_group.grolist)
> ORDER BY pg_group.grosysid
for the "lazy clause" generation, which is when it takes "x=y" and
converts it into "x=?", this is the same issue as the foreign key.
but for this, i have committed in rev 2001 a more thorough search for
a "Column" in each side of the clause so that it can identify which
side of a "=" operation it can apply a bind parameter to, so a test
program can now generate:
SELECT pg_group.groname AS pg_group_groname, pg_group.grolist AS
pg_group_grolist, pg_group.grosysid AS pg_group_grosysid
FROM pg_catalog.pg_group
WHERE %(lazy_bcba)s = any(pg_group.grolist) ORDER BY pg_group.grosysid
a similar approach might apply to the "foreignkey" detection issue
although i like to keep these "clause analysis" functions as
conservative as possible, since forcing explicitness in the case of
unusual configurations (joining on a function is an unusual
configuration) reduces the chances of surprise behavior. in the case
of the "lazy clause" i would rather not have users start to worry
about manually defining those.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~----------~----~----~----~------~----~------~--~---
