django - how to bind records of table with calculated count values from another table
<p>In my application I have a list of trainings. One field on this list should display number of booking for each of training.
To show what I mean I prepared SQL query:</p>

<pre><code>SELECT *
FROM club_training a
LEFT JOIN
(SELECT training_id, count(*)
FROM club_booking
group by training_id) b
ON a.id = b.training_id
</code></pre>

<p>Could you give me some advice how to do it in django?
I used <code>Booking.objects.all().values('training_id').annotate(booked_amount=Count('training_id'))</code> in my code, but the result is that all count values for all trainings are displayed for each training on the list. Should be displayed one count value which is apropriate for each training.</p>

<p><a href="https://i.stack.imgur.com/nfkLF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nfkLF.png" alt="enter image description here"></a></p>

<p>views.py</p>

<pre><code>class HomePageView(TemplateView):
    """Home Page with list of trainings"""
    template_name = 'club/training_list.html'


    def get_context_data(self, **kwargs):
        now = datetime.datetime.now()
        context = super(HomePageView, self).get_context_data(**kwargs)
        context['trainings'] = Training.objects.filter(state="A", training_date__gte=now).order_by('training_date', 'start_time')
        for each_training in context['trainings']:       
            each_training.diff = each_training.availability - each_training.counter
            each_training.counter = Booking.objects.all().values('training_id').annotate(booked_amount=Count('training_id'))
        return context 
</code></pre>

<p>models.py</p>

<pre><code>class Training(models.Model):
    """Class for plan training"""
    STATE = (
        ('A', 'Active'),
        ('I', 'Inactive'),
    )
    name = models.ForeignKey('TrnDesc')
    instructor = models.ForeignKey('Instructor')
    start_time = models.TimeField(blank=True)
    end_time = models.TimeField(default='00:00:00')
    availability = models.PositiveIntegerField(default=15)
    state = models.CharField(max_length=1, choices=STATE, default='A')
    training_date = models.DateField(default=date.today)
    counter = models.PositiveIntegerField(default=0)
    def __str__(self):
        return self.name.name

class Booking(models.Model):
    """Data of people which book fitness classes"""
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    email = models.CharField(max_length=50)
    phone = models.CharField(max_length=10)
    training = models.ForeignKey('Training')
    def __str__(self):
        return self.training.name.name
</code></pre>

<p>training_list.html</p>

<pre><code>{% extends 'club/base.html' %}

{% block content %}

        &lt;ul class="nav nav-pills"&gt;
            &lt;li role="presentation" class="active"&gt;&lt;a href="#"&gt;Fitness Classes&lt;/a&gt;&lt;/li&gt;
            &lt;li role="presentation"&gt;&lt;a href="#"&gt;Join Us&lt;/a&gt;&lt;/li&gt;
            &lt;li role="presentation"&gt;&lt;a href="#"&gt;Contact Us&lt;/a&gt;&lt;/li&gt;
        &lt;/ul&gt;
        &lt;br&gt;&lt;/br&gt;
    {% regroup trainings by training_date as date_list %}
    {% for date in date_list %}
        &lt;div class="panel panel-default"&gt;


            &lt;div class="panel-heading"&gt;{{date.grouper|date:"l, d F o"}}&lt;/div&gt;


            &lt;table class="table"&gt;
                &lt;tr&gt;
                    &lt;th style="width: 20%"&gt;Training name&lt;/th&gt;
                    &lt;th style="width: 30%"&gt;Training description&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;Instructor&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;Start time&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;End time&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;Left&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;Test_counter&lt;/th&gt;
                    &lt;th style="width: 10%"&gt;Actions&lt;/th&gt;
                &lt;/tr&gt;


                {% for training in date.list %}

                &lt;tr&gt;
                    &lt;td&gt;{{training.name}}&lt;/td&gt;
                    &lt;td&gt;{{training.name.desc}}&lt;/td&gt;
                    &lt;td&gt;{{training.instructor}}&lt;/td&gt;
                    &lt;td&gt;{{training.start_time|time:"H:i"}}&lt;/td&gt;
                    &lt;td&gt;{{training.end_time|time:"H:i"}}&lt;/td&gt;
                    &lt;td&gt;{{training.diff}}&lt;/td&gt;
                    &lt;td&gt;{{training.counter}}&lt;/td&gt;
                    &lt;td&gt;&lt;a href="{% url 'book' training_id=training.pk%}"&gt;&lt;button type="button" class="btn btn-primary"&gt;Book&lt;/button&gt;&lt;/a&gt;&lt;/td&gt;
                &lt;/tr&gt;

                {% endfor %}    
            &lt;/table&gt;
        &lt;/div&gt;
    {% endfor %}

{% endblock %}
</code></pre>