Where clause on m2m field

[serg-yalosovetsky]

Is it possible to create a where clause for searching within m2m fields?

example

class User(Table):
    id = Integer(primary_key=True)
    invited = M2M(LazyTableReference("InvitedUser", module_path=__name__))
    attended = M2M(LazyTableReference("AttendedUser", module_path=__name__))
    liked_songs = M2M(LazyTableReference("LikedSongs", module_path=__name__))
    liked_playlists = M2M(LazyTableReference("LikedPlaylists", module_path=__name__))
    liked_rooms = M2M(LazyTableReference("LikedRooms", module_path=__name__))
    liked_genres = M2M(LazyTableReference("LikedGenres", module_path=__name__))


class Song(Table):
    class Players(str, Enum):
        audio = 'a'
        apple = 'p'
        spotify = 's'
        youtube = 'y'
        soundcloud = 's'
        input = 'i'
    id = Serial(primary_key=True)
    track_id = Varchar(length=150, index=True, required=True)
    name = Varchar(max_length=150, index=True, required=True)
    created = Timestamptz(default=TimestamptzNow())
    artist = Varchar(max_length=150, null=True)
    album = Varchar(max_length=150, null=True)
    metadata = JSONB(default={})
    genres = M2M(LazyTableReference("Song2Genre", module_path=__name__))
    logo = Varchar(max_length=150, null=True)
    duration = Float(default=0.0, help_text='seconds')
    player = Varchar(max_length=1, choices=Players, required=True)
    uploaded_by = ForeignKey(references=User, on_delete=OnDelete.cascade)
    playlists = M2M(LazyTableReference("Song2Play", module_path=__name__))
    liked = M2M(LazyTableReference("LikedSongs", module_path=__name__))
    same_songs = M2M(LazyTableReference("SameSongs", module_path=__name__))

I want to get all songs genres, that user liked
Sorta like

user = await User.objects().get(User.id == user_id)
users_genres = await User.select(User.genres(Genre.id)).where(User.id == user_id)
user_genres = users_genres[0]

songs_of_genres = await Song.select(Song.id).where(Song.genres.is_in(user_genres))
# or
songs_of_genres = await Song.objects().where(Song.genres.is_in(user_genres))

now this just return AttributeError: 'M2M' object has no attribute 'is_in'

Is it the only way now - download of songs and then manually check genres for each song?

[sinisaos]

Is it possible to create a where clause for searching within m2m fields?

As far as I know, this is not possible. If I understood your request correctly, you need to make two queries because is_in needs a list (as written in the docs). I think something like this will give the correct results (sorry if I didn't understand your request)

# songs that the user liked
users_songs = (
    await User.select(User.liked_songs(Song.id, as_list=True))
    .where(User.id == 1)
    .first()
)
# songs genres, that user liked
genres_per_user_songs = await Song.select(
    Song.genres(Genre.name, as_list=True)
).where(Song.id.is_in(users_songs["liked_songs"]))

print([i["genres"] for i in genres_per_user_songs])

[serg-yalosovetsky]

It is worked with liked songs, because there not so much licked songs user can have
But there is problem with genres - if i search songs with genre that user like?
There are not so many genres, and each genre has very many songs - 1 million of metal, 10 million of rock, etc.
So if user like metal and rock, i need to get list of 11 million songs, and then fetch this song to app?

I definitly can write raw sql to solve this, but this feel unclean.

[sinisaos]

I definitly can write raw sql to solve this, but this feel unclean.

If that's not a problem for you, can you share your raw sql? Piccolo has SelectRaw and WhereRaw to mix raw sql with select query type and where clause. Maybe you can get cleaner queries that way.

[serg-yalosovetsky]

to find all songs, that have same genres, that user with user_id == 1 liked, i can use sql like this:

SELECT s.id FROM public.song s
   WHERE EXISTS (
        SELECT g1.id, g1.name, u.id, g1.id 
        FROM public.user u, public.genre g1, public.liked_genres lg, public.genre g2, public.song2_genre sg
        
        WHERE u.id = lg.user 
            AND g1.id = lg.genre
            AND u.id = 1
            AND s.id = sg.song 
            AND g2.id = sg.genre
            AND  g2.id = g1.id
   )

await Song.raw(sql)
Out[8]: [{'id': 4}, {'id': 3}]

and schema is:

class User(Table):
    id = Integer(primary_key=True)
    invited = M2M(LazyTableReference("InvitedUser", module_path=__name__))
    attended = M2M(LazyTableReference("AttendedUser", module_path=__name__))
    liked_songs = M2M(LazyTableReference("LikedSongs", module_path=__name__))
    liked_playlists = M2M(LazyTableReference("LikedPlaylists", module_path=__name__))
    liked_rooms = M2M(LazyTableReference("LikedRooms", module_path=__name__))
    liked_genres = M2M(LazyTableReference("LikedGenres", module_path=__name__))

class Genre(Table):
    id = Serial(primary_key=True)
    name = Varchar(length=150, index=True, required=True)
    parents = Array(base_column=Integer(), required=True, default=[], index=True)
    songs = M2M(LazyTableReference("Song2Genre", module_path=__name__))
    color = Varchar(max_length=255, default='', blank=True)
    description = Text(null=True, blank=True)
    liked = M2M(LazyTableReference("LikedGenres", module_path=__name__))

class Song(Table):
    class Players(str, Enum):
        audio = 'a'
        apple = 'p'
        spotify = 's'
        youtube = 'y'
        soundcloud = 's'
        input = 'i'
    id = Serial(primary_key=True)
    track_id = Varchar(length=150, index=True, required=True)
    name = Varchar(max_length=150, index=True, required=True)
    created = Timestamptz(default=TimestamptzNow())
    artist = Varchar(max_length=150, null=True)
    album = Varchar(max_length=150, null=True)
    metadata = JSONB(default={})
    genres = M2M(LazyTableReference("Song2Genre", module_path=__name__))
    logo = Varchar(max_length=150, null=True)
    duration = Float(default=0.0, help_text='seconds')
    player = Varchar(max_length=1, choices=Players, required=True)
    uploaded_by = ForeignKey(references=User, on_delete=OnDelete.cascade)
    playlists = M2M(LazyTableReference("Song2Play", module_path=__name__))
    liked = M2M(LazyTableReference("LikedSongs", module_path=__name__))
    same_songs = M2M(LazyTableReference("SameSongs", module_path=__name__))

class Song2Genre(Table):
    song = ForeignKey(Song)
    genre = ForeignKey(Genre)

class LikedSongs(Table):
    user = ForeignKey(User)
    song = ForeignKey(Song)
    added = Timestamptz()

class LikedGenres(Table):
    user = ForeignKey(User)
    genre = ForeignKey(Genre)

[sinisaos]

On my example data user liked genres is Rock and Metal. Rock songs ids is 1,3,4,5,8,9 and Metal songs ids is 11,12. Result of your raw sql query for my example data is

[{'id': 1}, {'id': 3}, {'id': 4}, {'id': 5}, {'id': 8}, {'id': 9}, {'id': 11}, {'id': 12}]

The same can be achieved with these two queries.

from piccolo.utils.list import flatten

# user liked genres
liked_genres = await User.select(User.liked_genres(Genre.id, as_list=True)).where(
    User.id == 1
)
# flatten the result list for use in the next query
user_liked_genres = flatten([i["liked_genres"] for i in liked_genres])
# all songs for the genres that the user likes
all_genre_songs_user_like = await Genre.select(
    Genre.name, Genre.song(Song.id)
).where(Genre.id.is_in(user_liked_genres))

The result is this, which I think is correct

[{'name': 'Rock', 'song': [{'id': 1}, {'id': 3}, {'id': 4}, {'id': 5}, {'id': 8}, {'id': 9}]}, {'name': 'Metal', 'song': [{'id': 11}, {'id': 12}]}]

If you want exact result like your raw sql query you can do this

flatten_result = []
for item in all_genre_songs_user_like:
    flatten_result.extend(item["song"])
print(flatten_result)

which returned the same result as your raw sql query

[{'id': 1}, {'id': 3}, {'id': 4}, {'id': 5}, {'id': 8}, {'id': 9}, {'id': 11}, {'id': 12}]

If you have millions of results, you need to provide some kind of pagination. I hope this meets your requirements.