SQLAlchemy Join with optional parameters

Question

I've got the following Query in SQLAlchemy in Python:

 db_data = session.query(Artist.name.label("artist"), Song.title.label("song_title"),
           SongDictionary.word, SongDictionary.count.label("word_count"), 
           SongDictionary.word_type, SongDictionary.word_length).join(
           Song, Song.artist_id == Artist.id).join(SongDictionary, SongDictionary.song_id == 
           Song.id).all()

Now i would like to add 4 optional filters.

1. Artist.name
2. Song.title
3. SongDictionary.word
4. SongDictionary.wordlength

I don't want to use a if condition for every possible argument and change the query if an argument is None or not.

So i read about to pass the arguments via **kwargs and use filter_by instead of filter.

I tried it like this:

    kwargs = {}
    if filter_artist: kwargs["Artist.name"] = filter_artist
    if filter_song: kwargs["Song.title"] = filter_song
    if filter_word: kwargs["SongDictionary.word"] = filter_word
    if filter_wordlength: kwargs["SongDictionary.length"] = filter_wordlength

Added filter_by(**kwargs) before .all()

But when i am trying to pass the arguments via **kwargs it seems i have to put the arguments directly behind the join of the table, to access the columns of this table. So i have to create 3 filter variables. Would be an oppurtunity too, but i am also not able to filter_by the Artist table in my case.

I am sure there must be better way. Thank you!

Answer 1

got it.. found help here Flask SqlAlchemy how to call filter_by with **kwargs and column like expressions

in my case i create a list of args and pass this arguments into a filter.

    args = []
    if filter_artist: args.append(Artist.name.like("%%%s%%" % filter_artist))
    if filter_song: args.append(Song.title.like("%%%s%%" % filter_song))
    if filter_word: args.append(SongDictionary.word.like("%%%s%%" % filter_word))
    if filter_wordlength: args.append(SongDictionary.word_length == filter_wordlength)

add this args to the query and it works as i expected.

db_data = session.query(Artist.name.label("artist"), Song.title.label("song_title"), SongDictionary.word, 
SongDictionary.count.label("word_count"), SongDictionary.word_type, 
SongDictionary.word_length).join(Song, Song.artist_id == 
Artist.id).join(SongDictionary, SongDictionary.song_id == Song.id).filter(*args).all()