Model.objects.extra to consider before using raw SQL with Django

I sometimes considered issuing Django raw queries.

For example, when you want to use the result calculated based on the column instead of specifying the column in SELECT or WHERE.

I was in trouble like this.

--There is no query builder! ――It looks like it's in a plugin! ――But does PostgreSQL support it? → I'm using MySQL!

Ah, the ORM object has a raw method ... well,

entities = Entity.objects.raw("SELECT concat_ws(',', id, name) AS mystr FROM entities WHERE name IN %s", params=(list(names),))

Is it okay? But the list specified in IN does not pass. Hmm,

https://docs.djangoproject.com/ja/1.9/ref/models/expressions/#raw-sql-expressions

But it doesn't work ...

So, when I searched a little more, I found something like this.

https://docs.djangoproject.com/ja/1.9/ref/models/querysets/#django.db.models.query.QuerySet.extra

With this,

entities = Entity.objects.extra(
    select={'mystr': "concat_ws(',', id, name)"},
    where=['name IN %s'],
    params=[list(names)]
)

It feels like I can write a little better than raw.

However ...!

Use this method as a last resort

This is an old API that we aim to deprecate at some point in the future. Use it only if you cannot express your query using other queryset methods.

Because there is, it may be better not to use it ...

https://docs.djangoproject.com/ja/1.9/ref/models/expressions/#raw-sql-expressions

Looks better to use

Recommended Posts

Model.objects.extra to consider before using raw SQL with Django
Steps to develop Django with VSCode
Common html to rent with Django
How to get started with Django
How to authenticate with Django Part 2
How to authenticate with Django Part 3
[Django 2.2] Add a New badge to new posts with a date using a template filter
How to do arithmetic with Django template
Step notes to get started with django
Application development using SQLite with Django (PTVS)
Execute SQL with Django + Postgresql (including PreparedStatement)
Execute raw SQL using python data source with redash and display the result
Try to create a Todo management site using WebSocket with Django (Swamp Dragon)
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 2: Import data to SQL Server using PowerShell
The easiest way to get started with Django
Try using django-import-export to add csv data to django
How to develop a cart app with Django
[Django] How to get data by specifying SQL.
How to deal with SessionNotCreatedException when using Selenium
How to implement "named_scope" of RubyOnRails with Django
Upload images to S3 with GUI using tkinter