CODEWITHSUNDEEP
djangojoinfiltermany-to-many
Patrik Beck
Patrik Beck

Reputation: 2505

Getting django to use inner join

Hi I need to do a fairly complex query. And I can manage, to a certain point, to get django to return the objects, but some are duplicated.

The models are as follows: ProjectType, ProjectIdea. I need to select project types from the database that the company can start. Each company can start projects that they have an idea for, and are not patented. If they patent is expired, they can start it even if they do not have the idea.

class ProjectType(models.Model):
    patent_expires = models.DateTimeField(null=True)
    patent_owner = models.ForeignKey(Company,null=True)

#This really is just MtoN relationship
class ProjectIdea(models.Model):
    project = models.ForeignKey(ProjectType)
    company = models.ForeignKey(Company)

I tried the following queries:

#problem is that ProjectType(s), where patent expired and the company has idea for is returned twice
models.ProjectType.objects.filter(Q(projectidea__company=request.user.company) | Q(patent_expires__lt=timezone.now()))

#doesn't return projects where patent is expired and idea exists
models.ProjectType.objects.filter(Q(projectidea__company=request.user.company),(Q(patent_owner__isnull=True) | Q(patent_owner=request.user.company))).filter(Q(patent_expires__lt=timezone.now()) | Q(patent_owner__isnull=True))

#returns only the projects where patent expired and idea exists. Omits the not patented projects, where idea exists
q = models.ProjectType.objects.filter(Q(patent_expires__lt=timezone.now()) | Q(patent_owner=request.user.company)).filter(projectidea__company=request.user.company,patent_owner__isnull=True)

.distinct() #has no effect what so ever

I tried multiple more variations, but i can't figure out how to write it properly. I also tried approach with only .exclude() but it seems I can't use Q(...) & Q(...) in it, which make the expression impossible.

Any ideas?

Upvotes: 1

Views: 114

Answers (1)

Thomas
Thomas

Reputation: 11888

Naively I would assume this to work, (Assuming I've correctly captured your intent).

our_ideas = Q(projectidea__company=request.user.company)
has_patent = Q(patent_owner__isnull=False)
patent_expired = Q(patent_expires__lt=timezone.now())

startable_projects = models.ProjectType.objects\
    .filter( (~has_patent & our_ideas) | (has_patent & patent_expired) ).distinct()

If it does not, can you provide the SQL that is generated, and an example of the SQL you would expect to see?

Upvotes: 1

Related Questions