BTW, this is running in turbogears, and it might be related to how
i've been keeping a handle to a SA session in my model file. i tried
explicitly opening a session where i need it, and it seems to work as
expected now.
so on a related note, anyone have experience setting up a connection
pool in oracle for SA? i see in the docs for postgres/psycopg but i
don't know how or if i can port that to oracle.
On 8/15/06, jeff emminger <[EMAIL PROTECTED]> wrote:
> i'm getting a strange bug with joins in my object associations... not
> sure if it's just an error in how i've defined them in my code though.
>
> the basic outline is such:
>
> Review
> - id
> - user
> - submitted_by_user
>
> User
> - id
> - roles
>
> Role
> - id
>
> RolesUsers
> - role_id
> - user_id
>
> so, a Review has one User as 'user', and possibly one User as
> 'submitted_by_user'
>
> the bug i seem to be experiencing is that upon creation of a Review,
> in retrieving the associated 'user', SA is issuing SQL with a bad
> table alias in the ON clause. in the example below, it is using
> "users_7788" when it has not defined that alias.
>
> i'm not sure if i can create a test file to recreate this, but i'll
> try if you need.
>
> below is the config code i'm using.
>
> -------------
>
> SELECT
> users.username AS users_username,
> users.hashed_password AS users_hashed_password,
> users.last_name AS users_last_name,
> roles_ad97.id AS roles_ad97_id,
> roles_ad97.name AS roles_ad97_name,
> users.first_name AS users_first_name,
> users.date_deleted AS users_date_deleted,
> users.id AS users_id,
> users.email AS users_email
> FROM
> users
> LEFT OUTER JOIN roles_users roles_users_424e
> ON users_7788.id = roles_users_424e.user_id
> LEFT OUTER JOIN roles roles_ad97
> ON roles_users_424e.role_id = roles_ad97.id
> WHERE
> users.id = :users_id
> ORDER BY
> users.rowid,
> roles_users_424e.rowid
>
> {'users_id': 13}
>
> #=========================
>
> reviews = Table(
> "reviews", qa_metadata,
> Column("id", Integer, Sequence('reviews_seq'), primary_key=True),
> Column("stuff", Unicode(255), nullable=True),
> Column("user_id", Integer, ForeignKey("users.id"), nullable=False),
> Column("submitted_at", DateTime(), nullable=True),
> Column("submitted_by_user_id", Integer, ForeignKey("users.id"),
> nullable=True)
> )# end reviews Table
>
> users = Table(
> "users", qa_metadata,
> Column("id", Integer, Sequence("users_seq"), primary_key=True),
> Column("username", Unicode(32), nullable=False, unique=True),
> Column("hashed_password", Unicode(40), nullable=False),
> Column("first_name", Unicode(255), nullable=False),
> Column("last_name", Unicode(255), nullable=False),
> Column("email", Unicode(255), nullable=False),
> Column("date_deleted", DateTime(), nullable=True)
> )# end users Table
>
> roles = Table(
> "roles", qa_metadata,
> Column("id", Integer, Sequence("roles_seq"), primary_key=True),
> Column("name", Unicode(255), nullable=False)
> )# end roles Table
>
> roles_users = Table(
> "roles_users", qa_metadata,
> Column("role_id", Integer, ForeignKey("roles.id"), nullable=False,
> primary_key=True),
> Column("user_id", Integer, ForeignKey("users.id"), nullable=False,
> primary_key=True)
> )# end roles_users Table
>
> Review.mapper = mapper(Review, reviews,
> properties=dict(
> user=relation(
> User.mapper,
> lazy=False,
> private=False,
> primaryjoin=users.c.id==reviews.c.user_id
> ),
> submitted_by_user=relation(
> User.mapper,
> lazy=False,
> private=False,
> primaryjoin=users.c.id==reviews.c.submitted_by_user_id
> )
> )
> )
>
> RoleUser.mapper = mapper(RoleUser, roles_users)
>
> User.mapper = mapper(User, users,
> properties = dict(
> roles = relation(
> Role,
> secondary=roles_users,
> primaryjoin=users.c.id==roles_users.c.user_id,
> secondaryjoin=roles_users.c.role_id==roles.c.id,
> lazy=False,
> private=False
> )
> ),
> extension=UserExt()
> )
>
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
