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) 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. > > > -- > 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<sqlalchemy%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<sqlalchemy%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<sqlalchemy%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. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.