Here's the SQL I got :
SELECT face.id AS face_id
FROM face
LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1
ON face.id = face_bookings__booking_faces_1.face_id
LEFT OUTER JOIN booking
ON booking.id = face_bookings__booking_faces_1.booking_id
JOIN time_period ON booking.time_period_id = time_period.id
WHERE
time_period.start_date > %(start_date_1)s
OR
time_period.end_date < %(end_date_1)s
With the following code :
class Booking(BaseModel):
"""
"""
using_options(tablename="booking")
reprattr = "time_period"
faces = ManyToMany("Face")
# A client has one and only one booking per time period
time_period = ManyToOne("TimePeriod")
@classmethod
def get_available_faces(self,time_period):
"""
Return faces that are not booked during the given time_period.
"""
from timeperiod import TimePeriod
from face import Face
start_date_cond = TimePeriod.start_date > time_period.end_date
end_date_cond = TimePeriod.end_date < time_period.start_date
unbooked = or_(start_date_cond,end_date_cond)
# query =
Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
# return query.all()
query = Face.query.filter(unbooked)
#return query.all()
query = query.outerjoin(Face.bookings)
#return query.all()
query = query.join(Booking.time_period)
return query.all()
And still not the expected results (it should return faces with no bookings at
all but it doesen't).
Thanks for any help.
Y.Chaouche
--- On Wed, 10/13/10, chaouche yacine <[email protected]> wrote:
From: chaouche yacine <[email protected]>
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: [email protected]
Date: Wednesday, October 13, 2010, 5:25 AM
Thank you Thadeus, I believe
Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full
outerjoin, or is there another way to do it ?
Y.Chaouche
--- On Wed, 10/13/10, Thadeus Burgess <[email protected]> wrote:
From: Thadeus Burgess <[email protected]>
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: [email protected]
Date: Wednesday, October 13, 2010, 12:04 AM
For outer joins you need a where clause on the joined tables.
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
Using a full outer join should return the expected results.
--
Thadeus
On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine <[email protected]>
wrote:
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
