Django combine 2 QuerySets

Question

I have 2 QuerySets that target different tables in the same database.

What i'm trying to achieve is to filter results by created_at and then by hit_count.

I'm using django-hitcount for the hit_count

Here's the Model

class Post(models.Model):
    STATUS_CHOICES = (
        (1, 'Technology'),
        (2, 'Security'),
        (3, 'Android|iOS'),
    )
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, unique=True, editable=False)
    title = models.CharField(max_length=250)
    body = models.TextField()
    main_image = models.ImageField(null=False, upload_to=save_image)
    second_image = models.ImageField(blank=True, default="")
    third_image = models.ImageField(blank=True, default="")
    fourth_image = models.ImageField(blank=True, default="")
    fifth_image = models.ImageField(blank=True, default="")
    slug = models.SlugField(blank=True, default="", editable=True)
    created_at = models.DateField(default=date.today)
    category = models.PositiveSmallIntegerField(choices=STATUS_CHOICES, default=1)
    custom = managers.PostManager()

Here's the Manager

class PostManager(models.Manager):

    def get_queryset(self):
        return super(PostManager, self).get_queryset()

    def get_tech(self):
        return self.get_queryset().filter(category=1)

    def get_security(self):
        return self.get_queryset().filter(category=2)

    def get_mobile(self):
        return self.get_queryset().filter(category=3)

    def get_category_count(self):
        return self.get_tech().count(), self.get_security().count(), self.get_mobile().count()

    def get_top_stories_of_month(self):
        s_date = datetime.strftime(datetime.now() - timedelta(days=25), '%Y-%m-%d')
        e_date = datetime.strftime(datetime.now() - timedelta(days=32), '%Y-%m-%d')
        qs1 = self.get_queryset().filter(Q(created_at__lte=s_date) | Q(created_at__gte=e_date))
        qs2 = HitCount.objects.order_by('hits')

As you see, i have stored the results to qs1 and qs2, but have no idea how to combine them.

I also have played around in the shell to try to filter out the objects in the query_set by object_pk's which they have in common. The result is very strange tho, when comparing pk's it returns False for some reason.

Code from Shell

>>> p = Post.custom.get_top_stories_of_month()
>>> h = HitCount.objects.order_by('hits')
>>> p
<QuerySet [<Post: dddddddddddddddddd>, <Post: kljlkjlkj>, <Post: asdasdad>, <Post: aasdfasdf>, <Post: zvzxv>]>
>>> h
<QuerySet [<HitCount: asdasdad>, <HitCount: zvzxv>, <HitCount: aasdfasdf>, <HitCount: kljlkjlkj>, <HitCount: dddddddddddddddddd>]>
>>> for i in h:
...     print(i.object_pk)
...     for j in p:
...             print(j.pk)
...             print(i.object_pk == j.pk)
... 
868ca0d9-f324-4845-b34b-38a1203eacb3
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
False
889d895b-f73c-48ca-9a96-064cbe6292c1
False
868ca0d9-f324-4845-b34b-38a1203eacb3
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
False
4b6427b1-4dab-4705-b5de-d39ccccec119
False
4b6427b1-4dab-4705-b5de-d39ccccec119
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
False
889d895b-f73c-48ca-9a96-064cbe6292c1
False
868ca0d9-f324-4845-b34b-38a1203eacb3
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
False
4b6427b1-4dab-4705-b5de-d39ccccec119
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
False
889d895b-f73c-48ca-9a96-064cbe6292c1
False
868ca0d9-f324-4845-b34b-38a1203eacb3
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
False
4b6427b1-4dab-4705-b5de-d39ccccec119
False
889d895b-f73c-48ca-9a96-064cbe6292c1
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
False
889d895b-f73c-48ca-9a96-064cbe6292c1
False
868ca0d9-f324-4845-b34b-38a1203eacb3
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
False
4b6427b1-4dab-4705-b5de-d39ccccec119
False
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
72b2104f-2cb6-49b3-80fd-fd0e80fab5b1
False
889d895b-f73c-48ca-9a96-064cbe6292c1
False
868ca0d9-f324-4845-b34b-38a1203eacb3
False
762f1c29-6ba5-4aaf-99a3-833c8c60d126
False
4b6427b1-4dab-4705-b5de-d39ccccec119
False

ekneiling · Accepted Answer · 2020-01-09 18:10:38Z

3

The docs for django-hitcount describe here how to add a field to one of your models (Post) that will relate back to the HitCount model.

After you add a hit_count attribute to your Post model that relates by following the example in the docs, you can order and/or filter by hits as well as post dates in your query. You query would look something like this:

top_posts = Post.objects.order_by("hit_count__hits").filter(Q(created_at__lte=s_date) | Q(created_at__gte=e_date))

answered Jan 9, 2020 at 18:10

ekneiling

1571 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

h1dd3n · Accepted Answer · 2020-01-10 14:03:12Z

0

Sadly i wasn't able to make the query as elegant as it should be got some relation error when order_by('hit_count.hits')

Decided to do it this way:

s_date = datetime.strftime(datetime.now() - timedelta(days=25), '%Y-%m-%d')
        e_date = datetime.strftime(datetime.now() - timedelta(days=32), '%Y-%m-%d')
        qs1 = self.get_queryset().filter(Q(created_at__lte=s_date) |
                                         Q(created_at__gte=e_date))
        mlist = sorted(list(qs1), key=lambda obj: obj.hit_count.hits, reverse=True)
        return mlist[:2]

This returns 2 most viewed posts.

answered Jan 10, 2020 at 14:03

h1dd3n

2921 silver badge14 bronze badges

Collectives™ on Stack Overflow

Django combine 2 QuerySets

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related