Thanks for this Mike. I've actually changed my model slightly to reduce the complexity, but I'm very grateful for the effort you put into trying to help me out. I'm going to keep this kicking around so I can try and understand what is going on.
On Friday, 1 June 2018 19:54:54 UTC+1, Mike Bayer wrote: > > On Thu, May 31, 2018 at 5:43 AM, Ashley Bye <[email protected] > <javascript:>> 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. > > > I've written out a full model here and gotten to #3 and I don't > understand what this wants. There are two paths from Employee to > Account - there is Account via Account.employee_id, and there is > Account via Account.service_id -> employees_services -> Employee and > it's not clear if these are denormalized versions of the same > information or completely exclusive of each other. > > the join you showed is: > > join(EmployeeService, Account, Account.employee_id == > EmployeeService.employee_id).join(Service, Service.id == > Account.service_id) > > > This looks like you're trying to get the overlap of Accounts that both > point to a particular employee as well as are linked to that same > employee via the EmployeeService->Service table. > > in the mapping you show: > > 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 > }) > > you seem to have taken a guess that a "join" object points directly to > the tables that are embedded inside of it, this is unfortunately not > the case. You can instead access these columns that are table-name > qualified, which unfortunately does not seem to be so clearly > documented anywhere right now, you can see the basic idea in the docs > for join.alias() at > > http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.Join.alias, > > > but more directly you can see it like: > > j = db.join( > EmployeeService, Account, > Account.employee_id == EmployeeService.employee_id).join( > Service, Service.id == Account.service_id) > > import pdb > pdb.set_trace() > > > (Pdb) !j.c.keys() > ['employees_services_employee_id', 'employees_services_service_id', > 'accounts_id', 'accounts_employee_id', 'accounts_service_id', > 'services_id'] > > > It's also not clear what "list their services and accounts" means, > relationship collections only return one kind of object at a time, do > you mean, you want to get Account instances back and then call upon > Account.service for each instance? > > Anyway, heres the mapping it looks like you're intending to do: > > j = db.join( > EmployeeService, Account, > Account.employee_id == EmployeeService.employee_id).join( > Service, Service.id == Account.service_id) > > Account_via_EmployeeService_Service = mapper( > Account, j, > # these are all PK columns we are mapping so make sure we have > equivalent > # PK as in the primary mapper > primary_key=[j.c.accounts_id], > non_primary=True, properties={ > # the goal here is to name the attributes the same as in the > # primary mapper > 'id': [j.c.accounts_id], > 'employee_id': [j.c.employees_services_employee_id, > j.c.accounts_employee_id], > 'service_id': [ > j.c.employees_services_service_id, > j.c.services_id, j.c.accounts_service_id] > } > ) > > Employee.account_via_employeeservice = relationship( > Account_via_EmployeeService_Service, > primaryjoin=( > (Employee.employee_id == > Account_via_EmployeeService_Service.c.employee_id) > ) > ) > > > Below is full test case. The part at the end tries #3, but I've > tried to create a dataset that shows something interesting but I > can't, it is essentially the identical data you get by querying for > Employee.accounts directly. Please work with the below test case to > illustrate the test data that illustrates the condition as well as > what you expect to see. Note the query at the end is: > > SELECT employees.employee_id AS employees_employee_id, accounts.id AS > accounts_id, accounts.employee_id AS accounts_employee_id, > accounts.service_id AS accounts_service_id, employees_1.employee_id AS > employees_1_employee_id, services_1.id AS services_1_id > FROM employees JOIN (employees_services JOIN accounts ON > accounts.employee_id = employees_services.employee_id JOIN services ON > services.id = accounts.service_id) ON employees.employee_id = > employees_services.employee_id LEFT OUTER JOIN employees AS > employees_1 ON employees_1.employee_id = accounts.employee_id LEFT > OUTER JOIN services AS services_1 ON services_1.id = > accounts.service_id > > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.ext.associationproxy import association_proxy > import sqlalchemy as db > > Base = declarative_base() > > > class Employee(Base): > __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 = relationship( > 'Account', lazy='select', > backref=backref('employee', lazy='joined')) > > def __repr__(self): > return "Employee(%s)" % self.employee_id > > > class EmployeeService(Base): > __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 = relationship( > 'Employee', > backref=backref('employee_services', cascade='all, > delete-orphan')) > service = relationship( > 'Service', > backref=backref('service_employees', cascade='all, > delete-orphan')) > > > class Service(Base): > __tablename__ = 'services' > id = db.Column(db.Integer, primary_key=True) > employees = association_proxy( > 'service_employees', 'employee', > creator=lambda emp: EmployeeService(employee=emp)) > accounts = relationship( > 'Account', lazy='select', backref=backref('service', > lazy='joined')) > > > class Account(Base): > __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) > > def __repr__(self): > return "Account(id=%s, employee_id=%s, service_id=%s)" % ( > self.id, self.employee_id, self.service_id) > > j = db.join( > EmployeeService, Account, > Account.employee_id == EmployeeService.employee_id).join( > Service, Service.id == Account.service_id) > > Account_via_EmployeeService_Service = mapper( > Account, j, > primary_key=[j.c.accounts_id], > non_primary=True, properties={ > # the goal here is to name the attributes the same as in the > # primary mapper > 'id': [j.c.accounts_id], > 'employee_id': [j.c.employees_services_employee_id, > j.c.accounts_employee_id], > 'service_id': [ > j.c.employees_services_service_id, > j.c.services_id, j.c.accounts_service_id] > } > ) > > Employee.account_via_employeeservice = relationship( > Account_via_EmployeeService_Service, > primaryjoin=( > (Employee.employee_id == > Account_via_EmployeeService_Service.c.employee_id) > ) > ) > > e = create_engine("sqlite://", echo=True) > Base.metadata.create_all(e) > > > s = Session(e) > a1, a2, a3, a4, a5 = ( > Account(id=1), Account(id=2), Account(id=3), Account(id=4), > Account(id=5) > ) > e1, e2, e3 = ( > Employee(employee_id='e1'), Employee(employee_id='e2'), > Employee(employee_id='e3') > ) > s1, s2, s3 = Service(), Service(), Service() > > e1.accounts = [a1] > e2.accounts = [a2, a3, a4] > e3.accounts = [a5] > > s1.accounts = [a1, a4] > s2.accounts = [a2, a3] > s3.accounts = [a5] > > e1.services = [s1, s2] > e2.services = [s2, s3] > e3.services = [s1, s3] > > > s.add_all([ > e1, e2, e3, s1, s2, s3 > ]) > > s.commit() > > > # 1. For all/individual employees, list their services, and vice versa. > > for e in s.query(Employee): > print(e) > print(e.services) > > for serv in s.query(Service): > print(serv) > print(serv.employees) > > # 2. For all/individual services, list their accounts, and vice versa. > > for e in s.query(Employee): > print(e) > print(e.accounts) > > for acct in s.query(Account): > print(acct) > print(acct.employee) > > > # 3. For all/individual employees, list their services and accounts that > are > # associated with the employee. > > > for emp, acc in s.query(Employee, Account).join( > Employee.account_via_employeeservice): > print(emp, acc) > > # does not seem interesting > assert set(e2.account_via_employeeservice) == set([a2, a3, a4]) > > > > > > > > > > > > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
