Support for many-to-many relationships

[florimondmanca]

Hi,

I've heard Tom mention that "orm doesn't have support for M2M yet" (and indeed it does not currently support M2M), so I figured I'd open an issue since there isn't one yet either.

I will be playing around with potential APIs for M2M in orm, and report back here if I find anything. :-)

[florimondmanca]

I went through the Django M2M documentation and fiddled around to try and see how that could be adapted in an async context.

(Edit: updated with .prefetch_related().)

Definition of an M2M relationship:

import orm
import sqlalchemy
from databases import Database

database = Database("sqlite:///sqlite.db")
metadata = sqlalchemy.MetaData()

class Author(orm.Model):
    __tablename__ = "authors"
    __database__ = database
    __metadata__ = metadata
    first_name = orm.String(max_length=80)
    last_name = orm.String(max_length=80)

class Category(orm.Model):
    __tablename__ = "categories"
    __database__ = database
    __metadata__ = metadata
    name = orm.String(max_length=40)

class PostCategory(orm.Model):
    __tablename__ = "posts_categories"
    __database__ = database
    __metadata__ = metadata

class Post(orm.Model):
    __tablename__ = "posts"
    __database__ = database
    __metadata__ = metadata
    title = orm.String(max_length=200)
    categories = orm.ManyToMany(Category, through=PostCategory)
    author = orm.ForeignKey(Author)

(Note: this proposes to require an explicit through table. This isn't how Django does things (it generates a through table for you by default), but it's probably simpler to implement, more explicit, and makes it clear where to add fields if we want to store extra data about the association.)

Creation and linking of objects:

# orm can do this already.
guido = await Author.objects.create(first_name="Guido", last_name="Van Rossum")
post = await Post.objects.create(title="Hello, M2M", author=guido)
news = await Category.objects.create(name="News")

# Add a category to a post.
await post.categories.add(news)
# or from the other end:
await categories.posts.add(post)  # or `.post_set`, like Django?
# ^ Django would use `.post_set`, but I think we should use
# the related table name — hence `.posts`.

# Creating related object from instance:
tips = await post.categories.create(name="Tips")

Querying:

# tl;dr: `post.categories` exposes the QuerySet API.
post_categories = await post.categories.all()
assert news == await post.categories.get(name="News")
num_posts = await news.posts.count()
posts_about_m2m = await news.posts.filter(title__contains="M2M").all()
posts_about_python = await Post.objects.filter(categories__name="python")

# Traversal of relationships: which categories has Guido contributed to?
await Category.objects.filter(posts__author=guido)
# or:
await Category.objects.filter(posts__author__first_name="Guido")

Prefetching:

# Loads categories and posts (2 queries) and perform the join in Python.
categories = await Category.objects.prefetch_related("posts").all()
# No extra queries needed => no more `await`s required.
for category in categories:
    posts = category.posts
    ...

# Advanced prefetching:
num_categories_with_guido = await Category.objects.prefetch_related(
    orm.Prefetch(
        "posts",
        queryset=Post.objects.filter(author=guido),
    )
).count()

Removal of M2M links:

await post.categories.remove(news)
# or:
await news.posts.remove(post)
# post would also lose 'news' category when running:
await news.delete()

# Remove all related objects:
await post.categories.clear()
assert await post.categories.all().count() == 0

[florimondmanca]

Unfortunately don’t have much bandwidth to start working on this atm — if anyone is feeling lucky, feel free to give it a shot!