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 [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.
