Re: [sqlalchemy] Relationship spanning on multiple tables
On Aug 11, 2011, at 6:45 PM, neurino wrote:
Sorry if I bother again but adding some others relationships like this
spanning on 5 tables:
mapper(UserLayer, inherits=base_user_layer_mapper,
polymorphic_identity=CLASS_LAYER,
properties={
...
'lca_transport': relationship(LCATransport,
primaryjoin=and_(
user_layers.c.id_mat==lca_transport.c.id_mat,
user_layers.c.id_user_str==user_stratigraphies.c.id,
user_stratigraphies.c.id_prov==provinces.c.id,
provinces.c.id_reg==regions.c.id,
regions.c.id_mr==lca_transport.c.id_mr),
foreign_keys=(user_layers.c.id_mat,
user_layers.c.id_user_str)),
})
if you take a look at
http://www.sqlalchemy.org/trac/attachment/ticket/2255/very_long_m2o.py the
mappings succeed in compiling but many-to-one lazyloads on a long chain like
that have never been tested. Overall we don't have strong documentation /
methodologies written down for long chain relationships like these since this
is not really the primary use case of relationship.
I'd advise not relying upon relationship() to join out that long for now.In
my own applications, I reserve all my long chains of joins as explicitly
written in session.query() calls. The relationships themselves are simple.
The issue here is one of those where I have to set aside 2 hours of time and
focus on it so I'll try to get to it when I can.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Relationship spanning on multiple tables
Sorry if I bother again but adding some others relationships like this
spanning on 5 tables:
mapper(UserLayer, inherits=base_user_layer_mapper,
polymorphic_identity=CLASS_LAYER,
properties={
...
'lca_transport': relationship(LCATransport,
primaryjoin=and_(
user_layers.c.id_mat==lca_transport.c.id_mat,
user_layers.c.id_user_str==user_stratigraphies.c.id,
user_stratigraphies.c.id_prov==provinces.c.id,
provinces.c.id_reg==regions.c.id,
regions.c.id_mr==lca_transport.c.id_mr),
foreign_keys=(user_layers.c.id_mat,
user_layers.c.id_user_str)),
})
I can get it working at first attempt while I can't get `transm_limit` to
work here:
http://pastebin.com/mjfgPrcB
As a sidenote if I add `viewonly=True` to relationship above I get this
error:
sqlalchemy.exc.ArgumentError: Remote column 'user_stratigraphies.id'
is not part of mapping Mapper|LCATransport|lca_transport.
Specify remote_side argument to indicate which column lazy join
condition should bind.
Thanks for your support
neurino
On Tue, Aug 2, 2011 at 9:43 AM, neurino neur...@gmail.com wrote:
Sorry Michael,
there was a typo in my code, due to some wrong copy/paste or search/replace
I guess, I'm afraid.
The error I reported comes up mapping to `TransmLimit` and not `SurfaceRes`
(which is the one already mapped above).
I edited the pastebin:
http://pastebin.com/mjfgPrcB
now with:
'transm_limit': relationship(TransmLimit, single_parent=True,
uselist=False,
primaryjoin=and_(
user_stratigraphies.c.id_prov==provinces.c.id,
provinces.c.id_cz==transm_limits.c.id_cz,
user_stratigraphies.c.id_str==stratigraphies.c.id,
stratigraphies.c.id_tec==tech_elements_classes.c.id,
tech_elements_classes.c.id_tu==transm_limits.c.id_tu,
),
foreign_keys=(
user_stratigraphies.c.id_prov,
),
),
I get the error I reported:
sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated,
locally mapped column pairs for primaryjoin condition
'user_stratigraphies.id_prov = provinces.id AND provinces.id_cz =
transm_limits.id_cz AND user_stratigraphies.id_str = stratigraphies.id AND
stratigraphies.id_tec = tech_elements_classes.id AND
tech_elements_classes.id_tu = transm_limits.id_tu' on relationship
ustratatigraphy.transm_limit. For more relaxed rules on join conditions,
the relationship may be marked as viewonly=True.
Also there's the strange `viewonly=True` behavior that breakes
`UserStratigraphy.surface_res` relationship if uncommented.
Thanks for your patience.
neurino
On Mon, Aug 1, 2011 at 11:14 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
'transm_limit': relationship(SurfaceRes, single_parent=True,
#uselist=False,
#primaryjoin=and_(
#user_stratigraphies.c.id_prov==provinces.c.id,
#provinces.c.id_cz==transm_limits.c.id_cz,
#user_stratigraphies.c.id_str==stratigraphies.c.id,
#stratigraphies.c.id_tec==tech_elements_classes.c.id,
#tech_elements_classes.c.id_tu==transm_limits.c.id_tu,
#),
this fails because you aren't joining to the table to which SurfaceRes is
mapped, surface_res.
On Aug 1, 2011, at 7:02 AM, neurino wrote:
I'm trying to get some relationships spanning on multiple tables (4 or
5).
While I got the `4 tables` one working on first attempt (I was
surpized I could...) I can't get the `5 tables` one to work while the
code is almost the same.
Moreover with the first relationship if I add adding `viewonly=True`
initialization fails.
I just need these to get (not even set) some values with convenience
of SA attributes.
I published a working example on pastebin:
http://pastebin.com/RsZ6GCRq
I hope someone can sort out this thing, thank you.
Greetings
neurino
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
Re: [sqlalchemy] Relationship spanning on multiple tables
Sorry Michael, there was a typo in my code, due to some wrong copy/paste or search/replace I guess, I'm afraid. The error I reported comes up mapping to `TransmLimit` and not `SurfaceRes` (which is the one already mapped above). I edited the pastebin: http://pastebin.com/mjfgPrcB now with: 'transm_limit': relationship(TransmLimit, single_parent=True, uselist=False, primaryjoin=and_( user_stratigraphies.c.id_prov==provinces.c.id, provinces.c.id_cz==transm_limits.c.id_cz, user_stratigraphies.c.id_str==stratigraphies.c.id, stratigraphies.c.id_tec==tech_elements_classes.c.id, tech_elements_classes.c.id_tu==transm_limits.c.id_tu, ), foreign_keys=( user_stratigraphies.c.id_prov, ), ), I get the error I reported: sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated, locally mapped column pairs for primaryjoin condition 'user_stratigraphies.id_prov = provinces.id AND provinces.id_cz = transm_limits.id_cz AND user_stratigraphies.id_str = stratigraphies.id AND stratigraphies.id_tec = tech_elements_classes.id AND tech_elements_classes.id_tu = transm_limits.id_tu' on relationship ustratatigraphy.transm_limit. For more relaxed rules on join conditions, the relationship may be marked as viewonly=True. Also there's the strange `viewonly=True` behavior that breakes `UserStratigraphy.surface_res` relationship if uncommented. Thanks for your patience. neurino On Mon, Aug 1, 2011 at 11:14 PM, Michael Bayer mike...@zzzcomputing.comwrote: 'transm_limit': relationship(SurfaceRes, single_parent=True, #uselist=False, #primaryjoin=and_( #user_stratigraphies.c.id_prov==provinces.c.id, #provinces.c.id_cz==transm_limits.c.id_cz, #user_stratigraphies.c.id_str==stratigraphies.c.id, #stratigraphies.c.id_tec==tech_elements_classes.c.id, #tech_elements_classes.c.id_tu==transm_limits.c.id_tu, #), this fails because you aren't joining to the table to which SurfaceRes is mapped, surface_res. On Aug 1, 2011, at 7:02 AM, neurino wrote: I'm trying to get some relationships spanning on multiple tables (4 or 5). While I got the `4 tables` one working on first attempt (I was surpized I could...) I can't get the `5 tables` one to work while the code is almost the same. Moreover with the first relationship if I add adding `viewonly=True` initialization fails. I just need these to get (not even set) some values with convenience of SA attributes. I published a working example on pastebin: http://pastebin.com/RsZ6GCRq I hope someone can sort out this thing, thank you. Greetings neurino -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Relationship spanning on multiple tables
I'm trying to get some relationships spanning on multiple tables (4 or 5). While I got the `4 tables` one working on first attempt (I was surpized I could...) I can't get the `5 tables` one to work while the code is almost the same. Moreover with the first relationship if I add adding `viewonly=True` initialization fails. I just need these to get (not even set) some values with convenience of SA attributes. I published a working example on pastebin: http://pastebin.com/RsZ6GCRq I hope someone can sort out this thing, thank you. Greetings neurino -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Relationship spanning on multiple tables
'transm_limit': relationship(SurfaceRes, single_parent=True, #uselist=False, #primaryjoin=and_( #user_stratigraphies.c.id_prov==provinces.c.id, #provinces.c.id_cz==transm_limits.c.id_cz, #user_stratigraphies.c.id_str==stratigraphies.c.id, #stratigraphies.c.id_tec==tech_elements_classes.c.id, #tech_elements_classes.c.id_tu==transm_limits.c.id_tu, #), this fails because you aren't joining to the table to which SurfaceRes is mapped, surface_res. On Aug 1, 2011, at 7:02 AM, neurino wrote: I'm trying to get some relationships spanning on multiple tables (4 or 5). While I got the `4 tables` one working on first attempt (I was surpized I could...) I can't get the `5 tables` one to work while the code is almost the same. Moreover with the first relationship if I add adding `viewonly=True` initialization fails. I just need these to get (not even set) some values with convenience of SA attributes. I published a working example on pastebin: http://pastebin.com/RsZ6GCRq I hope someone can sort out this thing, thank you. Greetings neurino -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
