Fetch and count all records in M2M join table

Using Django Using the ORM
1

I have two tables that are in M2M relationship. I have not created explicit model for the join table.

class CustomUser(AbstractUser):
    class Meta:
        app_label = "users"
        db_table = "users"

class Listing(models.Model):
    bookmarks = models.ManyToManyField(CustomUser, related_name="bookmarked_listings", blank=True)

    class Meta:
        app_label = "listings"
        db_table = "listings"

How can I fetch and count all records in the join table, listing_bookmarks? I don’t mean just getting bookmarks of a listing or bookmarked listings of an user.

2

The reference Listing.bookmarks.through is a reference to the implicitly-created join model for that relationship.

In the general case, you should be able to use Listing.bookmarks.through.objects.count() to get the number of rows and Listing.bookmarks.through.objects.all() to retrieve them.

3

How do I filter exactly by listing ID and user ID? I get Unknown ORM lookup segment when I do this.

Listing.bookmarks.through.objects.filter(listing_id=listing.pk, customuser_id=self.user.pk)

How do I also change customuser_id to user_id in the through table?

{E18A3CB1-8278-40A5-B747-2BAEF29D9C9C}
{E18A3CB1-8278-40A5-B747-2BAEF29D9C9C}1920×1020 115 KB

4

Use the shell and the ORM to tell you the field reference names.

e.g. Listing.bookmarks.through._meta.fields

5 
>>> Listing.bookmarks.through._meta.fields
(<django.db.models.fields.BigAutoField: id>, <django.db.models.fields.related.ForeignKey: listing>, <django.db.models.fields.related.ForeignKey: customuser>)
6

It works for me for every combination I’ve tried.

Please post the complete function (or class) where you are trying this along with the complete error message and traceback from the server console. (Or, if you’re trying this from the Django shell, show the relevent attempt and error.)

7

Full context:

def test__listing__bookmark(self):
        # Arrange
        listing = self.service.create(self.user, self.items, self.terms, self.LISTING_DATA)

        data = dict(self.USER_DATA)
        data["email"] = self.ALT_EMAIL
        data["phone"] = self.ALT_PHONE
        user2 = self.create_user(data)

        # Act
        self.service.bookmark(listing, user2)

        # Assert
        listing_bookmarks = Listing.bookmarks.through
        self.assertGreater(listing_bookmarks.objects.count(), 0)
        self.assertTrue(listing_bookmarks.objects.filter(listing_id=listing.pk, customuser_id=self.user.pk).exists())

Full error:

Unknown ORM lookup segment `listing_id` in `listing_id` for `listings.Listing`. Django ORM Intellisense

Unknown ORM lookup segment `customuser_id` in `customuser_id` for `listings.Listing`.Django ORM Intellisense
8

That looks like an IDE or tooling message and not an actual Django error.

Does the query work? That’s all that really matters.

9

It works in the shell.

10

Right - which means that the error message itself is an error. You can ignore it.

11

If you just need the total number of rows in the automatically created M2M table, you can access the through model Django created for the field:

Listing.bookmarks.through.objects.count()

And if you need the actual join records:

Listing.bookmarks.through.objects.all()

You don’t need to define an explicit model just to query the automatically generated through table. Listing.bookmarks.through gives you that model directly.