Sqlalchemy: Filter Many-to-one Relationship Where The One Object Has A List Containing A Specific Value

I have some tables like this: 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.

Solution 1:

With this test data:

# Test Data
artists = [
    Artist(
        name='shouldmatch 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')))
assertlen(q.all()) == 2

Solution 2:

After some more research, I found out one way to approach this problem, although a bit differently than what I wanted.

First, to get all the Artists that contain a specific Genre, I executed:

artists = Artist.query.filter(
    Artist.genres.any(Genre.name.like('genre_name'))).all()

Then, I iterated through each Artist, and queried the Song model by using the artist as a keyword expression in the filter, like so:

for a in artist:
    most_common_genre_songs = Song.query.filter_by(artist=a).all()

I am not sure if there is a more efficient way to make this call, or do it in a one-liner (I'm betting there is), but for now, this will do.