title: Inner join two querysets
body: <p>I have three models
  <strong>Learner</strong>, <strong>Level</strong> and the through model <strong>LearnerLevel</strong>.</p>
  
  <p>The models look like this:</p>
  
  <pre><code>class Learner(models.Model):
      user = models.OneToOneField(User, on_delete=models.CASCADE)
      words = models.ManyToManyField('Word', through='LearnerWord', related_name='Learners')
      levels = models.ManyToManyField('Level', through='LearnerLevel', related_name='Learners')
  
      def __str__(self):
          return self.user.username
  class Level(models.Model):
      name = models.CharField(max_length=100)
      selectedByUser = models.BooleanField(default=False)
      words = models.ManyToManyField('Word', through='LevelWord', related_name='levels')
      countWords = models.IntegerField(default=0)
      language = models.ForeignKey('Language', related_name='languagelevels', on_delete=models.CASCADE, null=True,
                                   blank=True)
  
      def __str__(self):
          return self.name
  
  class LearnerLevel(models.Model):
      learner = models.ForeignKey('Learner', related_name='learnerlevels', on_delete=models.SET_NULL, null=True)
      level = models.ForeignKey('Level', related_name='learnerlevels', on_delete=models.SET_NULL, null=True, blank=True)
      knownWords = models.IntegerField(default=0)
  
      def __str__(self):
          return self.learner.user.username + ' ' + self.level.name
  </code></pre>
  
  <p>The tables look like this:</p>
  
  <pre><code>SELECT * FROM app_level;
  
  id|name          |countWords|language_id|selectedByUser
  1 |A1            |0         |1          |0
  2 |A1 Supermarkt |0         |1          |0
  3 |A1            |22        |2          |0
  4 |A1 Albanisch1 |20        |4          |0
  5 |A1 Albanisch2 |17        |4          |0
  6 |A1 Albanisch3 |21        |4          |0
  7 |A1 Albanisch4 |20        |4          |0
  8 |A1 Albanisch4 |20        |4          |0
  9 |A1 Albanisch5 |20        |           |0
  10|A1 Albanisch7 |20        |4          |0
  11|A1 Albanisch8 |20        |4          |0
  12|A1 Albanisch9 |20        |4          |0
  13|A1 Albanisch10|20        |4          |0
  14|A1 Albanisch11|20        |4          |0
  15|A1 Albanisch12|20        |4          |0
  
  SELECT * FROM wordapp_learnerlevel;
  
  id|knownWords|learner_id|level_id
  1 |8         |1         |4
  2 |16        |1         |5
  3 |3         |1         |6
  4 |8         |1         |1
  5 |8         |2         |4
  </code></pre>
  
  <p>My first question is, how can I achieve the following with Django?</p>
  
  <pre><code>SELECT wordapp_level.name, 
         wordapp_level.countWords, 
         wordapp_learnerlevel.knownWords 
  FROM   wordapp_level 
  INNER JOIN wordapp_learnerlevel 
         ON wordapp_level.id = wordapp_learnerlevel.level_id;
  
  name         |countWords|knownWords
  A1 Albanisch1|20        |8
  A1 Albanisch2|17        |16
  A1 Albanisch3|21        |3
  A1           |0         |8
  A1 Albanisch1|20        |8
  </code></pre>
  
  <p>My goal is to get a new queryset that I can give to a modelview in the get_queryset function from the django-rest-framework.
  My second question is, is that good practice?</p>
  
  <p>I want something like this:</p>
  
  <pre><code>def get_queryset(self):
      learnerStatistic = LearnerLevel.objects.select_related('level')
      return learnerStatistic
  </code></pre>
  
  <p>So for example the learner has learned from level with the id 1 2 3. Now in learnerlevel the knownWords changed where the level_id is 1 2 3. Now I want to give the user a statistic. "You learned <strong>learnerlevel.knownWords</strong> of <strong>level.countWords</strong> from <strong>level.name</strong>"</p>
  
  <p>It is not clear to me from the Django documentation</p>
  
  <p>========== EDIT ==========</p>
  
  <p>I tried</p>
  
  <pre><code>str(LearnerLevel.objects.select_related('level').query)
  </code></pre>
  
  <p>I get</p>
  
  <pre><code>SELECT "wordapp_learnerlevel"."id", "wordapp_learnerlevel"."learner_id", "wordapp_learnerlevel"."level_id", "wordapp_learnerlevel"."knownWords", "wordapp_level"."id", "wordapp_level"."name", "wordapp_level"."selectedByUser", "wordapp_level"."countWords", "wordapp_level"."language_id" FROM "wordapp_learnerlevel" LEFT OUTER JOIN "wordapp_level" ON ("wordapp_learnerlevel"."level_id" = "wordapp_level"."id")'
  </code></pre>
  
  <p>but when</p>
  
  <p>str(LearnerLevel.objects.select_related('level').values().query)</p>
  
  <p>I get</p>
  
  <pre><code>SELECT "wordapp_learnerlevel"."id", "wordapp_learnerlevel"."learner_id", "wordapp_learnerlevel"."level_id", "wordapp_learnerlevel"."knownWords" FROM "wordapp_learnerlevel"
  </code></pre>
  
  <p>but I just want <strong>learnerlevel.knownWords</strong>, <strong>level.countWords</strong>, <strong>level.name</strong></p>
  
  <p>If I do</p>
  
  <pre><code>qs = LearnerLevel.objects.select_related('level').only('knownWords','level__name','level__countWords')
  </code></pre>
  
  <p>then</p>
  
  <pre><code>qs.values()
  </code></pre>
  
  <p>is</p>
  
  <pre><code>&lt;QuerySet [{'id': 1, 'learner_id': 1, 'level_id': 4, 'knownWords': 8}, {'id': 2, 'learner_id': 1, 'level_id': 5, 'knownWords': 16}, {'id': 3, 'learner_id': 1, 'level_id': 6, 'knownWords': 3}, {'id': 4, 'learner_id': 1, 'level_id': 1, 'knownWords': 8}, {'id': 5, 'learner_id': 2, 'level_id': 4, 'knownWords': 8}]&gt;
  </code></pre>
  
  <p>But I expect it to be</p>
  
  <pre><code>&lt;QuerySet [{'id': 1, 'name': 'A1', 'knownWords': 8, 'countWords': xx}, .....]&gt;
  </code></pre>
  
  <p>====== Solution ======</p>
  
  <p>I found the solution</p>
  
  <pre><code>def get_queryset(self):
      currLearner = Learner.objects.get(user=self.request.user)
      return currLearner.learnerlevels.values('knownWords', 'level__name', 'level__countWords')
  </code></pre>
  
  <p>and the serializer is</p>
  
  <pre><code>class LearnerLevelXXSerializer(serializers.Serializer):
      knownWords = serializers.IntegerField()
      level__name = serializers.CharField(max_length=200)
      level__countWords = serializers.IntegerField()
  </code></pre>
  
  <p>Thank you Denis Cornehl. Your advice helped me</p>