：备忘录：	执照
：包裹：	派皮
	Django 版本	2.0、2.1、2.2、3.0、3.1、3.2、4.0、4.1
	Python 版本	3.6、3.7、3.8、3.9、3.10

如何安装？

pip install django-dynamic-from-clause

主意

IDEA 是为了能够将表格函数、任何 sql/查询输出等映射到 Django 模型！

我们希望任何具有表格输出（例如表、视图、函数、查询等）的数据库操作/对象都映射到专用的 django 模型。我们希望能够在该模型上使用 ORM 方法（如选择相关、预取、注释等）。

我们通过让您能够动态定义SQL FROM 子句并使用 args 填充（如果它是一个函数）它来做到这一点。查看示例以检查它的强大程度。

例子：

包装聚合结果

from django.db import models

from django_dynamic_from_clause.models import DynamicFromClauseBaseModel

# regular models
class Owner(models.Model):
    name = models.CharField(max_length=512)


class InventoryRecord(models.Model):
    count = models.IntegerField()
    owner = models.ForeignKey(
        Owner, related_name='inventory_records', on_delete=models.CASCADE
    )


# Our perspective for the InventoryRecordQuerySet
class AggregatedInventoryPerspective(DynamicFromClauseBaseModel):
    count_sum = models.IntegerField()
    owner = models.ForeignKey(
        Owner,
        related_name='+', # Feel free to set the related name and use it. It will work without problems. 
        on_delete=models.DO_NOTHING,
        primary_key=True # We have to pick a field which will mimic the primary key
     )

# Lets make some aggregations
aggr_inv_records_queryset = InventoryRecord.objects.values(
    "owner"
).annotate(
    count_sum=models.Sum("count")
)
# Generated SQL is: 
# 'SELECT "test_app_inventoryrecord"."owner_id", SUM("test_app_inventoryrecord"."count") AS "count_sum" 
# FROM "test_app_inventoryrecord" 
# GROUP BY "test_app_inventoryrecord"."owner_id"'
#
# And example output is: <QuerySet [{'owner': 36, 'count_sum': 24}]>


# Let use ORM on the results from the aggr_inv_records_queryset
aggregated_inv_records = AggregatedInventoryPerspective.objects.set_source_from_queryset(
    aggr_inv_records_queryset
).select_related('owner')

# Generated SQL is:
# SELECT 
#   "_aggregatedinventoryperspective"."count_sum", 
#   "_aggregatedinventoryperspective"."owner_id",
#   "test_app_owner"."id", "test_app_owner"."name" 
# FROM (
#    SELECT "test_app_inventoryrecord"."owner_id", SUM("test_app_inventoryrecord"."count") AS "count_sum" 
#    FROM "test_app_inventoryrecord" 
#    GROUP BY "test_app_inventoryrecord"."owner_id") AS "_aggregatedinventoryperspective" 
#    INNER JOIN "test_app_owner" ON ("_aggregatedinventoryperspective"."owner_id" = "test_app_owner"."id"
# )
# and example output is: 
#   <DynamicFromClauseQuerySet [<AggregatedInventoryPerspective: AggregatedInventoryPerspective object (36)>]>
aggregated_inv_records.get().owner  # return an owner :), Our perspective can be prefetched from the Owner model as well.

在同一查询集上过滤窗口注释的低谷结果

from django.db import models
from django.db.models import QuerySet
from django.db.models import F, Window
from django.db.models.functions import Rank

from django_dynamic_from_clause.query import DynamicFromClauseQuerySet

# Regular django model, with extra objects manager 
class Human(models.Model):
    objects = QuerySet.as_manager()
    dynamic_from_clause_objects = DynamicFromClauseQuerySet.as_manager()
    weight = models.IntegerField()
    height = models.IntegerField()

# We would like to annotate rank, and filter through it, 
# which is imposible in regular django without raw query. 
# Django will throw NotSupportedError
humans_with_rank = Human.objects.all().annotate(rank=Window(
    expression=Rank(),
    order_by=[F('height'), F('weight')]
))

# But we can easily overcome that!
# By using our manager, to make query from the query
humans_with_rank_less_or_equal_two = Human.dynamic_from_clause_objects.set_source_from_queryset(
    humans_with_rank
).get(rank__lte=2)
# Let's see how generated query looks like:
# SELECT 
#   "test_app_human"."id", "test_app_human"."weight",
#   "test_app_human"."height", "test_app_human"."rank" AS "rank" 
# FROM (
#   SELECT 
#       "test_app_human"."id",
#       "test_app_human"."weight", 
#       "test_app_human"."height",
#       RANK() OVER (ORDER BY "test_app_human"."height", "test_app_human"."weight") AS "rank" 
#   FROM "test_app_human"
# ) AS "test_app_human" 
# WHERE "test_app_human"."rank" <= 2
# And we still deal with a Human objects!
# <DynamicFromClauseQuerySet [<Human: Human object (218)>, <Human: Human object (216)>]>

让我们使用一些数据库函数 - 检查哪些行被锁定在提供的表上

from django.db import models
from django.db.models import Func
from django.contrib.postgres.fields import ArrayField

from django_dynamic_from_clause.models import DynamicFromClauseBaseModel


class ExampleModel(models.Model):
    pass


class PGRowLocks(Func):  # you have to create pgrowlock extension first
    function = 'pgrowlocks'
    template = "%(function)s('%(expressions)s')"


# This model maps to the pgrowslocks function output which is all locks on provided table
class PgRowsLocks(DynamicFromClauseBaseModel):
    EXPRESSION_CLASS = PGRowLocks 

    locked_row = ArrayField(models.PositiveIntegerField(), size=2, primary_key=True)
    locker = models.PositiveBigIntegerField()
    multi = models.BooleanField()
    xids = ArrayField(models.PositiveIntegerField())
    modes = models.PositiveIntegerField(models.TextField())
    pids = ArrayField(models.SmallIntegerField())

# Now we can easy check what is locked on which table :)
locked_rows = PgRowsLocks.objects.fill_expression_with_parameters(
        ExampleModel._meta.db_table
).all()    

我的表格函数

cooming soon, for now check tests

笔记：

我们必须指定哪个字段是模型上的主键

可用方法

...

这个怎么运作？

代码很简单。我们在这里唯一要做的就是扩展 django SQL 编译器并更改它创建 from_clause 的方式。该库的代码很少。

动机

我认为这种方法很有意义，因为我看到了很多问题或丑陋的解决方案，它们试图：

• 使用表函数，
• 在聚合查询集上序列化对象（最好在聚合后处理对象然后 {"a__b": value} ）
• 对嵌套查询进行选择，
• 用python代码替换数据库应该做什么，
• 在序列化程序 lvl 上“手动”预取，
• 和其他丑陋的东西。

我认为这个库包含解决上述问题的好主意和合理尝试。

去做：

• 迁移（在这里或在其他库中，如 django-db-views - db 函数可以很好地替代视图，cus 视图总是计算整个数据集，这可能会引发性能问题）。

如何使用回购

在主目录中添加您的 .env 文件，该文件设置 POSTGRES 环境变量。检查 conftest.py 文件以获取更多详细信息。