Applying union() on same model is not recognising ordering using GenericRelation

user_2282638 0
Viewed 1074

I have an Article model like this

from django.contrib.contenttypes.fields import GenericRelation
from django.db import models
from hitcount.models import HitCountMixin, HitCount

class Article(models.Model):
    title = models.CharField(max_length=250)
    hit_count_generic = GenericRelation(
    HitCount, object_id_field='object_pk',
    related_query_name='hit_count_generic_relation')

when I do Article.objects.order_by('hit_count_generic__hits'), I am getting results.but when I do

articles_by_id = Article.objects.filter(id__in=ids).annotate(qs_order=models.Value(0, models.IntegerField()))
articles_by_name = Article.objects.filter(title__icontains='sports').annotate(qs_order=models.Value(1, models.IntegerField()))
articles = articles_by_id.union(articles_by_name).order_by('qs_order', 'hit_count_generic__hits')

getting error

ORDER BY term does not match any column in the result set

How can i achieve union like this? I had to use union instead of AND and OR because i need to preserve order. ie; articles_by_id should come first and articles_by_name should come second.

using Django hitcount for hitcount https://github.com/thornomad/django-hitcount. Hitcount model is given below.

class HitCount(models.Model):
"""
Model that stores the hit totals for any content object.
"""
hits = models.PositiveIntegerField(default=0)
modified = models.DateTimeField(auto_now=True)
content_type = models.ForeignKey(
    ContentType, related_name="content_type_set_for_%(class)s", on_delete=models.CASCADE)
object_pk = models.TextField('object ID')
content_object = GenericForeignKey('content_type', 'object_pk')

objects = HitCountManager()

As suggested by @Angela tried prefetch related. 

articles_by_id = Article.objects.prefetch_related('hit_count_generic').filter(id__in=[1, 2, 3]).annotate(qs_order=models.Value(0, models.IntegerField()))
articles_by_name = Article.objects.prefetch_related('hit_count_generic').filter(title__icontains='date').annotate(qs_order=models.Value(1, models.IntegerField()))

the query of the prefetch_related when checked is not selecting the hitcount at all see.

 SELECT "articles_article"."id", "articles_article"."created", "articles_article"."last_changed_date", "articles_article"."title", "articles_article"."title_en", "articles_article"."slug", "articles_article"."status", "articles_article"."number_of_comments", "articles_article"."number_of_likes", "articles_article"."publish_date", "articles_article"."short_description", "articles_article"."description", "articles_article"."cover_image", "articles_article"."page_title", "articles_article"."category_id", "articles_article"."author_id", "articles_article"."creator_id", "articles_article"."article_type", 0 AS "qs_order" FROM "articles_article" WHERE "articles_article"."id" IN (1, 2, 3)
django-orm django
3 Answers

From Django's official documentation:

Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the combined queries.

So, make sure that your database allows combining queries like this.

user_50998030

ORDER BY term does not match any column in the result set

You are getting this error, because that's exactly what's happening. Your final result-set for articles does not contain the hits column from the hitcount table , due to which the result-set cannot order using this column.

Before delving into the answer, let's look at what's happening with your django querysets under the hood.

Retrieve a particular set of articles and include an extra ordering field qs_order set to 0.

articles_by_id = Article.objects.filter(id__in=ids).annotate(qs_order=models.Value(0, models.IntegerField()))

SQL Query for the above

Select id, title,....., 0 as qs_order from article where article.id in (Select ....) # whatever you did to get your ids or just a flat list

Retrieve another set of articles and include an extra ordering field qs_order set to 1

articles_by_name = Article.objects.filter(title__icontains='sports').annotate(qs_order=models.Value(1, models.IntegerField()))

SQL Query for the above

Select id, title, ...1 as qs_order from article where title ilike '%sports%'

Original queryset and order_by hit_count_generic__hits

Article.objects.order_by('hit_count_generic__hits')

This will actually perform an inner join and fetch the hitcount table to order by the hits column.

Query

Select id, title,... from article inner join hitcount on ... order by hits ASC

Union

So when you do your union, the result-set of the above 2 queries is combined and then ordered using your qs_order and then hits ...where it fails.

Solution

Use prefetch_related to get your hitcount table in the initial queryset filtering, so you can then use the hits column in the union to order.

articles_by_id = Article.objects.prefetch_related('hit_count_generic').filter(id__in=ids).annotate(qs_order=models.Value(0, models.IntegerField()))
articles_by_name = Article.objects.prefetch_related('hit_count_generic').filter(title__icontains='sports').annotate(qs_order=models.Value(1, models.IntegerField()))

Now as you have the desired table and its columns in both your SELECT queries, your union should work the way you have defined.

articles = articles_by_id.union(articles_by_name).order_by('qs_order', 'hit_count_generic__hits')
user_9319220
user_151275820
Related