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<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.