如何从另一个sql表中获取值并在jinja中显示它？

Question

这是我的用户表：

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    karma INT NOT NULL DEFAULT '0',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)COLLATE='utf8mb4_general_ci';

我就是这样在烧瓶里得到我的评论的：

class comments():
    def gettcomm(id):
        cur = mysql.connection.cursor()
        cur.execute("""
            SELECT  UserComment.*, (COUNT(Ghost.id) - 1) AS depth
            FROM (comments AS UserComment, comments as Ghost)

            WHERE   UserComment.lft BETWEEN Ghost.lft AND Ghost.rgt
            AND     Ghost.post_id = %s
            AND     UserComment.post_id =%s
            AND     UserComment.parent_id is not null

            GROUP BY UserComment.id
            ORDER BY UserComment.lft""", ([id], [id]))
        commentaria = cur.fetchall()
        cur.close()
        return commentaria

commentaria = comments.gettcomm(id)

我想从用户表中为每个用户获取业力，并如下所示：

{% for item in commentaria %} 
            {{item['username']}} 's karma is: {{item['karma']}}
        {% endfor %}

Answer 1

您应该能够像这样在SQL端连接两个表：

SELECT UserComment.*, karma, (COUNT(Ghost.id) - 1) AS depth
FROM (comments AS UserComment, comments as Ghost)
JOIN users ON users.id = UserComment.user_id
WHERE UserComment.lft BETWEEN Ghost.lft AND Ghost.rgt
  AND Ghost.post_id = %s
  AND UserComment.post_id = %s
  AND UserComment.parent_id is not null
GROUP BY UserComment.id
ORDER BY UserComment.lft

但是，我只能猜测user_id字段的存在和名称

我可能会这样写：

SELECT UserComment.*, karma, (COUNT(Ghost.id) - 1) AS depth
FROM comments AS UserComment
JOIN comments AS Ghost ON UserComment.lft BETWEEN Ghost.lft AND Ghost.rgt
JOIN users ON users.id = UserComment.user_id
WHERE Ghost.post_id = %s
  AND UserComment.post_id = %s
  AND UserComment.parent_id is not null
GROUP BY UserComment.id
ORDER BY UserComment.lft