title: Django: how to join and annotate multiple sql tables?
body: <p>I'm building a sample django app and I can't aggregate properly some sql results.</p>
  
  <p>Consider these 3 models:</p>
  
  <p><strong>Movie model</strong></p>
  
  <pre><code>class Movie(models.Model):
      source_id = models.CharField(max_length=100, primary_key=True)
      title = models.CharField(max_length=200, validators=[MinLengthValidator(1)])
  </code></pre>
  
  <p><strong>Rating model</strong></p>
  
  <pre><code>class Rating(models.Model):
      movie = models.ForeignKey(Movie, on_delete=models.CASCADE)
      rating = models.PositiveIntegerField()
      username = models.CharField(max_length=100, validators=[MinLengthValidator(1)])
  </code></pre>
  
  <p><strong>Comment model</strong></p>
  
  <pre><code>class Comment(models.Model):
      movie = models.ForeignKey(Movie, on_delete=models.CASCADE)
      username = models.CharField(max_length=100, validators=[MinLengthValidator(1)])
      body = models.TextField()
  </code></pre>
  
  <p>Then consider the following mysql tables:</p>
  
  <p><strong>movie</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>+-----------+-----------+
  | source_id |   title   |
  +-----------+-----------+
  | 15sdfsd4  | Spiderman |
  +-----------+-----------+
  </code></pre>
  
  <p><strong>rating</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>+----+--------+----------+----------+
  | id | rating | username | movie_id |
  +----+--------+----------+----------+
  |  1 |      4 | jack     | 15sdfsd4 |
  |  2 |      3 | mick     | 15sdfsd4 |
  +----+--------+----------+----------+
  </code></pre>
  
  <p><strong>comment</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>+----+----------+--------------------+----------+
  | id | username |        body        | movie_id |
  +----+----------+--------------------+----------+
  |  1 | charles  | I loved this movie | 15sdfsd4 |
  |  2 | mick     | Nice sound fx      | 15sdfsd4 |
  +----+----------+--------------------+----------+
  </code></pre>
  
  <p>I would like to query for a list of movie ids and get a summary of the avg rating and the nr of comments.</p>
  
  <p>I tried something like</p>
  
  <pre><code>ids = ['15sdfsd4','54fdf5d']
  m = Movie.objects.filter(source_id__in=ids).annotate(Avg('rating'), Count('comment'))
  </code></pre>
  
  <p>I would expect a comment count of 2 and an avg rating of 3.5 for the movie with id <em>15sdfsd4</em>. Instead I get a comment count of 4 and an avg rating of 1.5 which I can't make much sense of...</p>
  
  <p>Do you have any suggestion? Thanks</p>