[SQLObject] Help on creating efficient many-to-many queries

[Dennis B. <bra...@go...>]

Hi.

I'm using SQLObject with TurboGears and have the following model:

class File(SQLObject):
  name =3D StringCol()
  ...
  directories =3D RelatedJoin("Directory")

class Directory(SQLObject):
  name =3D StringCol()
  ...
  files =3D RelatedJoin("File")

(The reason for this structure is that I consider - for the moment -
file equal if they the same name and size, and directories represents
a list of "mirrors")

Now I want to show all files with all full pathts matching a pattern,
for example:

some/long/path/README
another/path/README

The obvious way would be:
for file in model.File.select(model.File.q.name.startswith("README")):
  for dir in file.directories:
    print dir.name,file.name

However, this takes quite to long (35 seconds with the test database
only containing 20000 files)

In SQL I'd write:

SELECT directory.name,file.name FROM file,directory,directory_file
WHERE file_id=3Dfile.id AND directory.id=3Ddirectory_id AND file.name LIKE
'README%';

and this query only takes around 100ms, so it's around 300 times faster!

Is there a way to create an query like that? I'd rather not hard-code
this query; and I cannot use sqlbuilder since there is no
File.q.directories.

Any suggestions?