sqlalchemy通过和限制其他表对多对多查询进行排序

我试着加入3个表，（电影，角色，演员）。我的代码执行时没有错误，但是当我试图打印数据时，我只能访问第一个表上的信息。

role = db.Table('role',
                 db.Column('movie_id', db.Integer, db.ForeignKey('movie.id')),
                 db.Column('actor_id', db.Integer, db.ForeignKey('actor.id')))

class Movie(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(20), nullable=False)
    genre = db.Column(db.Text)
    year = db.Column(db.Integer)
    characters = db.relationship('Actor',lazy='dynamic',
                         secondary=role,
                         backref=db.backref('movies', lazy='dynamic')) #  updated

class Actor(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(200), nullable=False)
    picture = db.Column(db.String(200), nullable=False)
    age = db.Column(db.Integer)

query = Movie.query.join(role).join(Actor).filter_by(Movie.year==1974)

显示查询后，我将无法访问有关参与者的信息。
编辑：我希望能够过滤、限制和排序联接的actors表。例子： query = Movie.query.join(role).join(Actor).order_by(Actor.age.desc()).limit(4).filter_by(Movie.year==1974) 更新：我正在尝试将snakecharmerb提供的原始sql查询转换为sqlalchemy，我遇到了障碍。到目前为止，我的情况是：

query = select(select(Movie.title, Actor.first_name, Actor.age, func.row_number().over(partition_by=role.c.movie_id, order_by=Actor.age.desc())).label("rn")
                   {???????}.label("m")
                   .join(role, (role.c.movie_id == movie.id))
                   .join(Actor, (role.c.actor_id == actor.id)))
                   .where("rn <= 4")

我试过用 .select_from() 代替 {???????} 但我没试着把它放进盒子里 .select_from() 成功了。

更新2：

我想我做到了，我用了这个问题：

sub_query = db.session.query(Movie.title, Actor.first_name, Actor.age,
func.row_number().over(partition_by=role.c.movie_id).label('rn'))\
    .join(role, (role.c.movie_id == movie.id))
    .join(Actor, (role.c.actor_id == actor.id)).subquery()
query = db.session.query(sub_query).filter(text('rn <= 4'))

但我（希望）还有最后一个问题。返回的数据是一个平面列表

[
    ('movie name 1', "actor name 1", 1),
    ('movie name 1', 'actor name 2', 2),
    ('movie name 1', 'actor name 3', 3),
    ('movie name 1', 'actor name 4', 4),
    ('movie name 2', 'actor name 1', 1),
    ('movie name 2', 'actor name 7', 2),
    ('movie name 2', 'actor name 4', 3),
    ('movie name 2', 'actor name 2', 4),
    ('movie name 3', 'actor name 8', 1),
    ('movie name 3', 'actor name 9', 2)

我希望我能得到这样的东西

('movie name 1', ["actor name 1",
                      'actor name 2',
                      'actor name 3',
                      'actor name 4']),
    ('movie name 2', ['actor name 1',
                      'actor name 7',
                      'actor name 4',
                      'actor name 2']),
    ('movie name 3', ['actor name 8',
                      'actor name 9']),
]

我想我可以用一个 .group_by() 但我一直没能让它起作用。

他们之间的关系 Movie 以及 Actor 需要重新定义，以便在 Movie 侧（我也重命名了backref，所以你可以 Actor.movies ):

characters = db.relationship('Actor',
                             lazy='dynamic',
                             secondary=role,
                             backref=db.backref('movies', lazy='dynamic'))

一旦完成， Movie.characters 可以为每部电影查询（本例是纯sqlalchemy，但转换为flask sqlalchemy应该不难）：

from sqlalchemy import orm

query = session.query(Movie).filter(Movie.year == 1974)
for movie in query:
    print(movie.title)
    for actor in movie.characters.order_by(Actor.age.desc().limit(4):
        print(actor.first_name, actor.age)
    print()

就效率而言，限制每部电影返回的演员数量是好的，但是使用动态加载意味着我们生成一个查询来获取电影，然后生成一个查询来获取每部电影的角色（这是n+1问题）。这也不是很有效。
使用纯sql，可以通过以下查询检索所需的数据：

SELECT * FROM (SELECT m.title, a.first_name, a.age, ROW_NUMBER() OVER (PARTITION BY r.movie_id ORDER BY a.age DESC) AS rn
FROM movies m 
  JOIN role r ON r.movie_id = m.id
  JOIN actors a ON r.actor_id = a.id 
WHERE m.year = 1974) 
dummy WHERE rn <= 4;

sqlalchemy通过和限制其他表对多对多查询进行排序

更新2：

1条答案

相关问题

热门标签

最新问答