Joining two querysets in Django
<p>Suppose I have the following models</p>

<pre><code>class Award(models.Model):
    user = models.ForeignKey(User)


class AwardReceived(models.Model):
    award = models.ForeignKey(award)
    date = models.DateField()
    units = models.IntegerField()


class AwardUsed(models.Model):
    award = models.ForeignKey(award)
    date = models.DateField()
    units = models.IntegerField()  
</code></pre>

<p>Now, suppose I want to get the number of awards for all users and the number of awards used for all users (ie, a queryset containing both). I prefer to do it one query for each calculation - when I combined it in my code I had some unexpected results. Also for some of my queries it won't be possible to do it one query, since the query will get too complex - I'm calculating 8 fields. This is how I solved it so far:</p>

<pre><code>def get_summary(query_date)
    summary = (Award.objects.filter(awardreceived__date__lte=query_date))
                    .annotate(awarded=Sum('awardissuedactivity__units_awarded')))

    awards_used = (Award.objects.filter(awardused__date__lte=query_date)
                        .annotate(used=Sum('awardused__date__lte__units')))

    award_used_dict = {}
    for award in awards_used:
        award_used_dict[award] = award.used

    for award in summary:
        award.used = award_used_dict.get(award, 0)

    return summary
</code></pre>

<p>I'm sure there must be a way to solve this without the dictionary approach? For instance, something like this: <code>awards_used.get(award=award)</code>, but this causes a db lookup every loop.</p>

<p>Or some other fancy way to join the querysets?</p>

<p>Note this is a simplified example and I know for this example the DB structure can be improved, I'm just trying to illustrate my question.</p>