如何使用Django ORM删除表中的重复行？

rsaldnfx 于 2023-01-10 发布在 Go

关注(0)|答案(1)|浏览(178)

我有一个数据库表bad_reviews和一个对应的Django模型BadReviews。我想删除基于字段client_id，survey_id，text，rating，privacy_agreement的重复记录。我提出了这个查询：

SELECT br.*
FROM bad_reviews br
JOIN (
    SELECT client_id, survey_id, text, rating, privacy_agreement, COUNT(*)
    FROM bad_reviews
    GROUP BY client_id, survey_id, text, rating, privacy_agreement
    HAVING count(*) > 1
) dupes
ON br.client_id = dupes.client_id
AND br.survey_id = dupes.survey_id
AND br.text = dupes.text
AND br.rating = dupes.rating
AND br.privacy_agreement = dupes.privacy_agreement
ORDER BY br.client_id, br.survey_id, br.text, br.rating, br.privacy_agreement, br.id

如何使用Django ORM重写它？

django

来源：https://stackoverflow.com/questions/75059549/how-to-delete-duplicate-rows-from-a-table-using-django-orm

1条答案

按热度按时间

jvlzgdj91#

我希望这能奏效。

from django.db.models import Count, Subquery

#  Equivalent of this query in with Django ORM: SELECT client_id, survey_id, text, rating, privacy_agreement, COUNT(*)
#     FROM bad_reviews
#     GROUP BY client_id, survey_id, text, rating, privacy_agreement
#     HAVING count(*) > 1
subquery = BadReviews.objects \
        .values('client_id', 'survey_id', 'text', 'rating', 'privacy_agreement') \
        .annotate(count=Count('id')).filter(count__gt=1)

# use the subquery as a filter in the main query
bad_reviews = BadReviews.objects.filter(
    client_id=Subquery(subquery.values('client_id')),
    survey_id=Subquery(subquery.values('survey_id')),
    text=Subquery(subquery.values('text')),
    rating=Subquery(subquery.values('rating')),
    privacy_agreement=Subquery(subquery.values('privacy_agreement')),
).order_by('client_id', 'survey_id', 'text', 'rating', 'privacy_agreement', 'id')

赞(0）回复(0）举报 2023-01-10

我来回答

如何使用Django ORM删除表中的重复行？

1条答案

相关问题

热门标签

最新问答