Filter Django model based on other model-CodePudding

I have two models Wellinfo and LOGRD_RPT

class Wellinfo (models.Model):
    WellID    = models.CharField(max_length=15,unique=True)
    Perimeter = models.CharField(max_length=50)

class LOGRD_RPT(models.Model):
    WellID    = models.CharField(max_length=15, validators= [validate_textComptn])

I need to get a list of WellID (ListaWells) from the first model Wellinfo based on two conditions:

1- Perimeter.

2- if the WellID exists in the second model LOGRD_RPT

So the First step is to get all wells that belong to a Field='FD1' in the Wellinfo model

ListaWells=   Wellinfo.objects.filter(Perimeter=Field)

then I need to exclude the wells that don't exist in the 2nd model LOGRD_RPT.

CodePudding user response：

You can use Exists subquery:

from django.db.models import Exists, OuterRef

ListaWells = Wellinfo.objects.annotate(
    log_exists=Exists(LOGRD_RPT.objects.filter(WellID=OuterRef("WellID")))
).filter(Perimetre=Field, log_exists=True)

Or shorter:

from django.db.models import Exists, OuterRef

ListaWells = Wellinfo.objects.filter(
    Exists(LOGRD_RPT.objects.filter(WellID=OuterRef("WellID"))),
    Perimetre=Field,          
)

CodePudding user response：

It works with this

ListaWells = Wellinfo.objects.filter(Perimeter=Field)
ListaWell = Wellinfo.objects.filter(Perimeter=Field).values_list('WellID', flat=True).distinct()
LogaWells = LOGRD_RPT.objects.values_list('WellID', flat=True).distinct()
for well in ListaWell:
    if well not in LogaWells:
        ListaWells = ListaWells.exclude(WellID__exact= well)

and I think the way posted by neverwalkaloner is better if it works. any other suggestions?