CODEWITHSUNDEEP
djangodjango-queryset
Brenden
Brenden

Reputation: 8774

Django query across foreign keys

I need to get a list of users who have been referenced as a ForeignKey in two other models. I'm still a little unclear on queries when they reach this complexity.

Models:

class Page(models.Model):
    user = models.ForeignKey(User)

class EmailSent(models.Model):
    user = models.ForeignKey(User)
    name = models.CharField(max_length=200)

In English, what I want to get is: 10 active users who have 0 pages and have never had an email with the name check_in sent to them.

Here's where I am:

users = User.objects.filter(is_active=1).annotate(page_count=Count('pages')).filter(page_count=0)[10]

but not sure how to do what is essentially:

email_sent = EmailSent.objects.filter(user=user, name='check_in')

Any ideas?

Upvotes: 0

Views: 130

Answers (2)

servik
servik

Reputation: 3111

Try the following: 

users = User.objects.filter(is_active=1)\
            .exclude(emailsent__name='check_in')\
            .extra(select={'page_count': "select count(*) from YOURAPP_page where user_id = auth_user.id"}, where=['page_count  0'])[:10]

Upvotes: 0

lprsd
lprsd

Reputation: 87215

One possible way to get what you want is:

users = User.objects.filter(is_active=1).annotate(page_count=Count('pages')).filter(page_count=0)
EmailSent.objects.filter(user__in=users, name='check_in')[10]

Another way is,

users = User.objects.filter(is_active=1).annotate(page_count=Count('pages')).filter(page_count=0)
users.emailsent_set.all()[10]

Upvotes: 2

Related Questions