[sqlalchemy] Re: Confused about relations

[Scott Graham]

>
> u.Xs is going to have exactly those X's which have u's id as their
> user_id attribute.  the X which you appended to u.Ys[0] is from a
> different relationship.  so yeah you have to set the user_id attribute
> on the X, which is entirely legal.  However the "legit" way to do it
> is to just add the "X" to u's Xs collection and have the ORM take care
> of the user_id attribute for you.  just like:
>
> x = X()
> u.Ys[0].Xs.append(x)
> u.Xs.append(x)


Ah, I see, thanks. I hadn't thought of doing it that way since it sort of
looks like it'd be inserting it twice. But, makes sense now that I see it.


> no you dont need to do all that.  even if you are just setting
> "user_id" as you are now, just expire the attribute:
> session.expire(u, ['Xs']), and it will reload when you touch it
> again.  but if you think in terms of collections instead of
> foreign keys like above, then you dont even need that, it would just
> all work out.


Thanks again, I should have rtfm'd better for this one.

scott

--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Confused about relations

[Scott Graham]

Hi

(I apologize in advance if this is a silly question, but I'm having
some trouble figuring out why this isn't working as expected.)

I have 3 tables: Users, Xs, and Ys. Each has an id, and relevant data.
Each user can have a bunch of Xs as well as a bunch of Ys, for which I
have a ForeignKey("users.id") in Xs and Ys. In addition, each Y can
have 0 or more Xs. For this, I made an auxiliary table X_Ys containing
X and Y keys.

Tables:

usersTable = Table('users', metadata,
Column('id', Integer, primary_key=True),
...
)

ysTable = Table('ys', metadata,
Column('id', Integer, primary_key=True),
...
Column('user_id', Integer, ForeignKey('users.id'), nullable=False)
)

X_Ys = Table('x_ys', metadata,
Column('y_id', Integer, ForeignKey('ys.id'),
nullable=False, primary_key=True),
Column('x_id', Integer, ForeignKey('xs.id'),
nullable=False, primary_key=True))

XsTable = Table('xs', metadata,
Column('id', Integer, primary_key=True),
...
Column('user_id', Integer, ForeignKey('users.id'), nullable=False)
)

Mappers are:

mapper(User, usersTable, properties = {
'Ys': relation(Y, backref='user'),
'Xs': relation(X, backref='user'),
})
mapper(X, xsTable, properties = {
'Ys': relation(Y, secondary = X_Ys)
})
mapper(Y, ysTable, properties={
'Xs': relation(X, secondary = X_Ys)
})

then:

>>> u = User(...)
>>> u.Ys.append(Y(...))
>>> u.Ys[0].Xs.append(X(...))

>>> u.Ys
[]
>>> u.Ys[0].Xs
[]
>>> u.Xs
[]

I was expecting/hoping that u.Xs would have the X that was appended to
u.Ys[0]. I can sort of make it work as long as I make the id columns
in X_Ys non-nullable. In that case, session.commit() throws an
IntegrityError if I don't manually set the user_id in any X objects to
a user id (Y objects get their user id ok). However, when I do that:

>>> u.Ys[0].Xs[0].user_id = u.id
>>> u.commit()

I seem to have to make a new session before the changes "show up" in
the user. ie. I must do:

sess.close()
sess = Session()
u = s.query(User).first()

before u.Xs has the element I'd expect. It does, however, seem to be
ok at that point.

Is there something dumb that I've set up wrong that someone could
point me at? Or is this just a "gotcha" to be avoided?

thanks,
scott

--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---