3

I have a table of articles in my web2py project similar to this one:

db.define_table('articles',
                Field('created_on', 'datetime', default = datetime.today(),
                      required = True, notnull = True,
                      requires = [IS_NOT_EMPTY(),
                                  IS_DATETIME(format=T('%Y-%m-%d %H:%M:%S'))]),
                Field('article_en', 'text', required = True,
                      notnull = True, requires = IS_NOT_EMPTY()))

and another one which keeps track of uploaded files:

db.define_table('files',
                Field('filename', 'string', required = True, notnull = True),
                Field('uploaded_data', 'upload', autodelete = True,
                      required = True, notnull = True, requires = IS_NOT_EMPTY()),
                Field('created_on', 'datetime', required = True, notnull = True))

Now I would like to have references for every file an article uses, and references for every article to which a file belongs. I need this so I can easily delete unused files without the danger of messing up articles because of overlooked relationships.

To put it more straightforward the relationship is something very close to this: every article has several files and every file links to different articles.

From what I gathered from the web2py book there is no direct support for many-to-many relationships. In order to solve this issue a simple workaround would be to use an intermediate table dealing with the relations but obviously I am not a database guru so I have little to no idea how I should do it.

Some help would be greatly appreciated!

Athelionas
  • 181
  • 3
  • 12

1 Answers1

4

web2py does support many-to-many relationships. You do this by defining an intermediate table linking the other two tables:

db.define_table('article_files',
    Field('article', db.articles),
    Field('file', db.files))

This is not a workaround but the standard way to handle this type of relation in an RDBMS. Note, web2py uses a database abstraction layer (DAL), not an ORM. An ORM typically would not require you to explicitly define the intermediate table, though it would still create one behind the scenes.

You might also look into using list:reference fields.

Anthony
  • 25,466
  • 3
  • 28
  • 57
  • Thank you very much. This is exactly what I needed. One other question though, is there any benefit to using list:reference instead of the standard method? – Athelionas Jul 05 '12 at 06:27
  • 1
    Depending on what you're doing, list:reference can make some functionality a little easier to program, but it also involves some limitations (e.g., if you delete a given record from the files table, you have to go through all your articles records and delete that record ID from any articles list:reference fields). – Anthony Jul 05 '12 at 21:50
  • In that case I'll just stick to the standard way. It should be more efficient in my use case. Thank you for taking your time. – Athelionas Jul 05 '12 at 23:10
  • Yes, I think that's probably the way to go here. – Anthony Jul 05 '12 at 23:17