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:

find jobs with any of a list of keywords

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. You need some SQL along these lines:

SELECT ..
FROM job, job_keyword AS jk0, job_keyword AS jk1, job_keyword AS jk2
WHERE (job.id = jk0.job_id and jk0.keyword_id = k1 AND
       job.id = jk1.job_id and jk1.keyword_id = k2 AND
       job.id = jk2.job_id and jk2.keyword_id = k3)

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)
sqlstring.append ("(%s)"%' AND '.join(qhold))

does the trick. The final search is done by:

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

For illustration here is the record of a live search:

SELECT job.id, job.owner_id, job.deadline, job.contractororder,
       job.oldlocation_id, job.priority, job.num_ports, job.status,
       job.description, job.assignedto_id, job.alerttime,
       job.buildingnumber_id, job.created, job.ordernumber, job.summary,
       job.costs, job.activity
FROM job, job_keyword AS jk2, job_keyword AS jk0, job_keyword AS jk1
WHERE  (job.id = jk0.job_id and jk0.keyword_id = 3 AND
        job.id = jk1.job_id and jk1.keyword_id = 1 AND
        job.id = jk2.job_id and jk2.keyword_id = 2)
ORDER BY 'id' DESC

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.

Manymanyexample

An example query with Many-Many tables