[SQLObject] How to use IN() query

[Stewart M. <ste...@gm...>]

I've searched through the archives without finding anything on this topic.
Hopefully this will help someone else...

Summary: to use IN() or related functions, you must search through a list,
not a search result.

Details:
On the SQLobject website, there's an intriguing sentence in the section on
subqueries:
"Available queries are IN(),NOTIN(), EXISTS(), NOTEXISTS(), SOME(), ANY()
and ALL(). The last 3 are used with comparison operators, like this:
"somevalue =3D ANY(Select(...))".

So how would I use IN()?   I guessed (wrongly) that IN() is for selecting
items that are IN the results of another query.  Not! They're for selecting
items that are in a list.

Here's my situation:
I have a Route class with fields including hoodfrom and hoodto (start and
end neighbourhoods for a route). Neighbourhoods can be in one of four
quadrants, but the quadrant information is not in the Route class: there's =
a
separate Neighbourhood class that has fields including name and quadrant.

Let's say I want to select all routes which start in a neighbourhood that's
in the 'SE' quadrant and which end in one in the 'NW' quadrant.

I'm unable to figure out how to build a single query that does this (perhap=
s
some kind of join is needed?), but I can build some individual queries that
get me partly there. The following two queries get me lists of the
neighbourhoods that meet the start and end criteria.
select2 =3D Neighbourhood.select(Neighbourhood.q.quadrant=3D=3D'NE')
select3 =3D Neighbourhood.select(Neighbourhood.q.quadrant=3D=3D'NW')

Now I want to create a query that selects records whose hoodfrom is in the
results of select2 and whose hoodto is in the results of select3. I tried
this:
select =3D Route.select(AND(IN(Route.q.hoodfrom,select2),IN(Route.q.hoodto
,select3)))

It seems to work (at least, it doesn't barf), but when I try to print out
results, there's an error.  To print out results, I tried:
for sel in select:
    print sel.hoodfrom, sel.hoodto

The error message is:
Traceback (most recent call last):
ValueError: Unknown SQL builtin type: <class '
sqlobject.sresults.SelectResults'>
 for <SelectResults at 1194910>

To resolve this problem, I took the results of the first search, select2,
and created a list:
nelist =3D []
for sel in select2:
    nelist.append(sel.name)

And I did something similar for the results of select3, building a nwlist.

Then I re-ran the select query, modifying it to use the lists instead of th=
e
queries:
select =3D Route.select(AND(IN(Route.q.hoodfrom,nelist),IN(Route.q.hoodto
,nwlist)))

To see the results, I did this:
for sel in select:
    print sel.hoodfrom, sel.hoodto

which gave me an output:
Bridgeland Bowness
Bridgeland Mount Pleasant
Bridgeland University of Calgary
Bridgeland Mount Pleasant
Bridgeland Crescent Heights
Bridgeland Hillhurst


cheers,
--
Stewart Midwinter
st...@mi...
ste...@gm...
Skype, GoogleTalk, iChatAV, MSN, Yahoo: midtoad
AIM:midtoad1