Re: [sqlalchemy] Checking the availablity of a booked Item
Here is an example of how I use outerjoin to perform a similar query. class Survey, def get_apps(self): Get all persons who are over 18 and do not have an appointment but have this survey. qry = Person.query.filter(Person.age 18) qry = qry.outerjoin((Appointment, (Appointment.id_person == Person.id) (Appointment.id_survey == self.id))). \ filter(Appointment.id == None) #: Or != None, depending on the type of join. #: I go and append a few more outer joins here as well,# but they look exactly the same just different tables. The syntax of the outerjoin arguments is a tuple containing (Table to join to, whereclause to join on). Hopefully this helps you. Basically, I just look at the coding horror site for the kind of join I want to perform, and modify the syntax above to make it match the SQL. -- Thadeus On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.comwrote: Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On *Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com* wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com http://mc/compose?to=yacinechaou...@yahoo.comwrote: 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 yacinechaou...@yahoo.comhttp://mc/compose?to=yacinechaou...@yahoo.com * wrote: From: chaouche yacine yacinechaou...@yahoo.comhttp://mc/compose?to=yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.comhttp://mc/compose?to=sqlalch...@googlegroups.com 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 thade...@thadeusb.comhttp://mc/compose?to=thade...@thadeusb.com * wrote: From: Thadeus Burgess thade...@thadeusb.comhttp://mc/compose?to=thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.comhttp://mc/compose?to=sqlalch...@googlegroups.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
Did you try : qry = qry.outerjoin(Appointment.person).filter(Appointment.id == None) ? I have the intuition that sqlalchemy can guess on what foreign keys the join should operate based on how you declared your relations. What about (if Person.appointments exists): qry = qry.outerjoin(Person.appointments).filter(Appointment.id == None) ? I don't think you'd have the same results (join order matters). Y.Chaouche --- On Wed, 10/20/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 20, 2010, 8:27 AM Here is an example of how I use outerjoin to perform a similar query. class Survey, def get_apps(self): Get all persons who are over 18 and do not have an appointment but have this survey. qry = Person.query.filter(Person.age 18) qry = qry.outerjoin((Appointment, (Appointment.id_person == Person.id) (Appointment.id_survey == self.id))). \ filter(Appointment.id == None) #: Or != None, depending on the type of join. #: I go and append a few more outer joins here as well,# but they look exactly the same just different tables. The syntax of the outerjoin arguments is a tuple containing (Table to join to, whereclause to join on). Hopefully this helps you. Basically, I just look at the coding horror site for the kind of join I want to perform, and modify the syntax above to make it match the SQL. -- Thadeus On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com wrote: 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 yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
I'm not reading this super carefully, but typically the best to find X where no related Y is to use NOT EXISTS.With SQLA you can hit this in one step using ~Person.appointments.any(). On Oct 20, 2010, at 12:30 PM, chaouche yacine wrote: Did you try : qry = qry.outerjoin(Appointment.person).filter(Appointment.id == None) ? I have the intuition that sqlalchemy can guess on what foreign keys the join should operate based on how you declared your relations. What about (if Person.appointments exists): qry = qry.outerjoin(Person.appointments).filter(Appointment.id == None) ? I don't think you'd have the same results (join order matters). Y.Chaouche --- On Wed, 10/20/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 20, 2010, 8:27 AM Here is an example of how I use outerjoin to perform a similar query. class Survey, def get_apps(self): Get all persons who are over 18 and do not have an appointment but have this survey. qry = Person.query.filter(Person.age 18) qry = qry.outerjoin((Appointment, (Appointment.id_person == Person.id) (Appointment.id_survey == self.id))). \ filter(Appointment.id == None) #: Or != None, depending on the type of join. #: I go and append a few more outer joins here as well,# but they look exactly the same just different tables. The syntax of the outerjoin arguments is a tuple containing (Table to join to, whereclause to join on). Hopefully this helps you. Basically, I just look at the coding horror site for the kind of join I want to perform, and modify the syntax above to make it match the SQL. -- Thadeus On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com wrote: 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_datetime_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
Re: [sqlalchemy] Checking the availablity of a booked Item
Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com wrote: 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 yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 yacinechaou...@yahoo.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
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 yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 yacinechaou...@yahoo.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
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 yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 yacinechaou...@yahoo.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.comwrote: 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 yacinechaou...@yahoo.com* wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 thade...@thadeusb.com* wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com 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 yacinechaou...@yahoo.com 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
Re: [sqlalchemy] Checking the availablity of a booked Item
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
yacinechaou...@yahoo.comwrote:
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
Re: [sqlalchemy] Checking the availablity of a booked Item
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 thade...@thadeusb.com wrote:
From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
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 yacinechaou...@yahoo.com
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
[sqlalchemy] Checking the availablity of a booked Item
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_datetime_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.ormimport reconstructor
from elixirimport *
# 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):
