MYSQL query equivalent in django

Question

I have a django project with 2 models called wca and identifiers, I am wanting to update a field called 'secid' within identifiers model pulled from the equivalent field in wca model where code from identifiers model matches isin field in wca model.

Models

class Identifier(TimeStampMixin, models.Model):
    secid = models.IntegerField(default=0, db_index=True)
    code_type = models.CharField(max_length=10, null=True)
    code = models.CharField(max_length=12, db_index=True)
    account = models.ForeignKey(Account, on_delete=models.CASCADE, null=True)

    def __str__(self):
        return self.code
        
        
class Wca(models.Model):
    secid = models.IntegerField(blank=False, null=False, db_index=True)
    isin = models.CharField(max_length=12, blank=True, db_index=True)
    uscode = models.CharField(max_length=9, blank=True, db_index=True)
    sedol = models.CharField(max_length=7, blank=True, db_index=True)
    
    def __str__(self):
        return str(self.secid)

I have the working sql code for this but would like to convert into django equivalent for best practice. My sql query is as follows:

    UPDATE portfolio_identifier
    left outer join product_wca on portfolio_identifier.code = product_wca.isin
    SET
    portfolio_identifier.SecID = product_wca.secid
    WHERE
    code_type = 'ISIN';

Edit

The following is the updated MYSQL query to handle mismatches (NULL values):

UPDATE portfolio_identifier
inner join product_wca on portfolio_identifier.code = product_wca.isin and portfolio_identifier.code_type = 'ISIN'
SET portfolio_identifier.SecID = product_wca.secid;

Eduardo Alejandro Leyva · Accepted Answer · 2022-08-04 23:46:36Z

2

This should work with some changes in the model and fields names.

from django.db.models import OuterRef, Subquery

Identifier.objects.filter(code_type='ISIN').update(
    secid=Subquery(
        Wca.objects.filter(
            isin=OuterRef('code')
        ).values('secid')
    )
)

edited Aug 4, 2022 at 23:46

answered Aug 3, 2022 at 3:59

Eduardo Alejandro Leyva

4454 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rob Over a year ago

Thanks for your response, but I'm getting isssues running this, my query slightly changed now as in I no longer need the case statement so original query looks like this now:

UPDATE portfolio_identifier 	left outer join product_wca on portfolio_identifier.code = product_wca.isin 	SET 	portfolio_identifier.SecID = product_wca.secid 	WHERE 	code_type = 'ISIN';

Eduardo Alejandro Leyva Over a year ago

Then you should replace the statement to something like

Identifiers.objects.filter(code_type = 'ISIN').update(SecID=Subquery(WCA.objects.filter(code=OuteRef('isin')).values('secid')))

Eduardo Alejandro Leyva Over a year ago

To give you a better response please share your Models.

rob Over a year ago

I have updated my question with the models and updated query. I am basically wanting to run the equivalent django query to run in a view. much appreciated

Eduardo Alejandro Leyva Over a year ago

@rob I have updated my answer to fit your Models.

|

Eduardo Alejandro Leyva · Accepted Answer · 2022-08-12 22:37:52Z

1

from django.db.models import OuterRef, Subquery, F, When, Case, Value, IntegerField

Identifier.objects.filter(
    code_type='ISIN'
    code__in=Wca.objects.values('isin')
).update(
    secid=Subquery(
        Wca.objects.filter(
            isin=OuterRef('code')
        ).annotate(
            secid_new=Case(
                When(
                    secid__isnull=False,
                    then=F('secid')
                ),
                default=Value(0),
                output_field=IntegerField()
            )
       ).values('secid_new').distinct()
    )
)

Or if the case when the WCA secid is not needed then:

Identifier.objects.filter(
    code_type='ISIN'
    code__in=Wca.objects.values('isin')
).update(
    secid=Subquery(
        Wca.objects.filter(
            isin=OuterRef('code')
        ).values('secid')
    )
)

edited Aug 12, 2022 at 22:37

answered Aug 6, 2022 at 1:50

Eduardo Alejandro Leyva

4454 silver badges12 bronze badges

5 Comments

Eduardo Alejandro Leyva Over a year ago

@rob this should work for you.

rob Over a year ago

Thanks for the new query, I only just had a chance to try out now. Unfortunately I'm receiving the following error: raise IntegrityError(*tuple(e.args)) django.db.utils.IntegrityError: (1048, "Column 'secid' cannot be null")

Eduardo Alejandro Leyva Over a year ago

@rob can you check if for the row that you are getting this error, exists an WCA record with the isin field = to the code field in the Identifier model? I think that for one row the filter in the subquery in empty and that's why this error is raised

rob Over a year ago

Hi yes I can confirm there are and will generally be ISINs in identifier model that do not exist in WCA, I knew this previously having had the same issue on the first answer you kindly gave. Sorry If I was not clear enough, I adapted my original sql query to be an inner join and also added code_type = 'ISIN' on the join rather than having where clause which resolves the Null issue - this is what I'm after in django equivalent. I will add my working mysql query to my question for your reference. Thanks

Eduardo Alejandro Leyva Over a year ago

@rob i updated my answer to fit that, my suggestion is to use an aditional filter in the Identifier so select only the ones their code can be found in the Wca. this avoid the Null. An alternative could be usingextra docs.djangoproject.com/en/4.1/ref/models/querysets/#extra but i preffer this solution.

Collectives™ on Stack Overflow

MYSQL query equivalent in django

2 Answers 2

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related