Many to Many Fields

[dantownsend]

Some ORMs don't support M2M fields (e.g. Peewee), as it involves the ORM creating extra tables under the hood. It adds quite a bit of magic.

On the other side, some ORMs (such as Django's) have M2M fields. By having M2M fields explicitly supported in the ORM, it is beneficial for tooling such as the admin, as custom widgets can be displayed.

Currently, I just create a joining table explicitly:

class Post(Table):
    title = Varchar(length=255, unique=True)
    content = Text(default="")
    description = Text(default="")
    posted_on = Timestamp(null=True)
    created_on = Timestamp(default=DatetimeDefault.now)
    slug = Varchar(length=512, null=True)
    draft = Boolean(null=True)

    async def categories(self):
        return (
            await CategoryToPost.select(CategoryToPost.category.name)
            .where(post=self.id)
            .run()
        )

    @classmethod
    def get_readable(cls):
        return Readable(template="%s", columns=[cls.title])


class Category(Table):
    name = Varchar(length=150, null=True)

    @classmethod
    def get_readable(cls):
        return Readable(template="%s", columns=[cls.name])


class CategoryToPost(Table):
    category = ForeignKey(Category)
    post = ForeignKey(Post)

    @classmethod
    def get_readable(cls):
        return Readable(
            template="%s - %s", columns=[cls.category.name, cls.post.title]
        )

I'm interested to here people's thoughts on M2M fields.

[sinisaos]

@dantownsend I haven’t seen so far that we’ve had Discussions. That's great.
For M2M fields, people are probably biased. I think your way is a really good example that we don’t need M2M fields, and I like that way. Simple and just like in SQL. But probably some people think the exact opposite.

[sinisaos]

@dantownsend Perhaps it good idea to add M2M field, because each new feature is good for end users, and then each user can choose whether to use that feature or not.

[maxandix]

The cleaner alternative is to add left_join and inner_join statements like this:

categories = await Post.select(
    Post.id,
    Category.id,
    Category.name,
    Category.group_id
).left_join(
    CategoryToPost
).on(
    Post.id == CategoryToPost.post
).left_join(
    Category
).on(
    CategoryToPost.category == Category.id
).where(
    Post.id == some_id
).run()

[dantownsend]

@maxandix Yeah, Piccolo doesn't expose join syntax directly at the moment (similarly to Django). It makes the syntax nice and compact for common use cases:

await Post.select(Post.title, Post.author.name)

I need to have a think - maybe it does make sense to have an explicit join method for certain use cases.

[maxandix]

We don't need one more Django ORM 😉 Let's stick to be SQL-like ORM. Explicit join is powerful tool which will make a lot of use cases are possible and will reduce number of queries to DB.

[dantownsend]

@maxandix Fair point - I'm definitely considering it. Piccolo is very SQL-like in general. The main bit of magic is currently around joins as it can become very verbose otherwise, but there should be a way to explicitly specify joins. I just haven't fully thought it through yet.

[dantownsend]

One thing I do like about Django's M2M field, is it provides a widget like this in the admin:

I'd like to do something similar to this in the Piccolo admin.

[Fingel]

I like Django's approach: If you have a really simple M2M relationship that just requires a table with 2 foreign keys, it creates it for you. Nobody really wants to have to do that manually.

However, it also allows you to define a through model to define a M2M with extra fields on it.

This way you still get the nice autogenerated accessors (not needing to implement post.categories in your example) but you gain the flexibility of adding extra fields to your join table.

[pestrickland]

Hi everyone,

Sorry for digging this old discussion up but I have a question that you can hopefully help me with. I understand the concept of creating the joining table as given in the example at the top, and the categories() method makes sense too. Where I'm struggling though is how you would then use that method. If I wanted to generate a JSON output of post content for example, how could I go about including the post's related categories in that output?

Thanks for all the work with Piccolo so far; I'm really impressed with how intuitive so much of it is.

[dantownsend]

Hi @pestrickland.

If it's just categories, you might consider using an Array column instead.

class Post(Table):
    title = Varchar(length=255, unique=True)
    categories = Array(base_column=Varchar())

That will work nicely with Piccolo Admin too.

However, using the example schema above with the joining table, you can do this:

Returning a single post

if you're just returning a single Post and its categories, you can do this:

post = await Post.objects().get(Post.id == some_id)
# Using the custom method we defined in the example above:
categories = await post.categories()
response = post.to_dict()
response['categories'] = categories
return response

Lots of posts

If you need to return loads of posts with their categories, the most efficient way is to splice them together in Python.

Here's a gist showing how.

I hope that helps.

[sinisaos]

@dantownsend Another option would be to slightly change the categories method to this.

class Post(Table):
  title = Varchar(length=255, unique=True)
  content = Text(default="")
  description = Text(default="")
  posted_on = Timestamp(null=True)
  created_on = Timestamp(default=DatetimeDefault.now)
  slug = Varchar(length=512, null=True)
  draft = Boolean(null=True)

  async def categories(self):
      return await CategoryToPost.select(
          CategoryToPost.category.name, CategoryToPost.post.id
      ).run()

and then we can use that method like this

# example in FastAPI
@app.get("/posts/")
async def posts():
  categories = await Post().categories()
  posts = await Post.select().run()
  for post in posts:
      post["categories"] = [
          item["category.name"]
          for item in categories
          if item["post.id"] == post["id"]
      ]
  return posts

[dantownsend]

@sinisaos You're right - that also works, and is a bit easier to understand.

[pestrickland]

@dantownsend @sinisaos Thank you both for getting back so quickly. Lots of good information there.

[dantownsend]

We now have proper M2M support in Piccolo:

https://piccolo-orm.readthedocs.io/en/latest/piccolo/schema/m2m.html