On Fri, Oct 20, 2017 at 4:52 PM, Leslie Luyt <leslieluyt.j...@gmail.com> wrote:
> Here is my example:
>
> class Role(Base):
> __tablename__ = 'role'
> role_id = Column(BigInteger, primary_key=True)
> role_name = Column(String, nullable=False)
> role_description = Column(String)
>
>
> class Group(Base):
> __tablename__ = 'group'
> group_id = Column(BigInteger, primary_key=True)
> group_name = Column(String, nullable=False)
> group_description = Column(String)
>
> profiles = association_proxy('_profile_group_roles', 'profile')
>
>
> class Profile(Base):
> __tablename__ = 'profile'
> profile_id = Column(BigInteger, primary_key=True)
> profile_username = Column(String, nullable=False, unique=True)
>
> groups = association_proxy('_profile_group_roles', 'group')
> roles = association_proxy('_profile_group_roles', 'role')
>
>
> class ProfileGroupRole(Base):
> __tablename__ = 'profile_group_role_assoc'
> profile_id = Column(BigInteger, ForeignKey('profile.profile_id'),
> primary_key=True)
> group_id = Column(BigInteger, ForeignKey('group.group_id'),
> primary_key=True)
> role_id = Column(BigInteger, ForeignKey('role.role_id'),
> primary_key=True)
>
> group = relationship(Group, backref=backref('_profile_group_roles',
> cascade='all, delete-orphan'))
> role = relationship(Role)
> profile = relationship(Profile, backref=backref('_profile_group_roles',
> cascade='all, delete-orphan'))
>
>
> Example data:
> s = db.connect(engine_config)
> profile1 = db.Profile(profile_username='test_user')
> group1 = db.Group(group_name='group1', group_description='')
> role1 = db.Role(role_name='role1')
> prg1 = db.ProfileGroupRole(profile=profile1, group=group1,
> role=role1)
> group2 = db.Group(group_name='group2', group_description='')
> role2 = db.Role(role_name='role2')
> prg2 = db.ProfileGroupRole(profile=profile1, group=group2,
> role=role1)
> prg3 = db.ProfileGroupRole(profile=profile1, group=group2,
> role=role2)
> s.add_all([prg1, prg2, prg3])
> s.commit()
>
> Querying:
> db.Group.query.filter(db.Group.group_name ==
> 'group1').one().profiles[0].roles
>
> produces:
> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
> Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
> Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]
>
> and now what I expected to get back was just one the one role associated
> with 'group1':
> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]
OK so this is a little awkward if you truly want
"group.profiles[0].roles" to be local to the group. It means you don't
really want Group.profiles to be a list of Profile objects, which is
an object that can be associated with multiple groups. You want a
"view" object that is a GroupProfile, a Profile that's associated with
a specific group, which then has one or more Roles set up for that
Group/Profile association. So implied here is that the join across
the ProfileGroupRole association is between GroupProfile and Role - as
far as how to link Group and Profile together we will need to produce
distinct pairs by joining against a DISTINCT of ProfileGroupRole.
This is a more complicated technique.
I'm going off your original email that indicates you can't change the
table structure at all here. So to work all these requirements
together we can create a GroupProfile "view" object as:
class GroupProfile(Base):
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, ProfileGroupRole).join(Profile)).
distinct().alias()
)
group = relationship(
Group,
backref=backref("profiles", viewonly=True,
order_by=__table__.c.profile_id),
primaryjoin=foreign(__table__.c.group_id) == Group.group_id,
viewonly=True)
roles = relationship(
Role,
secondary=ProfileGroupRole.__table__,
primaryjoin=and_(
__table__.c.group_id == ProfileGroupRole.group_id,
__table__.c.profile_id == ProfileGroupRole.profile_id),
viewonly=True,
collection_class=set,
lazy="joined", innerjoin=True)
Just so you know, that mapping is using several advanced techniques to
work the three-way association table into a structure that behaves
more like individual two-way associations (however note this is
strictly read-only stuff, it won't persist data as designed). The map
to a SELECT sets up that we can get unique Group/Profile pairs
regardless of how many ProfileGroupRole rows link them together. The
GroupProfile.role relationship brings ProfileGroupRole in as an
association table a second time to produce individual rows per Role,
and it also uses joined eager loading with innerjoin=True to emit
fewer queries; we can use innerjoin because there are definitely Role
rows present.
Below is full demo for SQLite that returns what you expect as well as
what I think you expect for some more complex cases:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Role(Base):
__tablename__ = 'role'
role_id = Column(Integer, primary_key=True)
role_name = Column(String, nullable=False)
role_description = Column(String)
def __repr__(self):
return "%s%r" % (
self.__class__.__name__,
{k: v for k, v in self.__dict__.items()
if not k.startswith('_')})
class Group(Base):
__tablename__ = 'group'
group_id = Column(Integer, primary_key=True)
group_name = Column(String, nullable=False)
group_description = Column(String)
class Profile(Base):
__tablename__ = 'profile'
profile_id = Column(Integer, primary_key=True)
profile_username = Column(String, nullable=False, unique=True)
class ProfileGroupRole(Base):
__tablename__ = 'profile_group_role_assoc'
profile_id = Column(
Integer, ForeignKey('profile.profile_id'), primary_key=True)
group_id = Column(
Integer, ForeignKey('group.group_id'), primary_key=True)
role_id = Column(
Integer, ForeignKey('role.role_id'), primary_key=True)
group = relationship(
Group, backref=backref('_profile_group_roles',
cascade='all, delete-orphan'))
role = relationship(Role)
profile = relationship(
Profile, backref=backref('_profile_group_roles',
cascade='all, delete-orphan'))
# using generic DISTINCT here, so create a subquery to minimize the columns
# against which DISTINCT is being applied. On Postgresql, we can
# use DISTINCT ON which would allow us to drop the extra subquery.
distinct_profile_groups = select(
[ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\
distinct().alias()
class GroupProfile(Base):
# the GroupProfile itself is then based on Group/Profile rows
# joined to our distinct group_id/profile_id pairs
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, distinct_profile_groups).join(Profile)).
alias()
)
group = relationship(
Group,
backref=backref("profiles", viewonly=True,
order_by=__table__.c.profile_id),
# "foreign" - means our local "group_id" column can refer
# to the same "Group.group_id" across multiple rows, e.g. as though
# it's a foreign key to Group.group_id
primaryjoin=foreign(__table__.c.group_id) == Group.group_id,
viewonly=True)
roles = relationship(
Role,
secondary=ProfileGroupRole.__table__,
primaryjoin=and_(
__table__.c.group_id == ProfileGroupRole.group_id,
__table__.c.profile_id == ProfileGroupRole.profile_id),
viewonly=True,
collection_class=set,
lazy="joined", innerjoin=True)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
profile1 = Profile(profile_username='test_user')
group1 = Group(group_name='group1', group_description='')
role1 = Role(role_name='role1')
prg1 = ProfileGroupRole(profile=profile1, group=group1, role=role1)
group2 = Group(group_name='group2', group_description='')
role2 = Role(role_name='role2')
prg2 = ProfileGroupRole(profile=profile1, group=group2, role=role1)
prg3 = ProfileGroupRole(profile=profile1, group=group2, role=role2)
group3 = Group(group_name='group3', group_description='')
profile2 = Profile(profile_username='user2')
role3 = Role(role_name='role3')
prg4 = ProfileGroupRole(profile=profile1, group=group3, role=role1)
prg5 = ProfileGroupRole(profile=profile2, group=group3, role=role2)
prg6 = ProfileGroupRole(profile=profile2, group=group3, role=role3)
s.add_all([prg1, prg2, prg3, prg4, prg5])
s.commit()
# test one. A group with one GroupProfile and one Role.
group1 = s.query(Group).filter(Group.group_name == 'group1').one()
assert group1.profiles[0].roles == {role1}
print group1.profiles[0].roles
# test two. A group with one GroupProfile and two Roles.
group2 = s.query(Group).filter(Group.group_name == 'group2').one()
assert group2.profiles[0].roles == {role1, role2}
print group2.profiles[0].roles
assert len(group2.profiles) == 1
# test three. A group with *two* GroupProfile objects (e.g. two Profiles),
# each with roles.
group3 = s.query(Group).filter(Group.group_name == 'group3').one()
assert group3.profiles[0].roles == {role1}
assert group3.profiles[1].roles == {role2, role3}
print group3.profiles[0].roles
print group3.profiles[1].roles
assert len(group3.profiles) == 2
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
