django | 常見 SQL 及其對應的 ORM 寫法

kingron發表於2024-06-28
DjangoSQLORM

SELECT (SELECT COUNT(*)...) ... AS ...

SQL:

select (select count(*) from child where child.pid = parent.id) as auth_exists from parent

寫法：

subquery = Subquery(Child.objects.filter(parent_id=OuterRef('id')).order_by()
                    .values('parent').annotate(count=Count('pk'))
                    .values('count'), output_field=IntegerField())
Parent.objects.annotate(child_count=Coalesce(subquery, 0))

解釋：

  • .order_by() 取消可能存在排序的列
  • 第一個 values 呼叫 .values('parent') 生成一個分組
  • .annotate(count=Count('pk')) 生成 count 的列
  • 第二個 values 呼叫 .values('count') 將 count 作為讀出的列
  • Coalesce 返回 count 的結果，如果是 null，則返回 0

參考：mysql - Django Count lines in a Subquery - Stack Overflow


GROUP BY ... COUNT

SQL:

SELECT player_type, COUNT(*) FROM players GROUP BY player_type;

寫法:

result = Books.objects.values('author')
                      .order_by('author')
                      .annotate(count=Count('author'))

參考：Django Tutorial => GROUB BY ... COUNT/SUM Django ORM equivalent (riptutorial.com)


WHERE field IN (SELECT field FROM ...)

SQL:

SELECT * FROM book WHERE author_id IN (SELECT id FROM author WHERE name LIKE 'kingron%')

寫法：

authores = Author.objects.filter(name__startswith='kingron')

# 預設使用 pk 作為查詢的列
books = Book.objects.filter(author_id__in=authores)

參考：如何在 django 中用 orm 寫子查詢 - kingron - 部落格園


SELECT ROW_NUMBER() OVER (...)

SQL:

select *, row_number() over (partition by c1 order by c2 desc) as rn from my_table

寫法：

from django.db.models import F, Window
from django.db.models.functions import RowNumber

MyModel.objects.annotate(rn=Window(expression=RowNumber(), partition_by=[F('c1')], order_by=F('c2').desc()))

參考：在 django 中使用視窗函式 - kingron - 部落格園

相關文章