Django入门与实践-第20章：QuerySets（查询结果集）

    有3个任务：

    • 显示每个板块的总主题数
    • 显示每个板块的总回复数
    • 显示每个板块的最后发布者和日期

    在实现这些功能前，我们先使用Python终端

    因为我们要在Python终端尝试，所以，把所有的 models 定义一个 方法是个好主意

    boards/models.py()

    在 Post 模型中，使用了 Truncator 工具类，这是将一个长字符串截取为任意长度字符的简便方法（这里我们使用30个字符）

    现在打开 Python shell 

    1. python manage.py shell
    2. from boards.models import Board
    4. # First get a board instance from the database
    5. board = Board.objects.get(name='Django')

    这三个任务中最简单的一个就是获取当前版块的总主题数，因为 Topic 和 Baoard 是直接关联的。

    1. board.topics.all()
    2. <QuerySet [<Topic: Hello everyone!>, <Topic: Test>, <Topic: Testing a new post>, <Topic: Hi>]>
    4. board.topics.count()
    5. 4

    就这样子。

    现在统计一个版块下面的回复数量有点麻烦，因为回复并没有和 Board 直接关联

    1. from boards.models import Post
    3. Post.objects.all()
    4. <QuerySet [<Post: This is my first topic.. :-)>, <Post: test.>, <Post: Hi everyone!>,
    5.   <Post: New test here!>, <Post: Testing the new reply feature!>, <Post: Lorem ipsum dolor sit amet,...>,
    6.   <Post: hi there>, <Post: test>, <Post: Testing..>, <Post: some reply>, <Post: Random random.>
    7. ]>
    9. Post.objects.count()
    10. 11

    这里一共11个回复，但是它并不全部属于 “Django” 这个版块的。

    我们可以这样来过滤

    1. from boards.models import Board, Post
    3. board = Board.objects.get(name='Django')
    5. Post.objects.filter(topic__board=board)
    6. <QuerySet [<Post: This is my first topic.. :-)>, <Post: test.>, <Post: hi there>,
    7.   <Post: Hi everyone!>, <Post: Lorem ipsum dolor sit amet,...>, <Post: New test here!>,
    8.   <Post: Testing the new reply feature!>
    9. ]>
    11. Post.objects.filter(topic__board=board).count()
    12. 7

    最后一个任务是标识版块下面的最后一条回复

    1. # order by the `created_at` field, getting the most recent first
    2. Post.objects.filter(topic__board=board).order_by('-created_at')
    3. <QuerySet [<Post: testing>, <Post: new post>, <Post: hi there>, <Post: Lorem ipsum dolor sit amet,...>,
    4.   <Post: Testing the new reply feature!>, <Post: New test here!>, <Post: Hi everyone!>,
    5.   <Post: test.>, <Post: This is my first topic.. :-)>
    6. ]>
    8. # we can use the `first()` method to just grab the result that interest us
    9. Post.objects.filter(topic__board=board).order_by('-created_at').first()
    10. <Post: testing>

    太棒了，现在我们来实现它

    boards/models.py (完整代码)

    注意，我们使用的是self，因为这是Board的一个实例方法，所以我们就用这个Board实例来过滤这个 QuerySet

    现在我们可以改进主页的HTML模板来显示这些新的信息

    templates/home.html

    2. {% extends 'base.html' %}
    4. {% block breadcrumb %}
    5.   <li class="breadcrumb-item active">Boards</li>
    6. {% endblock %}
    8. {% block content %}
    9.   <table class="table">
    10.     <thead class="thead-inverse">
    12.         <th>Posts</th>
    13.         <th>Topics</th>
    14.         <th>Last Post</th>
    15.       </tr>
    16.     </thead>
    17.     <tbody>
    18.       {% for board in boards %}
    19.         <tr>
    20.           <td>
    21.             <a href="{% url 'board_topics' board.pk %}">{{ board.name }}</a>
    22.             <small class="text-muted d-block">{{ board.description }}</small>
    23.           </td>
    24.           <td class="align-middle">
    25.             {{ board.get_posts_count }}
    26.           </td>
    27.           <td class="align-middle">
    28.             {{ board.topics.count }}
    29.           </td>
    30.           <td class="align-middle">
    31.             {% with post=board.get_last_post %}
    32.               <small>
    33.                 <a href="{% url 'topic_posts' board.pk post.topic.pk %}">
    34.                   By {{ post.created_by.username }} at {{ post.created_at }}
    35.                 </a>
    36.               </small>
    37.             {% endwith %}
    38.           </td>
    39.         </tr>
    40.       {% endfor %}
    41.     </tbody>
    42.   </table>
    43. {% endblock %}

    现在是这样的效果

    5-14.png

    运行测试：

    1. python manage.py test
    1. Creating test database for alias 'default'...
    2. System check identified no issues (0 silenced).
    3. .......................................................EEE......................
    4. ======================================================================
    5. ERROR: test_home_url_resolves_home_view (boards.tests.test_view_home.HomeTests)
    6. ----------------------------------------------------------------------
    7. django.urls.exceptions.NoReverseMatch: Reverse for 'topic_posts' with arguments '(1, '')' not found. 1 pattern(s) tried: ['boards/(?P<pk>\\d+)/topics/(?P<topic_pk>\\d+)/$']
    9. ======================================================================
    10. ERROR: test_home_view_contains_link_to_topics_page (boards.tests.test_view_home.HomeTests)
    11. ----------------------------------------------------------------------
    12. django.urls.exceptions.NoReverseMatch: Reverse for 'topic_posts' with arguments '(1, '')' not found. 1 pattern(s) tried: ['boards/(?P<pk>\\d+)/topics/(?P<topic_pk>\\d+)/$']
    14. ======================================================================
    15. ERROR: test_home_view_status_code (boards.tests.test_view_home.HomeTests)
    16. ----------------------------------------------------------------------
    17. django.urls.exceptions.NoReverseMatch: Reverse for 'topic_posts' with arguments '(1, '')' not found. 1 pattern(s) tried: ['boards/(?P<pk>\\d+)/topics/(?P<topic_pk>\\d+)/$']
    19. ----------------------------------------------------------------------
    20. Ran 80 tests in 5.663s
    22. Destroying test database for alias 'default'...

    看起来好像有问题，如果没有回复的时候程序会崩溃

    templates/home.html

    2.   {% if post %}
    3.     <small>
    4.       <a href="{% url 'topic_posts' board.pk post.topic.pk %}">
    5.         By {{ post.created_by.username }} at {{ post.created_at }}
    6.       </a>
    7.     </small>
    8.   {% else %}
    9.     <small class="text-muted">
    10.       <em>No posts yet.</em>
    11.     </small>
    12.   {% endif %}
    13. {% endwith %}

    再次运行测试：

    1. python manage.py test

    现在是时候来改进回复列表页面了。

    5-16.png

    现在，我将告诉你另外一种方法来统计回复的数量，用一种更高效的方式

    和之前一样，首先在Python shell 中尝试

    1. python manage.py shell
    1. from django.db.models import Count
    2. from boards.models import Board
    4. board = Board.objects.get(name='Django')
    6. topics = board.topics.order_by('-last_updated').annotate(replies=Count('posts'))
    8. for topic in topics:
    9.     print(topic.replies)
    11. 2
    12. 4
    13. 2
    14. 1

    这里我们使用annotate ，QuerySet将即时生成一个新的列，这个新的列，将被翻译成一个属性，可通过 topic.replies来访问，它包含了指定主题下的回复数。

    我们来做一个小小的修复，因为回复里面不应该包括发起者的帖子

    1. topics = board.topics.order_by('-last_updated').annotate(replies=Count('posts') - 1)
    3. for topic in topics:
    4.     print(topic.replies)
    6. 1
    7. 3
    8. 1
    9. 0

    很酷，对不对？

    boards/views.py ()

    1. from django.db.models import Count
    2. from django.shortcuts import get_object_or_404, render
    3. from .models import Board
    5. def board_topics(request, pk):
    6.     board = get_object_or_404(Board, pk=pk)
    7.     topics = board.topics.order_by('-last_updated').annotate(replies=Count('posts') - 1)
    8.     return render(request, 'topics.html', {'board': board, 'topics': topics})

    templates/topics.html(完整代码)

    2. {% for topic in topics %}
    3.   <tr>
    4.     <td><a href="{% url 'topic_posts' board.pk topic.pk %}">{{ topic.subject }}</a></td>
    5.     <td>{{ topic.starter.username }}</td>
    6.     <td>{{ topic.replies }}</td>
    7.     <td>0</td>
    8.     <td>{{ topic.last_updated }}</td>

    下一步是修复主题的查看次数，但是，现在我们需要添加一个新的字段