Hello,
Here's my simple model (For simplification, consider Face as a Billboard) :
+-----+ +-----------+ +----------+
|Face |<...... |Campaign | ...>|TimePeriod|
+-----+ . +-----------+ . +----------+
|code | . |time_period|.... |start_time|
+-----+ . +-----------+ +----------+
....|faces | |end_time |
+-----------+ +----------+
One way to read this model is : A campaign can book multiple faces during a
certain period of time.
What I want to do is get all the available Faces for a given period of time, to
see what faces can I book for a new campaign that longs for that particular
period of time. I would typically have a Face.get_available(time_period) class
method that does the job. This method would look for all the faces that don't
have an ongoing booking. My question is : how to write such a method ?
Here's how I figured it out (couldn't get it to work) :
class Face(Entity):
using_options(tablename="faces")
@classmethod
def get_available(self,time_period):
"""
Return faces that are not booked (not in any campaign) during the given
time_period.
"""
# start_date_cond = TimePeriod.start_date > time_period.end_date
# end_date_cond = TimePeriod.end_date < time_period.start_date
# available_periods =
Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)
# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :
# L1
0-----------------------------------------------
# L2 ------------------------------------------|
# L3
0--------------------[--------------------]-------------------------->
# L3 represents the desired period (passed as argument) going from "["
to "]"
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1
# of course, end date must be after start date...
# Anyway you do it, your face isn't available for the period of time in
L3.
start_date_cond = TimePeriod.start_date <= time_period.end_date
end_date_cond = TimePeriod.end_date >= time_period.start_date
unavailable_periods =
Campaign.time_period.has(and_(start_date_cond,end_date_cond))
# I am not sure about what follows...
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces
return Face.query.filter(filter_cond).outerjoin(join_clause).all()
This code returns only faces that have already been booked before or have a
future booking, and are free for the moment. But faces with no bookings at all
are not returned. This may be due to an incorrect outerjoin ? (I also tried a
simple join with no success)
Here's the generated sql for one query :
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
SELECT faces.id AS faces_id
FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS
campaigns_faces__faces_1 ON campaigns.id =
campaigns_faces__faces_1.campaigns_id
LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id
WHERE NOT (EXISTS (SELECT 1
FROM time_periods
WHERE campaigns.time_period_id = time_periods.id
AND time_periods.start_date <= %(start_date_1)s
AND time_periods.end_date >= %(end_date_1)s))
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
{'start_date_1': datetime.date(2010, 10, 30), 'end_date_1': datetime.date(2010,
10, 20)}
[<Face id=1 at 0x932218c >]
Any help would be very appreciated.
Y.Chaouche
PS : and please, don't give me that lame "it's elixir" excuse. The question is
about how to construct the proper query for the desired operation in a
sqlalchemy way. Elixir is only another Declarative approach + mapping, that's
it.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
# ORM
from sqlalchemy.sql.expression import *
from sqlalchemy.orm import reconstructor
from elixir import *
# stdlib
from datetime import date
import sys
class TimePeriod(Entity):
"""
"""
using_options(tablename="time_periods")
start_date = Field(Date())
end_date = Field(Date())
name = Field(Unicode())
class Campaign(Entity):
using_options(tablename="campaigns")
time_period = ManyToOne("TimePeriod")
faces = ManyToMany("Face")
class Face(Entity):
using_options(tablename="faces")
@classmethod
def get_available_faces(self,time_period):
"""
Return faces that are not booked during the given time_period.
"""
# start_date_cond = TimePeriod.start_date > time_period.end_date
# end_date_cond = TimePeriod.end_date < time_period.start_date
# available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond))
start_date_cond = TimePeriod.start_date <= time_period.end_date
end_date_cond = TimePeriod.end_date >= time_period.start_date
unavailable_periods = Campaign.time_period.has(and_(start_date_cond,end_date_cond))
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces
return Face.query.filter(filter_cond).outerjoin(join_clause).all()
class DB:
def __init__(self,echo=False):
self.metadata = metadata
self.metadata.bind = "postgresql engine's url"
self.metadata.bind.echo = echo
self.session = session
self.session.bind = metadata.bind
# Use elixir's entities collection
setup_all()
self.drop_all()
create_all()
self.metadata.create_all()
self.session.commit()
def drop_all(self):
"""
"""
for table_name in metadata.tables.keys():
self.session.execute("DROP TABLE IF EXISTS %s CASCADE" % table_name)
self.commit()
def commit(self):
self.session.commit()
def flush(self):
self.session.flush()
echo = len(sys.argv) > 1 and sys.argv[1] != "False" or False
db = DB(echo=echo)
start_date1 = date(year=2010,month=10,day=1)
end_date1 = date(year=2010,month=10,day=15)
start_date2 = date(year=2010,month=10,day=10)
end_date2 = date(year=2010,month=10,day=19)
start_date3 = date(year=2010,month=10,day=20)
end_date3 = date(year=2010,month=10,day=30)
face1 = Face()
face2 = Face()
face3 = Face()
tp1 = TimePeriod(start_date = start_date1,end_date = end_date1)
tp2 = TimePeriod(start_date = start_date2,end_date = end_date2)
tp3 = TimePeriod(start_date = start_date3,end_date = end_date3)
campaign1 = Campaign(time_period=tp1)
campaign1.faces.append(face1)
db.commit()
print Face.query.all()
print Campaign.query.all()
print TimePeriod.query.all()
print "*"*40
print Face.get_available_faces(tp2)
print Face.get_available_faces(tp3)
