I'm trying to create a relationship between two tables, but filtered based
on information from an association proxy. This seems to me a bit like a
relationship to a non-primary mapper
(http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper).
However, I'm a little bit stuck on how to do this and am hoping I might be
able to get some help here?
I have the following schema:
Employees: one to many with EmployeesServices as an (bi-directional)
association proxy with Services; also one to many with Accounts.
Services: one to many with EmployeeServices as an (bi-directional)
association proxy with Employees. also one to many with Accounts.
Accounts: many to one with Employees. Also many to one with Services.
I need to be able to achieve:
1. For all/individual employees, list their services, and vice versa.
2. For all/individual services, list their accounts, and vice versa.
3. For all/individual employees, list their services and accounts that are
associated with the employee.
The below model achieves both 1 and 2 (employees.services and
services.employees respectively) and I do not want to lose this
functionality. I can partially achieve 3 through employees.accounts, but
this doesn't get me the service the account is associated with. Currently,
this model gets me all services associated with an employee but then all
accounts associated with the service irrespective of the employee. I
effectively want the relationship to be employees.services.accounts and it
should only return the accounts for the parent employee (I still need the
service to be available in the query result too).
Here's a breakdown of the models I am using (I am happy to change these if
need be, as I'm still in early stages of developing).
*Employee:*
class Employee(ResourceMixin, db.Model):
__tablename__ = 'employees'
employee_id = db.Column(db.String(10), primary_key=True)
services = association_proxy('employee_services', 'service', creator=
lambda srv: EmployeeService(service=srv))
accounts = db.relationship('Account', lazy='select', backref=db.backref(
'employee', lazy='joined'))
*EmployeeService:*
class EmployeeService(ResourceMixin, db.Model):
__tablename__ = 'employees_services'
employee_id = db.Column(db.String(10), db.ForeignKey(
'employees.employee_id'), primary_key=True)
service_id = db.Column(db.Integer, db.ForeignKey('services.id'),
primary_key=True)
employee = db.relationship('Employee', backref=db.backref(
'employee_services', cascade='all, delete-orphan'))
service = db.relationship('Service', backref=db.backref(
'service_employees', cascade='all, delete-orphan'))
*Service:*
class Service(ResourceMixin, db.Model):
__tablename__ = 'services'
id = db.Column(db.Integer, primary_key=True)
employees = association_proxy('service_employees', 'employee', creator=
lambda emp: EmployeeService(employee=emp))
accounts = db.relationship('Account', lazy='select', backref=db.backref(
'service', lazy='joined'))
*Account:*
class Account(ResourceMixin, db.Model):
__tablename__ = 'accounts'
id = db.Column(db.Integer, primary_key=True)
employee_id = db.Column(db.String(10), db.ForeignKey(
'employees.employee_id'), index=True, nullable=False)
service_id = db.Column(db.Integer, db.ForeignKey('services.id'), index=
True, nullable=False)
Is a relationship with a non-primary mapper the correct way to go in this
case? I'm struggling to get my head around how this would be implemented an
would appreciate any help that can be given.
Thanks,
Ash
--
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.
