Snippet List
#### Allows to fetch a row or array of rows of data, linked to parent object, in a single query. Data is fetched as JSON and is not serialized into Django objects.
##### Example:
from django.db import Models
class Book(models.Model):
authors = models.ManyToMany('Author', through='BookToAuthor', blank=True)
title = models.CharField(max_length=512, default='')
class Author(models.Model):
name = models.CharField(max_length=512, default='')
class BookToAuthor(models.Model):
author = models.ForeignKey(Author, on_delete=models.CASCADE)
book = models.ForeignKey(Book, on_delete=models.CASCADE)
##### Download author with all his/her books in a single query
from django.db.models import OuterRef
books_by_author_subquery = Book.objects.filter(
id__in=BookToAuthor.objects.filter(author_id=OuterRef(OuterRef('id')))
).values('title')
author = Author.objects\
.annotate(books=SubqueryJsonAgg(books_by_author_subquery))\
.get(id=1)
- json
- postgres
- postgresql
- subquery
Makes it possible to add a filtering condition directly after the aggregate function (or possible, `aggregate(expression) WITHIN GROUP (ordering clause)`. This is mostly useful if the annotation has two or more expressions, so it's possible to compare the result with and without the applied filter; it's more compact than using `Case`. It's suggested to add `values` to the queryset to get a proper group by.
Usage example:
`books = Book.objects.values('publisher__name').annotate(
count=Count('*'),
filtercount=Filter(expression=Count('publisher__name'),
condition=Q(rating__gte=5))
)
`
Supported on Postgresql 9.4+. Possible other third-party backends.
- filter
- postgresql
- olap
- aggregate
- expression
Mixin to support pagination when randomizing querysets.
Requirements: Postgres, Django Sessions
Note: This shouldn't be used on large complex datasets. It utilizes the relatively slow method of '?' randomized sorting. Use with caution.
Todo: MySQL support, Support for larger datasets
- django
- session
- pagination
- random
- postgres
- mixin
- postgresql
- cbv
- seeded
nothing to see here...
- pagination
- paginator
- postgresql
- paginate
- capped
Consider following models:
class Product(models.Model):
code = modeld.CharField()
class ProductTrans(models.Model):
product = models.ForeignKey('Product')
language = models.ChoiceField(choices=settings.LANGUAGES)
title = models.ChaField()
description = models.ChaField()
With this snippet is possible search through all translations of product at the same time (using string concatenation in trigger):
Product.objects.extra(
where = ['product_product.fulltext @@ to_tsquery(%s)'],
params = [ 'someproduct' ]
)
For PostgreSQL >=8.4 only.
- sql
- models
- translations
- model
- full-text
- postgres
- postgresql
- language
- fulltext
- translation
Have you always been annoyed by how you set up this elaborate big database schema and weren't able to have **ON DELETE CASCADE ON UPDATE CASCADE** in dbshell?
This solves the problem; create the two files and and empty *__init__.py* and put them somewhere in your path.
Then say DATABASE_ENGINE='postgresql_psycopg2_cascade' in settings.
Really I'd like this to be in the ForeignKey object, were it upstream Django or an own version of it, but it doesn't seem possible.
Ideas on how to make this configurable are more than welcome!
Props go out to Ari Flinkman for the inspiration to do this!
- database
- postgres
- foreign-key
- postgresql
- databases
A simple view used to manage the page cache stored in the database (here Postgresql in the django_cache table, you also have to set correctly CACHE_TABLE_OID, by the OID of the cache table (you can get it in PgAdmin)
- admin
- cache
- view
- postgresql
- management
Django does not currently allow one to pull ID values from arbitrarily named sequences. For example, if you did not create your ID column using the serial data type in PostgreSQL, you likely will not be able to use your sequences. This is quite a problem for those integrating with legacy databases. While ultimately the best place to fix this is django proper, this decorator will help people get by for now.
Note that in this case, all of my sequences are named "pk_TABLENAME". You'll likely have a different convention and should update the decorator appropriately. While I could have made the pattern a parameter, it didn't seem like that would gain much here.
- database
- sequence
- postgresql
9 snippets posted so far.