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
<[email protected]>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 <[email protected]>* wrote:
>
>
> From: Thadeus Burgess <[email protected]>
> Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
> To: [email protected]
> 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 <
> [email protected]
> <http://mc/[email protected]>>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
> <[email protected]<http://mc/[email protected]>
> >* wrote:
>
>
> From: chaouche yacine
> <[email protected]<http://mc/[email protected]>
> >
>
> Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
> To:
> [email protected]<http://mc/[email protected]>
> 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
> <[email protected]<http://mc/[email protected]>
> >* wrote:
>
>
> From: Thadeus Burgess
> <[email protected]<http://mc/[email protected]>
> >
> Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
> To:
> [email protected]<http://mc/[email protected]>
> 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 <[email protected]
> > 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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
> [email protected]<http://mc/[email protected]>
> .
> To unsubscribe from this group, send email to
> [email protected]<http://mc/compose?to=sqlalchemy%[email protected]>
> .
> 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
> [email protected]<http://mc/[email protected]>
> .
> To unsubscribe from this group, send email to
> [email protected]<http://mc/compose?to=sqlalchemy%[email protected]>
> .
> 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
> [email protected]<http://mc/[email protected]>
> .
> To unsubscribe from this group, send email to
> [email protected]<http://mc/compose?to=sqlalchemy%[email protected]>
> .
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected]<sqlalchemy%[email protected]>
> .
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected]<sqlalchemy%[email protected]>
> .
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
