SQLAlchemy:过滤多对一关系，其中一个对象具有包含特定值的列表 [英] SQLAlchemy: filter many-to-one relationship where the one object has a list containing a specific value

问题描述

我有一些这样的表:

class Genre(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), index=True)
    artist_id = db.Column(db.Integer, db.ForeignKey('artist.id'))

class Song(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), index=True)
    artist = db.relationship('Artist', uselist=False)
    artist_id = db.Column(db.Integer, db.ForeignKey('artist.id'))

class Artist(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), index=True)
    genres = db.relationship('Genre')
    songs = db.relationship('Song')

因此，基本上，歌曲只有一位歌手.每个艺术家可以有多种流派.

So basically, Songs have one Artist. And each Artist can have multiple Genres.

我正在尝试获取所有艺术家的所有歌曲，这些艺术家的类型列表中包含特定名称的类型.我做了一些研究，发现非常接近:

I am trying to get all Songs by any Artist whose list of Genre's contains a Genre by a specific name. I did some researching, and I found something very close:

Song.query.filter(Artist.genres.any(Genre.name.in_([genre_name_im_looking_for])))

这可以解决问题，但并非在所有情况下都可以.例如，上述声明还将返回所有具有流派独立摇滚"的艺术家的歌曲.如何指定我不希望流派名称出现在值列表中而是特定值?

This will sort of work, but not for all cases. For example, the above statement will also return all Songs with Artists who have the Genre 'indie rock'. How can I specify that I don't want the Genre name to be in a list of values, but to be a specific value?

Song.query.filter(Artist.genres.any(Genre.name='rock'))

也是不可能的，因为它是关键字表达式.

is also not possible because it is a keyword expression.

有什么想法吗?

推荐答案

使用以下测试数据:

# Test Data
artists = [
    Artist(
        name='should match rock',
        genres=[Genre(name='rock'), Genre(name='pop')],
        songs=[Song(name='love'), Song(name='hate')]
    ),
    Artist(
        name='should NOT match',
        genres=[Genre(name='indie rock')],
        songs=[Song(name='elsewhere')]
    ),
]

db.session.add_all(artists)
db.session.commit()

以下查询应满足您的要求

Query below should do what you want:

q = Song.query.filter(Song.artist.has(Artist.genres.any(Genre.name == 'rock')))
assert len(q.all()) == 2

这篇关于SQLAlchemy:过滤多对一关系，其中一个对象具有包含特定值的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！