N
N
Neoliz2017-05-26 12:17:05
PostgreSQL
Neoliz, 2017-05-26 12:17:05

How to exclude duplicate entries from QuerySet?

Hello! Actually the question is in the header. I will describe the problem. There are two models, comments and rubrics. Comments are linked by content type to categories, and MTPP to themselves. There was a need to sort Querysey with headings, by the date of creation of the last comment in the heading. Those. where the last koment was, that heading and above should be. Sorting is easy, but duplications are obtained due to comments.
PostgreSQL base==9.6 Django==1.10
models.py Minimal models

from mptt.models import MPTTModel, TreeForeignKey
from django.contrib.contenttypes.fields import GenericForeignKey, GenericRelation
from django.contrib.contenttypes.models import ContentType

class Comment(MPTTModel):
    """ Content type framework """
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField("ID объекта")
    content_object = GenericForeignKey('content_type', 'object_id')

    """ MTPP Tree comments """
    parent = TreeForeignKey('self', null=True, blank=False, related_name='children', db_index=True, verbose_name="Родительский комментарий")

    date_create = models.DateTimeField("Дата создания", auto_now_add=True)


class Discussion(ImageFieldAbstract):
    """ Item discussion """
    date_create = models.DateTimeField("Дата создания", auto_now_add=True)
    comments = GenericRelation(Comment)

I'm trying to sort:
from django.db.models import DateTimeField, Case, When
import datetime

Discussion.objects.all().annotate(
    comment_old_date=Case(
        When(
            comments__date_create__isnll=True,
            then=datetime.datetime.strptime('1950-01-01 00:00:00', '%Y-%m-%d %H:%M:%S')
        ),
        default='comments__date_create',
        output_field=DateTimeField()
    )
).order_by('-comment_old_date').distinct()

As a result, distinct does not work. And it's understandable why. Because if you do not specify the fields, then it goes through each column in the resulting table and checks for uniqueness (and order_by adds fields to the select for sorting, and the field with the date is unique for everyone.)
I've already tried everything:
.order_by('pk', '-comment_old_date').distinct('comment_old_date', 'pk')
.order_by('pk', '-comment_old_date').distinct('pk', 'comment_old_date')
.distinct('pk', 'comment_old_date').order_by('pk', '-comment_old_date')
.distinct().order_by('pk', '-comment_old_date')
# И Т,Д,

Can you tell me how to implement this correctly? Or correct me where I'm wrong.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
N
Neoliz, 2017-05-26
@timofeydeys

Question removed. Finally did so.

Discussion.objects.all().annotate(
                comment_old_date=Max(
                    Case(
                        When(
                            comments__date_create__isnull=True,
                            then=datetime.datetime.strptime('1950-01-01 00:00:00', '%Y-%m-%d %H:%M:%S')
                        ),
                        default='comments__date_create'
                    )
                )
            ).order_by('-comment_old_date')

A
Andrei Ramanchyk, 2017-05-27
@jagrmi

there is also in the same topic where filter is another feature, it removes all duplicate
distinct() entries - if you are interested, google about it, I remember using it somehow, but this "function" does not work with MySQL

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question