django—如何使用另一个表的计算计数值绑定表的记录

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:

在我的申请中，我有一份培训清单。这个列表中的一个字段应该显示每个培训的预约数量。为了说明我准备的SQL查询:

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

Could you give me some advice how to do it in django? I used Booking.objects.all().values('training_id').annotate(booked_amount=Count('training_id')) 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.

你能给我一些建议吗?我在代码中使用了Booking.objects.all().values('training_id').annotate(booked_amount=Count('training_id')，但结果是，所有训练的Count值都显示在列表中的每个训练中。应该显示一个计数值，这是每个训练的固有值。

views.py

views.py

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 

models.py

models.py

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

training_list.html

training_list.html

{% extends 'club/base.html' %}

{% block content %}

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


            <div class="panel-heading">{{date.grouper|date:"l, d F o"}}</div>


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


                {% for training in date.list %}

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

                {% endfor %}    
            </table>
        </div>
    {% endfor %}

{% endblock %}

2 个解决方案

#1

 for each_training in context['trainings']:       
        each_training.diff = each_training.availability - each_training.counter
        each_training.counter = Booking.objects.filter(training_id=each_training.id).count()  # just modify this line
    return context 

#2

I would approach from the other direction: each_training.booking_set.count()

我将从另一个方向开始:each_train .booking_set.count()

Also, I'm not sure it fits your database goals, but if I understand what you are looking for, you could set it up like this:

另外，我不确定它是否适合您的数据库目标，但是如果我理解您的需求，您可以这样设置:

models.py

models.py

class Training(models.Model):
    ...
    <fields>
    ...
    def __str__(self):
        return self.name.name

    @property
    def counter(self):
        return self.booking_set.count()

    @property
    def diff(self):
        return self.availability - self.counter

Like this, the values can go directly from the model to the template.

像这样，值可以直接从模型到模板。

The only problem that I can see with this is that I think it prevents these fields from being part of a queryset. For example, Training.objects.filter(counter__gt=0) won't work. If you need that, I think you'll still have to find an opportunity to save and update the value in the database, possibly using a signal, that way you don't have to save the value over and over again every time the view is called.

我能看到的唯一问题是，我认为它阻止了这些字段成为queryset的一部分。例如，Training.objects.filter(counter__gt=0)不能工作。如果需要，我认为您仍然需要找到保存和更新数据库中的值的机会，可能使用一个信号，这样您就不必在每次调用视图时反复保存该值。

It also looks like you could use a manager to handle some of the logic you are doing in the view:

看起来你还可以使用一个管理器来处理视图中的一些逻辑:

managers.py

managers.py

from django.db.models import Manager

class Active(Manager):

    def by_date(self, date)
        Training.objects.filter(state="A", training_date__gte=date).order_by('training_date', 'start_time')

Then you can add the manager to your model (being sure to preserve your vanilla manager):

然后你可以将经理添加到你的模型中(确保保留你的普通经理):

models.py

models.py

from .managers import Courses

class Training(models.Model):
    ...
    <fields>
    objects = models.Manager()
    active = Active()
    ...

    def __str__(self):
        return self.name.name

And now you can distribute all the information with a slim ListView.

现在，您可以使用瘦列表视图分发所有信息。

from django.views.generic import ListView

class HomePageView(ListView):
    """Home Page with list of trainings"""
    template_name = 'club/training_list.html'
    context_object_name = "trainings"

    def get_queryset(self):
        now = datetime.datetime.now()
        return Trainings.active.by_date(now)

I'm not familiar with some of the things you are doing in the template, but everything should work the same way it does now.

我不熟悉您在模板中所做的一些事情，但是所有事情都应该按照现在的方式进行。

Or maybe I'm way off, but hopefully its some good food for thought :)

又或者我走得太远了，但希望这是值得思考的好东西。