Annotate new field with the same name as existing one

Question

We are facing a situation here that I would like to get more experienced user’s opinion. We are at Django 4, Python 3.9.

The current scenario:

We already have our system running in production for a reasonable time and we need to change on the Backend some returned data to our Frontend application (Web, mobile…) based on users choice.

Something important is: Changes on the Frontend side are not an option for now.

We need to override some attributes from one model, based on existing or not corresponding data on another table.

The Tables (Django Models):

AgentSearch (this data is replaced every single day by an ETL process)

id
identifier_number (unique)
member_first_name
member_last_name
member_email
some_other_attrbs

Agent (Optional data that the agent can define)

id
agent_search_identifier_number (FK OneToOne)
first_name
last_name
email

For our requirement now, all the queryset will be made starting on AgentSearch. The idea is getting the Agent data instead of AgentSearch data every time there is an agent linked to it.

Today we get like this:

AgentSearch.objects.all()

I know that would be easier to annotate new columns like this:

        # using a different attribute name
        NEW_member_first_name=Case(
            When(
                agent__id__isnull=True,
                then=F("member_first_name"),
            ),
            default=F("agent__first_name"),
        ),

This would require us to make changes on the FrontEnd.

If we try:

        # using the same attribute name
        member_first_name=Case(
            When(
                agent__id__isnull=True,
                then=F("member_first_name"),
            ),
            default=F("agent__first_name"),
        ),

Django will throw:

`ValueError Exception saying: The annotation 'member_first_name' conflicts with a field on the model.`

We already tried removing the "overridable" attributes from the base query. Example:

        # Tell Django ORM to not get the column from DB
        AgentSearch.objects.all().defer('member_first_name')
        .annotate(
             member_first_name=Case(
                 When(
                     agent__id__isnull=True,
                     then=F("member_first_name"),
                 ),
                 default=F("agent__first_name"),
             ),

Still:

`ValueError Exception saying: The annotation 'member_first_name' conflicts with a field on the model.`

Also tried forcing the values only for non "overridable" attributes from the base query. Example:

        # Tell Django ORM to not get the column from DB
        AgentSearch.objects.all().values('some_other_attrbs')
        .annotate(
             member_first_name=Case(
                 When(
                     agent__id__isnull=True,
                     then=F("member_first_name"),
                 ),
                 default=F("agent__first_name"),
             ),

Still:

`ValueError Exception saying: The annotation 'member_first_name' conflicts with a field on the model.`

Also I give a try creating a django context manager to do this, but it would throw the same error.

In your opinion, what would be an elegant way to do this? We found a solution, but seems really ugly.

How it can work for now:

class BaseAgentSearch(AbstractBaseModel):
    ...
    class Meta:
        abstract = True

class AgentSearch(BaseAgentSearch):
    class Meta:
        db_table = "app_agentsearch"
        # setting to false, since this model will act only as a "reader"
        managed = False
    ###########################################################
    # Original DB columns are mapped to a temp_ attribute name
    ###########################################################
    temp_member_first_name = models.CharField(
        max_length=64, db_column="member_first_name"
    )
    temp_member_last_name = models.CharField(
        max_length=64, db_column="member_last_name"
    )
    temp_member_email = models.EmailField(
        max_length=64, blank=True, db_column="member_email"
    )


class AgentSearchWrite(BaseAgentSearch):
    class Meta:
        db_table = "app_agentsearch"

    member_first_name = models.CharField(max_length=64)
    member_last_name = models.CharField(max_length=64)
    member_email = models.EmailField(max_length=64, blank=True)

Then Query as:

    AgentSearch.objects.all()
    .annotate(
        member_first_name=Case(
            When(
                agent__id__isnull=True,
                then=F("temp_member_first_name"),
            ),
            default=F("agent__first_name"),
        ),
        member_last_name=Case(
            When(agent__id__isnull=True, then=F("temp_member_last_name")),
            default=F("agent__last_name"),
        ),
        member_email=Case(
            When(agent__id__isnull=True, then=F("temp_member_email")),
            default=F("agent__email"),
        ),

This works as expected. But seems really strange and ugly. And also, to add/update data, we should now use the AgentSearchWrite model that have the correct columns on DB and model.

Annotate new field with the same name as existing one

0 Answers