How can I filter a model in django based on a field of a many to many related object?

Question

I have two models related to each other with a many to many. I want to filter for one, Message, based on a field on the other, User.created_at, compared to a field on the first, Message.edit_date.

class Message(Model):
    content = CharField(max_length=512, blank=True, null=True)
    created_at = models.DateTimeField(blank=True, null=True)
    edit_date = models.DateTimeField(blank=True, null=True)
    users = models.ManyToManyField('User', related_name='message_user')

class User(Model):
    name = content = CharField(max_length=48)
    created_at = models.DateTimeField(blank=True, null=True)

Right now I am achieving this by looping over the two models and comparing them in the loop, which is slow.

message_query = Message.objects.none()
for user_name, created_at_date in Users.objects.filter(name='Tina').values_list('id', 'created_at').iterator():
    message_query.add(Q(
        users=user_id,
        edit_date__gte=created_at_date,
    ), Q.OR)

messages = Message.objects.filter(message_query)

Is there any way to create a filter for the items I'm attempting to filter for in a query?

Answer 1

You can filter on fields on the related model directly using F expressions. Something like this should work:

from django.db.models import F

# This will return all messages where one of the associated users
# was created_at before the message edit_date.
Message.objects.filter(
    edit_date__gte=F('users__created_at')
).distinct()

Note that this will return duplicate results if more than one user matches this condition for any given message - hence the distinct() the end.