Django ORM - Annotate Query with another SubQuery while keeping the results of SubQuery

user_3778674 0
Viewed 142
#  My Database Models
ModelA(models.Model):
    referancer = models.CharField()
    ...
        
ModelXYZ(models.Model):
    name = models.CharField()
    ...

I have models like top and there is no foreignkey relation between them also structure should stay this way because in my real query; filters and annotations are more complexier then this one. Also this is simplest way to reproduce problem.

modelXYZ_subquery = ModelXYZ.objects.filter(name__exact=OuterRef('referancer')).order_by('-id')
modelA_dataset = ModelA.objects.filter(**my_custom_filters).annotate(
    has_XYZ=Exists(modelXYZ_subquery),
    latest_xyz=Subquery(modelXYZ_subquery.values('id')[:1], output_field=IntegerField)
)

for _modelA in modelA_dataset:
    my_custom_function_1(_modelA)
    if _modelA.has_XYZ:
        detected_xyz = ModelXYZ.objects.get(id=_modelA.latest_xyz) # Database Hit
    else:
        detected_xyz = create_xyz(_modelA)
    my_custom_function_2(_modelA, detected_xyz)

This is the function I got but its working for too long because of the huge dataset.

I want to get rid of the unnecessarry database hit I got top there in for loop.

Is there anyway to achive this goal with annotation or prefetch_related or something?

annotate prefetch orm mysql django
1 Answers

You can consider using JoinField. This will not change your database structure, but will allow the ORM to do joins the same way it does with foreign keys. pip install django-joinfield and put joinfield in your INSTALLED_APPS in settings.py

from joinfield.joinfield import JoinField

class ModelXYZ(models.Model):
    name = JoinField(ModelA, to_field='referancer', on_delete=models.DO_NOTHING)

makemigrations will create a migration, but the migration will not change your database structure. It will be allowed to have values in ModelXYZ.name that don't exist in ModelA.referancer.

Now you can do a prefetch_related from ModelA to ModelXYZ.

user_39838040
Related