Support for many-to-many relationships #40

Activity

1. florimondmanca commented on May 10, 2019

   

   florimondmanca

   on May 10, 2019

   Last edited by florimondmanca

   ContributorAuthor

   More actions

   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

   👍React with 👍8Reacted by Fern, Kim Christie, Jearel Alcantara, Wow, Yevhenii Kovalchuk, Luke Branch, Yacht Shaver and Mahmudul Hasan
2. florimondmanca

   changed the title [-]Support for many-to-many relationship[/-] [+]Support for many-to-many relationships[/+] on May 10, 2019
3. florimondmanca commented on May 31, 2019

   

   florimondmanca

   on May 31, 2019

   Last edited by florimondmanca

   ContributorAuthor

   More actions

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

   😕React with 😕1Reacted by Mahmudul Hasan
4. aminalaee

   added

   featureNew feature or requestNew feature or request

   on Sep 17, 2021