CODEWITHSUNDEEP
database-designmany-to-manyrelational-databaserelationshipweb2py
Athelionas
Athelionas

Reputation: 181

Many-to-many relationship in web2py

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!

Upvotes: 3

Views: 2820

Answers (1)

Anthony
Anthony

Reputation: 25536

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.

Upvotes: 4

Related Questions