oh, you want to refer to the tertiary table in both the primary and secondary join. so right this pattern does not correspond to the A->secondary->B pattern and isn’t really a classic many-to-many.
a quick way to map these are to use non primary mappers (was going to just
paraphrase, but let me just try it out b.c. these are fun anyway, and I want to
see the new joining behavior we have in 0.9…):
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class First(Base):
__tablename__ = 'first'
first_id = Column(Integer, primary_key=True)
partition_key = Column(String)
def __repr__(self):
return ("First(%s, %s)" % (self.first_id, self.partition_key))
class Second(Base):
__tablename__ = 'second'
id = Column(Integer, primary_key=True)
first_id = Column(Integer)
other_id = Column(Integer)
class Partitioned(Base):
__tablename__ = 'partitioned'
id = Column(Integer, primary_key=True)
partition_key = Column(String)
other_id = Column(Integer)
def __repr__(self):
return ("Partitioned(%s, %s)" % (self.partition_key, self.other_id))
j = join(Partitioned, Second, Partitioned.other_id == Second.other_id)
partitioned_second = mapper(Partitioned, j, non_primary=True, properties={
# note we need to disambiguate columns here - the join()
# will provide them as j.c.<tablename>_<colname> for access,
# but they retain their real names in the mapping
"id": j.c.partitioned_id,
"other_id": [j.c.partitioned_other_id, j.c.second_other_id],
"secondary_id": j.c.second_id
})
First.partitioned = relationship(
partitioned_second,
primaryjoin=and_(
First.partition_key ==
partitioned_second.c.partition_key,
First.first_id ==
foreign(partitioned_second.c.first_id)
), innerjoin=True)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
First(first_id=1, partition_key='p1'),
First(first_id=2, partition_key='p1'),
First(first_id=3, partition_key='p2'),
Second(first_id=1, other_id=1),
Second(first_id=2, other_id=1),
Second(first_id=3, other_id=2),
Partitioned(partition_key='p1', other_id=1),
Partitioned(partition_key='p1', other_id=2),
Partitioned(partition_key='p2', other_id=2),
])
s.commit()
for row in s.query(First, Partitioned).join(First.partitioned):
print(row)
for f in s.query(First):
for p in f.partitioned:
print(f.partition_key, p.partition_key)
I mapped to a join directly, and not a select, so as long as we aren’t using
SQLite (and are using 0.9) we get nested join behavior like this:
SELECT first.first_id AS first_first_id, first.partition_key AS
first_partition_key, partitioned.id AS partitioned_id,
partitioned.partition_key AS partitioned_partition_key, partitioned.other_id AS
partitioned_other_id
FROM first JOIN (partitioned JOIN second ON partitioned.other_id =
second.other_id) ON first.partition_key = partitioned.partition_key AND
first.first_id = second.first_id
2013-12-05 11:27:18,347 INFO sqlalchemy.engine.base.Engine {}
(First(1, p1), Partitioned(p1, 1))
(First(2, p1), Partitioned(p1, 1))
(First(3, p2), Partitioned(p2, 2))
the load of f.partitioned will load the Partitioned objects in terms of the
“partitioned_second” mapper, so those objects will have those extra cols from
“second” on them. You can screw around with this using exclude_properties for
those cols you don’t need to refer to on the mapping, and perhaps primary_key
if the mapper complains, such as:
partitioned_second = mapper(Partitioned, j, non_primary=True, properties={
"id": j.c.partitioned_id,
"other_id": [j.c.partitioned_other_id, j.c.second_other_id],
}, exclude_properties=[j.c.second_id], primary_key=[j.c.partitioned_id,
j.c.second_other_id])
or you can just ignore those extra attributes on some of your Partitioned
objects.
On Dec 5, 2013, at 11:03 AM, Adrian Schreyer <adrian.schre...@gmail.com> wrote:
> Given the three mappings First, Second and Partitioned, I want to declare a
> relationship between First and Partitioned. The problem is that Partitioned
> is partitioned by partition_key which is a column in First but not in Second.
> Second however contains the identifier that actually links First to specific
> rows in the partitioned table.
>
> So far the mapping looks like this mock example:
>
>
> partitioned = relationship("Partitioned",
> secondary=Base.metadata.tables['schema.seconds'],
> primaryjoin="and_(First.first_id==Second.first_id,
> First.partition_key==Partitioned.partition_key)",
> secondaryjoin="Second.other_id==Partitioned.other_id",
> foreign_keys="[Second.first_id, Partitioned.partition_key,
> Partitioned.other_id]",
> uselist=True, innerjoin=True, lazy='dynamic')
> It works, but it only interpolates the First.first_id with the actual value
> which normally makes sense but to make the PostgreSQL constraint-exclusion
> work the First.partition_key would need to be interpolated with the proper
> value as well. Right now it is only given as
> First.partition_key==Partitioned.partition_key.
>
> Does that make sense? I am not sure if my relationship configuration is wrong
> or if this kind of mapping is simply not supported.
>
>
>
> On Thu, Dec 5, 2013 at 3:31 PM, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
>
> On Dec 5, 2013, at 6:57 AM, Adrian Schreyer <adrian.schre...@gmail.com> wrote:
>
>> Actually that was a bit too early but I tracked the problem down to the
>> many-to-many relationship. Parameters are only interpolated (e.g.
>> %(param_1)s) for the primaryjoin to the secondary table. Is there a
>> technique to force relationship() to interpolate a parameter between the 1st
>> and 3rd table instead of using only table.column=table.column?
>
> there’s no reason why that would be the case can you provide more specifics?
>
>
>
>
>>
>>
>> On Thu, Dec 5, 2013 at 10:58 AM, Adrian Schreyer <adrian.schre...@gmail.com>
>> wrote:
>> Never mind,
>>
>> the problem was that I specified the clause in a secondaryjoin and not in
>> the primaryjoin of the relationship().
>>
>>
>> On Thu, Dec 5, 2013 at 10:44 AM, Adrian <adrian.schre...@gmail.com> wrote:
>> Hi All,
>>
>> I have a few partitioned tables in my PostgreSQL database but I do not know
>> yet how to make the ORM relationship() with partition constraint-exclusion
>> on the instance level. Constraint-exclusion does not work with joins and
>> requires scalar values - the problem is that I would need to add an
>> additional WHERE clause to the primaryjoin (which adds the partition key) if
>> the relationship is accessed from the instance level, e.g. user.addresses.
>> Is there a mechanism in relationship() to distinguish between class-based
>> joins (User.addresses) and instance-level access?
>>
>> --
>> You received this message because you are subscribed to a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/unsubscribe.
>> To unsubscribe from this group and all its topics, 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.
>>
>>
>>
>> --
>> 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.
>
>
>
> --
> 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
