SQLAlchemy: filter many-to-one relationship where the one object has a list containing a specific value
Solution 1
With this test data:
# 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
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.
Related videos on Youtube
Jakeway
Updated on September 15, 2022Comments
-
Jakeway over 1 year
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.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.
Any ideas?
-
Alec over 8 yearsShouldn't it be
Song.query.filter(Artist.genres.any(Genre.name == 'rock'))
? (comparison rather than assignment)
-
-
Jakeway over 8 yearsThis is exactly what I was looking for, thanks. Quick question for you, I also have another table, Users, which has a one to many relationship with songs, and I'm now looking to augment your suggested query to filter the songs that belong to a specific user. This query works
Song.query.filter(Song.artist.has( Artist.genres.any(Genre.name == 'genre'))).filter( Song.user == some_user_object).all()
What kind of effect would swapping the filter statements have on performance? -
van over 8 yearsI do not think that there would be any performance difference in any modern RDBMS.
-
Alex F over 3 yearsGreat solution.