I'm on mobile at the moment but currently while your code embeds an
appropriate creational pattern into each association proxy, that has no
bearing upon the objects that each proxy returns, since they are all
proxying the same collection which contains everything. The reason it's
called a "proxy" is because there is no collection local to the proxy
itself, it's strictly a window into the single collection that's bound to
the relationship.
So the architecture here should instead use three relationships, and to
make it automatically handle the enumeration you can use a single table
inheritance pattern on your association object, meaning you would have
ContractorAssociation, EmployeeAssociation, and PartTimeAssociation. You
would find things work pretty automatically in that style. alternatively
you can use custom join conditions on each relationship. There are even
ways you can programmatically build up these classes and relationships
based on the items available in the enum, if that were a concern.
Again I'm on mobile but if you are still struggling I can point you to more
docs or proof of concepts later on,.other folks here maybe can help.
On Sun, Apr 21, 2019, 8:51 PM Jason Armstrong <riverdr...@gmail.com> wrote:
> Hello everyone,
>
> I am modelling a relationship between two groups which is a many-to-many
> relationship, with an additional constraint.
> I have an Employer and a Worker class. Employers can have a relationship
> to a worker in a number of ways, which
> I'm representing by an enum: Contractor, Employee, Part-Time etc.
> The join table has three columns, employer_id, worker_id and worker_type,
> which is the enum value.
>
> The below code works for creating the relationship, but I don't quite
> understand how to query it.
>
> I create an employer, and then a worker.
> Then I use
> employer.contractor.append(worker)
>
> The join table contains the correct ids and the 'CONTRACTOR' enum value.
> But when I iterate through employer.part_time, I get back the same worker
> who is a 'CONTRACTOR'. I'm expecting that only
> employer.contractor should contain values, and that employer.employee and
> employer.part_time should be empty.
>
> Is there something corresponding to *creator* that can be used when
> retrieving the values?
> Or, what is the best way for me to get back the correct values? Do I need
> to issue a query, and with what parameters?
>
>
> from sqlalchemy import Column, ForeignKey, Enum, Text, Integer,
> create_engine
> from sqlalchemy.orm import relationship, backref, sessionmaker,
> scoped_session
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.ext.declarative import declarative_base
> from enum import Enum as eEnum
>
>
> Base = declarative_base()
>
> class WorkerType(eEnum):
> CONTRACTOR = 0
> EMPLOYEE = 1
> PART_TIME = 2
>
> class Employer(Base):
> __tablename__ = "employer"
>
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
> contractor = association_proxy(
> "employer_workers", "worker",
> creator=lambda el:EmployerWorkerAssociation(
> worker=el,
> worker_type=WorkerType.CONTRACTOR
> )
> )
>
> employee = association_proxy(
> "employer_workers", "worker",
> creator=lambda el:EmployerWorkerAssociation(
> worker=el,
> worker_type=WorkerType.EMPLOYEE
> )
> )
>
> part_time = association_proxy(
> "employer_workers", "worker",
> creator=lambda el:EmployerWorkerAssociation(
> worker=el,
> worker_type=WorkerType.PART_TIME
> )
> )
>
>
> class EmployerWorkerAssociation(Base):
> __tablename__ = "employer_to_worker"
>
> employer_id = Column(ForeignKey("employer.id"), primary_key=True)
> worker_id = Column(ForeignKey("worker.id"), primary_key=True)
> worker_type = Column(Enum(WorkerType), primary_key=True)
>
> employer = relationship(
> "Employer",
> backref=backref("employer_workers", cascade="all, delete-orphan")
> )
>
> worker = relationship(
> "Worker",
> backref=backref("worker_employers")
> )
>
> class Worker(Base):
> __tablename__ = "worker"
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
>
>
> engine = create_engine("sqlite://")
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> session = scoped_session(Session)
>
> e1 = Employer(name="The Company")
> session.add(e1)
> session.commit()
>
> w1 = Worker(name="The Programmer")
> session.add(w1)
> session.commit()
>
> e1.contractor.append(w1)
> session.add(e1)
> 44,0-1
> 81%
> session.commit()
>
> # I'm expecting contractor=1 employee=0 part_time=0
> print(f"Contractors: {len(e1.contractor)}")
> print(f"Employees : {len(e1.employee)}")
> print(f"Part Timers: {len(e1.part_time)}")
>
> # There shouldn't be anyone 'PART_TIME'
> for worker in e1.part_time:
> print(f"{worker.id} {worker.name}")
>
>
>
> Thank you for your time.
>
> --
> 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.
