Filter records by lookup in the same table-CodePudding

Suppose we have a table:

 --------------- ...
| id | old_id | 
 --------------- ...
| ...

How do I select all values by some custom criteria except where id is found in column old_id?

I've tried:

records = MyRecords.objects\
                .filter(<my custom criteria>)\
                .exclude(id__in=models.OuterRef('old_id'))\
                .order_by('-modified_at')

and other variations, but to no avail.

Basically, I am trying to achieve this behavior:

select * from mytable where 
   <custom criteria> and
   not id in (select old_id from mytable where 1)

CodePudding user response：

I think you can do this:

ids_to_exclude = MyRecords.objects.all().values_list('old_id',flat=True)
#get unique values
ids_to_exclude  = list(set(ids_to_exclude))

records = MyRecords.objects\
                .filter(<my custom criteria>)\
                .exclude(id__in= ids_to_exclude)\
                .order_by('-modified_at')

CodePudding user response：

If need to check for all rows, maybe old_id in other row.

from django.db import models

# Please, never convert `old_ids` queryset below to list, tuple, set, etc.
# Keep it as queryset, for better performance.
old_ids = MyRecords.objects.values_list("old_id", flat=True)

MyRecords.objects.filter().exlcude(id__in=old_ids)

If just check old_id in same row:

MyRecords.objects.filter(
   
).annotate(
   is_match_old_id=models.Case(
       models.When(id=models.F("old_id"), then=True),
       default=False,
       output_field=models.BooleanField()
   )
).filter(
   is_match_old_id=False
)