On Thursday, 31 May 2018 10:43:47 UTC+1, Ashley Bye wrote: > > 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 >
So I tried the following, based on the relationship to non primary mapper example: j = join(EmployeeService, Account, Account.employee_id == EmployeeService. employee_id).join(Service, Service.id == Account.service_id) Account_via_EmployeeService_Service = mapper(Account, j, non_primary=True, properties={ 'account_id': [j.employees_services.account_id, j.employees_services. service_account_id], 'service_id': j.employees_services.service_id }) Which gives me 'Join' and '_ORMJoin' has no 'employees_services' members. What am I doing wrong? -- 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.