On Thu, May 31, 2018 at 5:43 AM, Ashley Bye <[email protected]> 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].
> 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.
--
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.
