django-dynamic-from-clause 0.0.5

Gives the ability to dynamically configure SQL For clause for models. This give you ability to wrap any sql into models and use ORM features on it.

:memo:	License
:package:	PyPi
	Django Versions	2.0, 2.1, 2.2, 3.0, 3.1, 3.2, 4.0, 4.1
	Python Versions	3.6, 3.7, 3.8, 3.9, 3.10

How to install?

pip install django-dynamic-from-clause

IDEA

The IDEA is to be able to map a tabular functions, any sql/queries outputs, and other, to Django models!

We want any database operation/object which has tabular output (such as table, view, function, queries, and so on), to be mapped to a dedicated django model. We want to be able to use the ORM methods (like select related, prefetch, annotations and others) on that model.

We are doing so by giving you the ability to define the SQL FROM clause dynamically and fill (if it is a function) it with args. See examples to check how powerful it might be.

Examples:

Wrap aggregation result

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.

Filter trough results of the window annotation on same queryset

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, forward_fields=['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)>]>

Let's use some database functions - check which rows are lock-ed on provided table

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()    

My tabular function

cooming soon, for now check tests

Note:

We have to specify which field is the primary key on the model

Available methods

...

How it works?

The Code is easy. The only thing which we do here is to extend the django SQL compiler and change how it creates the from_clause. The library has very little code.

Motivation

I think that this approach has sense cus I saw a lot of problems or ugly solutions which have tried to:

• use table functions,
• serialize objects on aggregated queryset (better to deal with objects then {"a__b": value} after aggregation)
• make selects over nested queries,
• replacing what database should do with python code,
• "manually" prefetching on serializers lvl,
• and others ugly things.

I think that this library contains a good idea, and a reasonable attempt, to solve issues like the above.

TODO:

• Migrations (here or in other library like the django-db-views - db functions can be a good replacement for views, cus views always calculate the whole dataset which can raise performance issues).

How to work with repo

add your .env file in the main directory, which set up POSTGRES env variables. Check conftest.py file for more details.