<jupyter_start><jupyter_text>Use sql in postgresql to reorder frontpage stories efficiently.<jupyter_code>import sqlparse
from django.db.models import Func, Value, BooleanField
from django.db.models.functions import Rank, DenseRank, RowNumber, Cast
from django.db.models import F, Window, DurationField, ExpressionWrapper, DateTimeField, IntegerField, FloatField
from django.db.models import OuterRef, Subquery, Sum

class Days(Func):
    """Cast float field to Interval in days"""
    output_field=DurationField()
    template="to_char(%(expressions)s, 'S9990.0999 \"days\"')::interval"
    
class Epoch(Func):
    """Get epoch timestamp from date time field """
    output_field=FloatField()
    template="extract(epoch from %(expressions)s ) / 3600"

class English(Func):
    """Is the language english"""
    output_field=BooleanField()
    template="%(expressions)s = 'en'"
    

adjusted_publication_time=F('story__publication_date') + Days('priority')
adjusted_ranking = Window(expression=RowNumber(), partition_by=[English('story__language')], order_by=adjusted_publication_time.desc(nulls_last=True))
qry = FrontpageStory.objects.published().annotate(ranking=adjusted_ranking).values('id', 'ranking')
raw_query = sqlparse.format(str(qry.query), reindent=True, keyword_case='upper')
print(raw_query)

update_ordering = f"""
WITH ordered_frontpage AS ( {raw_query} )
UPDATE "frontpage_frontpagestory" SET "order" = "ordered_frontpage"."ranking"
FROM "ordered_frontpage"
WHERE "frontpage_frontpagestory"."id" = "ordered_frontpage"."id";
"""
from django.db import connection
with connection.cursor() as cursor:
   cursor.execute(update_ordering) 
print(update_ordering)
FrontpageStory.objects.update(order=0)
FrontpageStory.objects.first().save()
#FrontpageStory.objects.reorder()
list(FrontpageStory.objects.all().values('order', 'story__language', 'pk', 'headline', 'story__publication_date').order_by('order'))[:10]
qry = FrontpageStory.objects.annotate(ranking=adjusted_ranking).values('id', 'ranking', 'order')
raw_query = sqlparse.format(str(qry.query), reindent=True, keyword_case='upper')
print(raw_query)
list(qry[:20])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13730929264909847, 'fraction_numerical': 0.00785945446139621, 'mean_word_length': 7.796747967479675, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '164755', 'license': 'Apache-2.0', 'n_tokens_mistral': 702, 'n_tokens_neox': 664, 'n_words': 133, 'path': 'django/notebooks/commit/Frontpage order windowing function.ipynb', 'repo_name': 'Haakenlid/tassen', 'size': '2163'}