Testexample

An example query with Many-Many tables

I have an application with a class like:

class Job (SQLObject):
    ...
    keywords = RelatedJoin ('Keyword')

ie an object holding information about a job which I want to allow the users to be able to add (and remove) keywords. The keyword table is simply:

class Keyword(SQLObject):
    name = StringCol(alternateID=True,notNone=True,length=50)
    jobs = RelatedJoin('Job')
    _defaultOrder = 'name'

The application has a search page (a web form) which works by looking for various <inputs> for each field in the job, creating an SQL statement for each of these and joining these together with "AND " then doing Job.select(sqlstring). Most of the fields are fairly trivial but the keywords are more interesting which is what I'm going to describe here.

There are two operations the user wants to be able to do from the search page:

1. find jobs with any of a list of keywords

2. find jobs with all of a given list of keywords

For pedagogic purposes I'm assuming that input_keys is a list of strings, one for each of the keywords selected on the form. There is also a radio button to decide between AND and OR which is what these cases boil down to.

The first case is relatively simple. If the input was for three keywords it just needs an SQL statment like:

SELECT ..
FROM job, job_keyword
WHERE job.id = job_keyword.job_id and (job_keyword.keyword_id = k1 or
                                      job_keyword.keyword_id = k2 or
                                      job_keyword.keyword_id = k3)

so a simple piece of code like:

sqlstring.append ("job.id = job_keyword.job_id and (%s)"%
                  ' OR '.join(["job_keyword.keyword_id = %d"%Keyword.byName(k).id
                               for k in input_keys]]))
tables.append ('job_keyword')

The second one is more challenging because you need to do multiple joins with the intermediate job_keyword table. So a piece of code like:

qhold = []
for i,k in enumerate(input_keys):
    tabl = 'jk%d'%i
    qhold.append ("job.id = %s.job_id and %s.keyword_id = %d"%
                  (tabl, tabl, Keyword.byName(k).id))
    tables.append ('job_keyword AS %s'%tabl)
q.append ("(%s)"%' AND '.join(qhold))

does the trick. The final search is done by:

Q = ' AND '.join(sqlstring)
items = Job.select(Q,clauseTables=tables)

Notes:

• the main trick, which took me ages to think of, is that the clauseTables list is really a list of expresssions not just table names.

• I didn't need to do the Keyword.byName(k) lookup in the code, it could have been added as another join but I know my keyword table is tiny compared to the number of jobs so I suspect it would be more efficient.