Django ORM Filtering Related Objects By Field Value?

Question

I am working on a project that involves a channel/post mechanism. I am currently implementing functionality to "delete" posts by setting a field named "deleted" to a timestamp of when it was deleted. It needs kept for historical significance (last activity) and to track people who are inflammatory (the service is a privilege based portion of the whole project).

models.py

class Post(models.Model):
    deleted = models.DateTimeField(null=True, blank=True)
    created_date = models.DateTimeField(auto_now_add=True)
    updated_date = models.DateTimeField(auto_now_add=True)
    post_text = models.CharField(max_length=1000)
    post_to = models.ForeignKey('Channel', related_name='post_to_channel')
    post_from = models.ForeignKey(User, related_name='user_from_post')

class Channel(models.Model):
    created_date = models.DateTimeField(auto_now_add=True)
    channel_title = models.CharField(max_length=50, unique=True)
    channel_description = models.CharField(max_length=100)
    channel_creator = models.ForeignKey(User)
    channel_private = models.BooleanField(default=False)
    channel_users = models.ManyToManyField(User, through='Membership', related_name='channels', blank=True)
    channel_posts = models.ManyToManyField(Post, related_name='channel_post', blank=True)
    initial_invites = models.ForeignKey(User, null=True, blank=True, related_name='initial_invites')
    objects = models.Manager()

class Membership(models.Model):
    channel = models.ForeignKey(Channel)
    channel_user = models.ForeignKey(User, related_name='channel_membership')
    join_date = models.DateTimeField(auto_now_add=True)

I have an endpoint which updates the "deleted" field of the "Post" object to be a timestamp from null. How do I then avoid returning all Posts which have a timestamp when queried at the "Channel" level, having the Posts be loaded through their relationship and a separate serializer? Basically, I need to be able to query "Channel" and get a list of channels I am a part of, then through their relationship of ManyToMany with "Post" load all related Posts excluding those that have a value in the "deleted" field. Is this possible without calling the ORM for each channel, subsequently filtering out the deleted posts? To be clear, the "Channel" should still come through if there's a deleted post, only the "Post" itself should be hidden.

If I didn't provide enough information, just let me know what you need to see.

If the answer is that this is not possible without repetitious ORM calls, suggesting other options would be appreciated, including weighing in on the option to "archive" the posts to a duplicate model, without hooks to be called during these situations and deleting the original.

Edit 1: Add a property as noted in the chosen answer, however it can't see self.channel_posts. models.py

class Channel(models.Model):
    created_date = models.DateTimeField(auto_now_add=True)
    channel_title = models.CharField(max_length=50, unique=True)
    channel_description = models.CharField(max_length=100)
    channel_creator = models.ForeignKey(User)
    channel_private = models.BooleanField(default=False)
    channel_users = models.ManyToManyField(User, through='Membership', related_name='channels', blank=True)
    channel_posts = models.ManyToManyField(Post, related_name='channel_post', blank=True)
    initial_invites = models.ForeignKey(User, null=True, blank=True, related_name='initial_invites')
    objects = models.Manager()
    @property
    def get_viewable_posts(self):
        print (self.channel_users) # prints []
        print (self.channel_posts) # prints []
        return CourtyardPost.objects.filter(deleted__isnull=True, courtyard_post_to=self.pk) #works, but adds IO strain

Currently, as shown above it's an ORM call per channel, but printing self.channel_posts or self.channel_users returns an empty list, in all scenarios. The property is called as a part of Django Rest Framework's serializer as implemented below:

class CourtyardChannelSerializer(serializers.ModelSerializer):
    current_user = serializers.SerializerMethodField('_user')
    post_to_channel = CourtyardPostSerializer(many=True, source='get_viewable_posts', read_only=True)
    most_recent = CourtyardPostSerializer(many=True, source='latest_post', read_only=True)
    channel_users = CourtyardUserSerializer(many=True, read_only=True)
)
invite_list = serializers.ReadOnlyField(source='get_invitation_set', required=False)

    def _user(self, obj):
        user = self.context['request'].user.username
        return user

score 1 · Accepted Answer · answered Feb 14 '17 at 02:46

1

class Channel(models.Model):
    created_date = models.DateTimeField(auto_now_add=True)
    channel_title = models.CharField(max_length=50, unique=True)
    channel_description = models.CharField(max_length=100)
    channel_creator = models.ForeignKey(User)
    channel_private = models.BooleanField(default=False)
    channel_users = models.ManyToManyField(User, through='Membership', related_name='channels', blank=True)
    channel_posts = models.ManyToManyField(Post, related_name='channel_post', blank=True)
    initial_invites = models.ForeignKey(User, null=True, blank=True, related_name='initial_invites')
    objects = models.Manager()

    @property
    def active_posts(self):
        return self.channel_posts.filter(deleted=None)

Very simple, just added an extra property and now you can use it like this

channel = Channel.objects.first()
print(channel.active_posts.count())

answered Feb 14 '17 at 02:46

Ramast

7,157
3
32
32

I actually ran into an issue trying to use this as-is, for some reason `self.channel_posts` is an empty array whether or not filtered at the point of the property (via a print). I ended up needing to rework it into a filtered queryset of all posts, but this won't perform well at scale (due to so many DB calls) any ideas why it would show up empty? You're answer is still the cleanest, just hoping it can work as you wrote it instead of my makeshift version. Either way, thank you for your help and time! – AntonMiles Feb 19 '17 at 14:56
If you run the code exactly as it is then obviously you would be getting active_posts for "first" channel in your DB. since Channel.objects.first() return first channel in DB. Could it be that first channel has no posts? – Ramast Feb 20 '17 at 01:33
I adapted it, allowing it to use the `active_posts` as the source for the serializer's representation of the ManyToMany relationship, I'll edit the question to illustrate this, but it never sees anything in any of the channel's for my default user, who had posts available before starting the delete functionality, and whom has posted since (which works, and is viewable via a different endpoint). It's still the best answer, and swapping it for a queryset works, just hoping to reduce the DB IO strain. *edited for clarity. – AntonMiles Feb 20 '17 at 15:43
I would suggest to first test it in python shell and make sure it return result, if it does then add break point to see why it doesn't work with serializer. You can always see the generated sql query by doing `print(str(self.channel_posts.filter(deleted=None).query))` – Ramast Feb 21 '17 at 06:20

score 0 · Answer 2 · edited May 23 '17 at 11:53

I'm guessing that at the moment, you're doing something like:

def channels_and_posts_for_user(user):
    for channel in user.channel_membership:
        posts = channel.channel_posts.all().filter(deleted__isnull=True)
        channels_and_posts.append(channel, posts)
    return channels_and_posts

and you'd like to get rid of that filter on every single call?

It is a pain, I agree, I've been trying to do something similar with an 'archived' variable in some of the models for my webapp.

I don't believe there's a way around it. You can create a custom manager like so:

class ChannelManagerWithUndeleted(models.Manager):
    def get_queryset(self):
        return super(ChannelManagerWithUndeleted, self).get_queryset().filter(deleted__isnull=True)

class Channel(models.Model):
    #...
    objects = models.Manager()    # Default Manager
    undeleted = EntryManager()    # Custom Manager

and then access the objects directly via Channel.undeleted.all() instead of Channel.objects.all, but you still need to specify this new manager on related calls anyway, and it ends up almost as verbose (if a little more DRY):

channels_and_posts = []
for channel in user.channel_membership:
    posts = channel.channel_posts.all(manager='undeleted').all()
    channels_and_posts.append(channel, posts)

This is also a relevant post: How to use custom manager with related objects?.

I think it's complicated because everyone wants slightly different behaviour in the different situations. e.g. I want my archived 'Events' to still be able to run reports, but not show up for the end user to select, so I'm using a custom manager everywhere consumer-facing.

If all you want to do is count them later for reporting, perhaps an option is to add a channel_deleted_posts ManyToManyField and move the Post (without deleting it) from channel_posts to channel_deleted_posts. Hooks necessary, I'm afraid.

I really hope there's a better answer and that what you want is trivial. I'd love to be proven wrong! :)

Django ORM Filtering Related Objects By Field Value?

2 Answers2