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