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
Django's CharField requires a max_length, and TextField displays a multi-
line widget, but in Postgres there's no reason to add an arbitrary max
thanks to the `varlena` storage format. So this is a TextField that
displays as a single line instead of a multiline TextArea.
If you create alot of data via fixtures or by inserting the pk you will more than likely see alot of issues with the sequences being out in postgres when creating new records.
Similar to: Foo with the pk(x) already exists
This you have to fix by updating the postgres sequences to start at the correct number.
The below will ensure after every migrate all sequences in predefined apps get reset.
The recipe uses deferred constraint validation to create circular references across database tables. The example requires Postgres (MySQL doesn't support deferred constraints).
To achieve this, the following is required:
* Insertions must be performed in a transaction. Foreign key constraints will be validated at the end of the transactions, allowing for insertion of rows with FKs pointing to rows that don't exist yet.
* Primary keys need to be generated before insertion. That's what `prefetch_id` does by pulling the next value from the `*_id_seq` sequence.
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
Use this paginator to make admin pages load more quickly for large tables when using PostgreSQL. It uses the reltuples statistic instead of counting the rows when there is no where clause. To use this code, add the following in your admin:
`class BigTableAdmin(admin.ModelAdmin):
paginator = LargeTablePaginator
def get_changelist(self, request, **kwargs):
return LargeTableChangeList
`
- paginator
- postgres
- reltuples
Unlimited-length character fields in Postgres perform the same as limited-length fields, and the Postgres manual suggests not arbitrarily limiting these fields. Unfortunately, Django does not provide a way to access unlimited-length character fields except using TextField, which is rendered differently in forms and in the admin, and has different connotations.
LongCharField is a way to avoid putting arbitrary max_length values where they aren't required. It will only work with databases that allow VARCHAR with no numeric parameters, such as Postgres. MySQL won't work.
- text
- field
- postgres
- charfield
- length
- max_length
- unlimited
- varchar
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
Keeps database table names out of custom SQL code, but still allows for correct parameter passing in the execute function. (psycopg doesn't substitute table or field names, only data, in the execute function)
12 snippets posted so far.