Django selecting a record if its Many-to-Many relation contains data

Question

I have these 2 tables

class Job(models.Model):
    name = models.CharField()
    collaborators = models.ManyToManyField(User, through="Collaborator")
    created_by = models.ForeignKey(User)

class Collaborator(models.Model):
    job = models.ForeignKey(Job)
    user = models.ForeignKey(User, related_name="jobs")
    role = models.CharField()
    

For a user, I want to select all those Jobs where either created_by=user or the user is in collaborators.

The following query does not produce correct results

Job.objects.filter(Q(created_by=user) | Q(collaborators=user))

Upon looking at the raw query generated by above code I have found out that this is because the query produced by django ORM creates a LEFT OUTER JOIN between Job and Collaborator, so if there are multiple Collaborators for the same Job then it returns multiple rows of the same job.

What I have come up with is the following query

Job.objects.filter(Q(created_by=user) | Q(pk__in=user.jobs.all().values("job_id")))

My question is, is there a better query than the above that I can use?

P.S. I am using django 4.0

Answer 1

You can use distinct() to remove duplicates from the QuerySet, which seems to be the simplest solution:

jobs = Job.objects.filter(Q(created_by=user) | Q(collaborators=user)).distinct()

But, its also possible to group_by() the results, for instance:

jobs = (
    Job.objects.filter(Q(collaborators=user) | Q(created_by=user))
    .annotate(times_repeated=Count('id'))
    .order_by()
)

Both options looks better than having a nested query.