Django mysql html 多表联查-处理多对多的表关系

many-to-many relationship 

已有数据库: 一本书由多个作者，一个作者写过多本书，三张表如下：

目标：想在前端页面上显示library list： 书-作者列表

作者 author	书 book	中间表book-author
姓 alastname	书名 name	book外键
名 afirstname		author外键

class Author(models.Model):

    alastname = models.CharField(max_length=30)
    afirstname = models.CharField(max_length=30)


    class Meta:
        managed = True
        db_table = 'author'

class Book(models.Model):
    name = models.CharField(max_length=30)
 

    class Meta:
        managed = True
        db_table = 'book'

class BookAuth(models.Model):
    book = models.ForeignKey(to="Book",to_field="id",on_delete=models.DO_NOTHING)
    author = models.ForeignKey(to="Author",to_field="id",on_delete=models.DO_NOTHING)

    class Meta:
        managed = True
        db_table = 'book_auth'

新建一个大的字典列表showlist[ ]，每一个字典对象show{ }存入书名和对应的作者列表

bookset为bookauth中间表的对象，

book为bookauth表的每一行, book.外键属性即可进入外表，再.外表内的属性则可获得值

book.book.book_name：通过book外键进入book表，获得book_name属性

最终showlist[ ]输出结果为：

[{'id':1,'bookid':11,'bookname':'data structure','authorname':['author1','author2']},

{{'id':2,'bookid':12,'bookname':'algorithm','authorname':['author2','author3']},]

def library(request):
    showlist = []
    bookset = models.BookAuth.objects.all()

    for book in bookset:
        author = []
        show = {}

        show['id'] = book.id #中间表每一行自己的ID
        show['bookid'] = book.book_id #book为中间表的对象，通过“.book”外键进入book表
        show['bookname'] = book.book.book_name #通过“.book”外键进入book表 获取book表的属性
        authorfname = book.author.afirstname #book为中间表的对象，通过“.author”外键进入author表
        authorlname = book.author.alastname
        authorname = ('%s %s' % (authorfname, authorlname)) #字符串连接姓和名

        author.append(authorname) #加入这本书的作者列表

        show['authorname'] = author 

#作者列表去重处理
    for i in range(0, len(showlist) - 2):
        for j in range(len(showlist) - 1, i, -1):
            if showlist[j]['bookid'] == showlist[i]['bookid']:
                showlist[i]['authorname'].append(showlist[j]['authorname'][0])
                authorset = list(set(showlist[i]['authorname']))
                showlist[i]['authorname'] = authorset
                print(showlist[i]['bookid'], authorset)
                showlist.pop(j)

    return render(request, 'library.html', {'showlist': showlist})

将showlist[ ]返回给html页面，输出一个三列的table

<table class="table table-hover">

        <tr>
            <th>Book</th>
            <th>Name</th>
            <th>Author</th>
          

        </tr>
        {% for show in showlist %}
            <tr>
                <td>{{ show.bookid }}</td>
                <td>{{ show.bookname }}</td>


                <td>{% for author in show.authorname %}
                    <span>{{ author }}</span>
                    <span>/</span>
                {% endfor %}
                </td>

            </tr>

        {% endfor %}

    </table>