Using Aggregate and Count

[Timothy W. Cook]

I see there was a similar question on this issue earlier today but
alas it didn't answer my question.

In my app I have models for:
Project: a project (not really part of this question but used for
context in the question).
Paper: an academic paper description
Review: a review of a paper
... other things not pertinent.

I can list Projects and from there I can list Papers assigned to each project.
In the listing of papers I want to show how many reviews there are for
each Paper.

Paper has a ForeignKey to Project and Review has a ForeignKey to Paper.

I have this code as my view:

class PapersListView(ListView):
model = Paper
template_name = 'papers/papers_list.html'

def get_context_data(self, **kwargs):
context = super(PapersListView, self).get_context_data(**kwargs)
papers = Paper.objects.filter(project__id=self.kwargs['pk'])
review_count = Review.objects.aggregate(review_count=Count('paper'))
print(review_count)
context['papers'] = papers
context['review_count'] = review_count
return context

and this in my template:

<div class="table">
<table class="listing" cellpadding="2" cellspacing="2">
<tr>
<th>Title</th>
<th>Reviews</th>
<th>Year</th>
<th>Journal</th>
<th>Current Stage</th>
</tr>
{% for p in papers %}
<tr>
<td class="style1"><a href="#">{{p.title}}</a></td>
<td>{{p.review_count}}</td>
<td>{{p.year_published}}</td>
<td>{{p.journal}}</td>
<td>{{p.current_stage}}</td>
</tr>
{% endfor %}
</table>
</div>

The print() inserted in the view gives me this:
{'review_count': 2}

In the rendered template the Reviews column is empty.

There are two Review instances in the database. But, they are for two
separate Papers in two separate Projects. So, my guess is that I have
two problems but after scouring the docs I can't determine where the
mistakes/misunderstandings are.

Any help is appreciated.

Cheers,
Tim







--
MLHIM VIP Signup: http://goo.gl/22B0U
============================================
Timothy Cook, MSc +55 21 94711995
MLHIM http://www.mlhim.org
Like Us on FB: https://www.facebook.com/mlhim2
Circle us on G+: http://goo.gl/44EV5
Google Scholar: http://goo.gl/MMZ1o
LinkedIn Profile:http://www.linkedin.com/in/timothywaynecook

[Daniel Roseman]

You're accessing a `review_count` attribute via each Paper instance, but you haven't set up anything that would do that - you only have a single top-level `review_count` dictionary.

Instead of a separate `aggregate` call, you should be using `annotate` on the original Paper queryset:

    papers = Paper.objects.filter(project__id=self.kwargs['pk']).annotate(review_count=Count('review'))

Now each element in `papers` has a `review_count` attribute.

-- 

DR.

[Timothy W. Cook]

Hi Daniel,

I had actually tried this. However, this is a reverse relationship
The foreignkey is on Review to Paper. So if I use your suggestion or
even:
papers = Paper.objects.filter(project__id=self.kwargs['pk']).annotate(review_count=Count('review__paper'))

Which I first thought was correct. I get a FieldError:
FieldError at /papers/papers_list/1
Cannot resolve keyword 'review' into field. Choices are: abstract, authors, ...

I thought that the docs at:
https://docs.djangoproject.com/en/1.6/topics/db/aggregation/#following-relationships-backwards

say this would work but apparently Django can't find the Review->Paper
relationship.

I'm stumped.

--Tim

[Daniel Roseman]

Well, it should definitely work. Can you perhaps post your Review and Paper models?

--

DR. 

[Timothy W. Cook]

Thanks DAniel.

The models:

class Review(models.Model):
""" A review of one paper."""
INCLUDE_CHOICES = [(True,'Include'),(False,'Exclude')]
paper = models.ForeignKey(Paper, verbose_name=_('Paper'),
related_name="%(app_label)s_%(class)s_related+", null=False,
blank=False, help_text=_("Select a paper."))
title_include = models.NullBooleanField("Title",
choices=INCLUDE_CHOICES,null=False, blank=False, help_text="Select
Exclude to remove from the study after title review.")
title_exclusion_choice = models.ForeignKey(Exclusion, null=False,
blank=False, related_name="%(app_label)s_%(class)s_related+")
title_exclusion_text = models.TextField(null=False, blank=False)
abstract_include = models.NullBooleanField("Abstract",
choices=INCLUDE_CHOICES, null=False, blank=False, help_text="Select
Exclude to remove from the study after abstract review.")
abstract_exclusion_choice = models.ForeignKey(Exclusion,
null=False, blank=False,
related_name="%(app_label)s_%(class)s_related+")
abstract_exclusion_text = models.TextField(null=False, blank=False)
full_text_include = models.NullBooleanField("Fulltext",
choices=INCLUDE_CHOICES, null=False, blank=False, help_text="Select
Exclude to remove from the study after full text review.")
full_text_exclusion_choice = models.ForeignKey(Exclusion,
null=False, blank=False,
related_name="%(app_label)s_%(class)s_related+")
full_text_exclusion_text = models.TextField(null=False, blank=False)

def __str__(self):
return 'Paper: '+self.paper.title


class Paper(models.Model):

STAGES = [('Pre-Selection','Pre-Selection'), ('Selection by
Title','Selection by Title'),
('Selection by Abstract','Selection by Abstract'),
('Selection by Full Text','Selection by Full Text')]

INCLUDE_CHOICES = [(True,'Include'),(False,'Exclude')]

title = models.CharField("Title", max_length=300, help_text="")
language = models.CharField(_("language"), max_length=400,
help_text=_('Enter the language of this paper.'))
repo = models.ForeignKey(Repository,
related_name="%(app_label)s_%(class)s_related",
verbose_name=_('Repository'), null=False,
blank=False,help_text=_("Source repository added when the paper was
generated."))
project = models.ForeignKey(Project,
related_name="%(app_label)s_%(class)s_related", null=True, blank=True)
current_stage = models.CharField("Current Stage",max_length=30,
default="Pre-Selection", choices=STAGES, help_text="The current stage
for this paper.")
journal = models.ForeignKey(Journal,
related_name="%(app_label)s_%(class)s_related",
verbose_name=_('Journal Title'), null=False,
blank=False,help_text=_("Select a Journal."))
authors = models.TextField('Authors', help_text="The authors of
this paper.", null=True, blank=True)
keywords = models.TextField("Keywords", null=True, blank=True,
help_text="" )
doi = models.CharField("DOI", null=True, blank=True,
max_length=70, help_text="Enter the paper DOI")
url = models.CharField("URL", null=True, blank=True,
max_length=255, help_text="Enter the paper URL")
year_published = models.CharField("Year", db_index=True,
max_length=400, help_text="" )
volume = models.CharField("Journal Volume", null=True, blank=True,
max_length=400, help_text="" )
issue = models.CharField("Journal Issue", null=True, blank=True,
max_length=400, help_text="" )
initial_page = models.CharField("Initial Page/Page Range",
max_length=200, default=0, help_text="" )
final_page = models.CharField("Final page", max_length=200,
null=True, blank=True, default=0, help_text="" )
abstract = models.TextField("Abstract", null=True, blank=True,
help_text="Copy/paste the abstract here if it was not provide during
import.")
ca_address = models.TextField("Corresponding Author Address", default='' )
ca_email = models.EmailField("Corresponding Author Email",
max_length=255, db_index=True, null=True, blank=True, help_text="")
nonduplicate = models.NullBooleanField("Duplicate", null=True,
blank=True, choices=INCLUDE_CHOICES, help_text="Select Exclude to
remove due to duplication of papers.")
daterange = models.NullBooleanField("Date Range", null=True,
blank=True, choices=INCLUDE_CHOICES, help_text="Select Exclude to
remove due to the date of publication.")
lang_ok = models.NullBooleanField("Language ", null=True,
blank=True, choices=INCLUDE_CHOICES, help_text="Select Exclude to
remove due to the language.")
comments = models.TextField("Comments", null=True, blank=True,
help_text="Enter your comments.")

objects = PaperManager()

def __str__(self):
return self.title


The Paper Manager (used to produce the PApers from imported text files).

class PaperManager(models.Manager):
def create_paper(self, ptitle,rid,jid,jyear,jvol,jissue,lang,pid):
p = self.create(title=ptitle, repo_id=rid, journal_id=jid,
year_published=jyear,
volume=jvol, issue=jissue,language=lang,project_id=pid)
p.save()
return p

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/e3e51b9d-bd15-4aa6-b481-25c16ad62bcb%40googlegroups.com.
>
> For more options, visit https://groups.google.com/groups/opt_out.

[Timothy W. Cook]

Does the fact that I use a relat_name on the Review.paper field have
anything to do with it not working?

In the Review model, notice:

paper = models.ForeignKey(Paper, verbose_name=_('Paper'),
related_name="%(app_label)s_%(class)s_related+", null=False,
blank=False, help_text=_("Select a paper."))

In the annotation I tried:
papers =
Paper.objects.filter(project__id=self.kwargs['pk']).annotate(Count('papers_reviews_related+'))

with and without the '+'. Still get a Field Error.

Thanks,
Tim

[Daniel Roseman]

On Thursday, 21 November 2013 00:49:52 UTC, Timothy W. Cook wrote:

Does the fact that I use a relat_name on the Review.paper field have
anything to do with it not working?

In the Review model, notice:
paper = models.ForeignKey(Paper, verbose_name=_('Paper'),
related_name="%(app_label)s_%(class)s_related+", null=False,
blank=False, help_text=_("Select a paper."))

In the annotation I tried:
        papers =
Paper.objects.filter(project__id=self.kwargs['pk']).annotate(Count('papers_reviews_related+'))

The documentation for related_name (https://docs.djangoproject.com/en/1.6/ref/models/fields/#django.db.models.ForeignKey.related_name) states that if the value is, or ends with, "+", then no reverse relationship will be created. I'm not sure why you are doing that, but remove the "+" from the related_name in the ForeignKey definition.

--

DR. 

[Timothy W. Cook]

On Thu, Nov 21, 2013 at 8:25 AM, Daniel Roseman <dan...@roseman.org.uk> wrote:
> states that if the value is, or ends with, "+", then no reverse relationship
> will be created. I'm not sure why you are doing that, but remove the "+"
> from the related_name in the ForeignKey definition.

Thanks for pointing this out. I knew there had to be a reason.

Cheers,
Tim