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