Проблема, которую вы здесь решаете, чтобы решить ее как можно элегантнее, использует очень продвинутые методы SQLAlchemy, поэтому я знаю, что вы новичок, но этот ответ покажет вам весь путь до конца. . Тем не менее, решение такой проблемы требует прохождения по одному шагу за раз, и вы можете получить желаемый ответ разными способами по мере прохождения.
Прежде чем вы приступите к гибридизации этого или чего-то еще, вам нужно подумать о SQL. Как мы можем запросить Time.cost для произвольной серии строк? Мы можем четко связать Time с Person, потому что у нас есть простой внешний ключ. Но связать Time с Payrate с помощью этой конкретной схемы сложно, потому что Time связывается с Payrate не только через person_id, но и через workon — в SQL мы бы присоединились к этому проще всего, используя «time.person_id = person.id AND time. работал МЕЖДУ payrate.start_date И payrate.end_date". Но у вас здесь нет "end_date", а это значит, что мы должны получить и это. Этот вывод - самая сложная часть, поэтому то, что я придумал, начинается так (я написал имена ваших столбцов в нижнем регистре):
SELECT payrate.person_id, payrate.hourly, payrate.starting, ending.ending
FROM payrate LEFT OUTER JOIN
(SELECT pa1.payrate_id, MIN(pa2.starting) as ending FROM payrate AS pa1
JOIN payrate AS pa2 ON pa1.person_id = pa2.person_id AND pa2.starting > pa1.starting
GROUP BY pa1.payrate_id
) AS ending ON payrate.payrate_id=ending.payrate_id
Могут быть и другие способы получить это, но это то, что я придумал - другие способы почти наверняка будут иметь что-то подобное (например, подзапросы, соединения).
Таким образом, с начальной/конечной ставкой оплаты мы можем понять, как будет выглядеть запрос. Мы хотим использовать BETWEEN, чтобы сопоставить запись времени с диапазоном дат, но последняя запись о ставке будет иметь NULL для «конечной» даты, поэтому один из способов обойти это — использовать COALESCE против очень высокой даты (другой — использовать условные выражения):
SELECT *, entry.hours * payrate_derived.hourly
FROM entry
JOIN
(SELECT payrate.person_id, payrate.hourly, payrate.starting, ending.ending
FROM payrate LEFT OUTER JOIN
(SELECT pa1.payrate_id, MIN(pa2.starting) as ending FROM payrate AS pa1
JOIN payrate AS pa2 ON pa1.person_id = pa2.person_id AND pa2.starting > pa1.starting
GROUP BY pa1.payrate_id
) AS ending ON payrate.payrate_id=ending.payrate_id) as payrate_derived
ON entry.workedon BETWEEN payrate_derived.starting AND COALESCE(payrate_derived.ending, "9999-12-31")
AND entry.person_id=payrate_derived.person_id
ORDER BY entry.person_id, entry.workedon
Теперь то, что @hybrid может сделать для вас в SQLAlchemy при запуске на уровне выражения SQL, это просто часть «entry.hours * payrate_derived.hourly», вот и все. Все JOIN и тому подобное вам нужно будет предоставить гибриду извне.
Итак, нам нужно вставить этот большой подзапрос в это:
class Time(...):
@hybrid_property
def cost(self):
# ....
@cost.expression
def cost(cls):
return cls.hours * <SOMETHING>.hourly
Итак, давайте разберемся, что такое <SOMETHING>. Создайте этот SELECT как объект:
from sqlalchemy.orm import aliased, join, outerjoin
from sqlalchemy import and_, func
pa1 = aliased(Payrate)
pa2 = aliased(Payrate)
ending = select([pa1.payrate_id, func.min(pa2.starting).label('ending')]).\
select_from(join(pa1, pa2, and_(pa1.person_id == pa2.person_id, pa2.starting > pa1.starting))).\
group_by(pa1.payrate_id).alias()
payrate_derived = select([Payrate.person_id, Payrate.hourly, Payrate.starting, ending.c.ending]).\
select_from(outerjoin(Payrate, ending, Payrate.payrate_id == ending.c.payrate_id)).alias()
Гибрид cost() со стороны выражения должен ссылаться на payrate_derived (мы займемся python через минуту):
class Time(...):
@hybrid_property
def cost(self):
# ....
@cost.expression
def cost(cls):
return cls.hours * payrate_derived.c.hourly
Затем, чтобы использовать наш гибрид cost(), он должен быть в контексте запроса, в котором есть это соединение. Обратите внимание, что здесь мы используем datetime.date.max Python, чтобы получить эту максимальную дату (удобно!):
print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
select_from(Time).\
join(Time.person).\
join(payrate_derived,
and_(
payrate_derived.c.person_id == Time.person_id,
Time.workedon.between(
payrate_derived.c.starting,
func.coalesce(
payrate_derived.c.ending,
datetime.date.max
)
)
)
).\
all()
Так что это объединение большое и неуклюжее, и нам нужно будет делать это часто, не говоря уже о том, что нам нужно будет загружать ту же коллекцию в Python, когда мы будем делать наш гибрид в Python. Мы можем сопоставить его с помощью relationship(), что означает, что мы должны настроить пользовательские условия соединения, но также нам нужно фактически сопоставить этот подзапрос, используя менее известную технику, называемую неосновным сопоставителем. Непервичный преобразователь дает вам способ сопоставить класс с некоторой произвольной таблицей или конструкцией SELECT только для целей выбора строк. Обычно нам никогда не нужно использовать это, потому что Query уже позволяет нам запрашивать произвольные столбцы и подзапросы, но чтобы получить его из relationship(), ему нужно сопоставление. Для сопоставления необходимо определить первичный ключ, а для отношения также необходимо знать, какая сторона отношения является «внешней». Это самая продвинутая часть здесь, и в данном случае она работает так:
from sqlalchemy.orm import mapper, relationship, foreign
payrate_derived_mapping = mapper(Payrate, payrate_derived, non_primary=True,
primary_key=[
payrate_derived.c.person_id,
payrate_derived.c.starting
])
Time.payrate = relationship(
payrate_derived_mapping,
viewonly=True,
uselist=False,
primaryjoin=and_(
payrate_derived.c.person_id == foreign(Time.person_id),
Time.workedon.between(
payrate_derived.c.starting,
func.coalesce(
payrate_derived.c.ending,
datetime.date.max
)
)
)
)
Так что это последнее, что мы должны были увидеть об этом объединении. Теперь мы можем выполнить наш запрос раньше:
print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
select_from(Time).\
join(Time.person).\
join(Time.payrate).\
all()
и, наконец, мы можем подключить наше новое отношение payrate к гибриду на уровне Python:
class Time(Base):
# ...
@hybrid_property
def cost(self):
return self.hours * self.payrate.hourly
@cost.expression
def cost(cls):
return cls.hours * payrate_derived.c.hourly
Решение, которое мы здесь нашли, потребовало много усилий, но, по крайней мере, самая сложная часть, это картирование заработной платы, полностью находится только в одном месте, и нам никогда не нужно смотреть на него снова.
Вот полный рабочий пример:
from sqlalchemy import create_engine, Column, Integer, ForeignKey, Date, \
UniqueConstraint, select, func, and_, String
from sqlalchemy.orm import join, outerjoin, relationship, Session, \
aliased, mapper, foreign
from sqlalchemy.ext.declarative import declarative_base
import datetime
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
person_id = Column(Integer, primary_key=True)
name = Column(String(30), unique=True)
class Payrate(Base):
__tablename__ = 'payrate'
payrate_id = Column(Integer, primary_key=True)
person_id = Column(Integer, ForeignKey('person.person_id'))
hourly = Column(Integer)
starting = Column(Date)
person = relationship("Person")
__tableargs__ =(UniqueConstraint('person_id', 'starting',
name='uc_peron_starting'))
class Time(Base):
__tablename__ = 'entry'
entry_id = Column(Integer, primary_key=True)
person_id = Column(Integer, ForeignKey('person.person_id'))
workedon = Column(Date)
hours = Column(Integer)
person = relationship("Person")
@hybrid_property
def cost(self):
return self.hours * self.payrate.hourly
@cost.expression
def cost(cls):
return cls.hours * payrate_derived.c.hourly
pa1 = aliased(Payrate)
pa2 = aliased(Payrate)
ending = select([pa1.payrate_id, func.min(pa2.starting).label('ending')]).\
select_from(join(pa1, pa2, and_(
pa1.person_id == pa2.person_id,
pa2.starting > pa1.starting))).\
group_by(pa1.payrate_id).alias()
payrate_derived = select([Payrate.person_id, Payrate.hourly, Payrate.starting, ending.c.ending]).\
select_from(outerjoin(Payrate, ending, Payrate.payrate_id == ending.c.payrate_id)).alias()
payrate_derived_mapping = mapper(Payrate, payrate_derived, non_primary=True,
primary_key=[
payrate_derived.c.person_id,
payrate_derived.c.starting
])
Time.payrate = relationship(
payrate_derived_mapping,
viewonly=True,
uselist=False,
primaryjoin=and_(
payrate_derived.c.person_id == foreign(Time.person_id),
Time.workedon.between(
payrate_derived.c.starting,
func.coalesce(
payrate_derived.c.ending,
datetime.date.max
)
)
)
)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=False)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e)
p1 = Person(name='p1')
session.add(p1)
session.add_all([
Payrate(hourly=10, starting=datetime.date(2013, 5, 17), person=p1),
Payrate(hourly=15, starting=datetime.date(2013, 5, 25), person=p1),
Payrate(hourly=20, starting=datetime.date(2013, 6, 10), person=p1),
])
session.add_all([
Time(person=p1, workedon=datetime.date(2013, 5, 19), hours=10),
Time(person=p1, workedon=datetime.date(2013, 5, 27), hours=5),
Time(person=p1, workedon=datetime.date(2013, 5, 30), hours=5),
Time(person=p1, workedon=datetime.date(2013, 6, 18), hours=12),
])
session.commit()
print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
select_from(Time).\
join(Time.person).\
join(Time.payrate).\
all()
for time in session.query(Time):
print time.person.name, time.workedon, time.hours, time.payrate.hourly, time.cost
Выходные данные (первая строка — совокупная версия, остальные — для каждого объекта):
[(u'p1', datetime.date(2013, 5, 19), 10, 100), (u'p1', datetime.date(2013, 5, 27), 5, 75), (u'p1', datetime.date(2013, 5, 30), 5, 75), (u'p1', datetime.date(2013, 6, 18), 12, 240)]
p1 2013-05-19 10 10 100
p1 2013-05-27 5 15 75
p1 2013-05-30 5 15 75
p1 2013-06-18 12 20 240
person
zzzeek
schedule
27.06.2013
