Is it possible in Pony ORM to add extra attributes to the intermediate table of a many-to-many relationship?

Question

In Pony ORM, it is possible to ‘automatically’ create a many-to-many relationship. For example, from the documentation (for version 0.6, emphasis mine):

In order to create many-to-many relationship you need to define both ends of the relationship as Set attributes:

class Product(db.Entity):
    tags = Set("Tag")

class Tag(db.Entity):
    products = Set(Product)

In order to implement this relationship in the database, Pony will create an intermediate table. This is a well known solution which allows you to have many-to-many relationships in relational databases.

Is it possible to create an extra attribute (column) in the automatically created intermediate table, so not just foreign keys to ‘Product’ and ‘Tag’, but, e.g., also a timestamp?

If yes, how?

If not, I guess I'll have to create the intermediate table explicitly. Can I in that case still use the nice Set-attribute definition (perhaps to the intermediate table, indicating the attribute of interest)?

Answer 1

Currently it is necessary to define explicit entity, like this:

class Product(db.Entity):
    name = Required(str)
    tags = Set("ProductTag")

class Tag(db.Entity):
    name = Required(str, unique=True)
    products = Set("ProductTag")

class ProductTag(db.Entity):
    product = Required(Product)
    tag = Required(Tag)
    PrimaryKey(product, tag)
    timestamp = Required(datetime, default=datetime.now)

Pony does not support virtual Set attributes like through in Django, but we plan to add them in the future. Right now you need to work with intermediate table explicitly.

Adding a tag to a product

p1 = Product[1]
tag1 = Tag.get(name='smartphones')

p1.tags.create(tag=tag1)
# or:
ProductTag(product=p1, tag=tag1)

Removing a tag from a product:

ProductTag[p1, tag1].delete()

Checking if a product has a specific tag:

ProductTag.get(product=p1, tag=tag1) is not None

Also, Pony support the concept of attribute lifting. That means that in Pony any collection attribute has all attributes of its items. The value of a such collection attribute is a collection of all values for individual items. For example, in order to get all tags for specific product, you can write:

p1.tags.tag

The p1.tags expression returns a collection of ProductTag items. Each ProductTag object has tag property which points to a specific tag object. So p1.tags.tag returns a collection of all Tag objects linked with a specific Product object.

It is possible to use attribute lifting inside queries. For example, in order to found all products with the tag smartphones you can write the following query:

select(p for p in Product if 'smartphones' in p.tags.tag.name)

Here, p.tags is a colection of ProductTag objects, p.tags.tag is a collection of Tag objects, and p.tags.tag.name is a collection of tag names. The query above is a syntactic sugar for the following query:

select(p for p in Product if 'smartphones' in select(item.tag.name for item in p.tags))

Also, the query can be rewritten as:

select(pt.product for pt in ProductTag if pt.tag.name == 'smartphones')