join where with many to many relations

[amirreza-sf80]

hi 👋
it is documented how to use where when doing a join using a one to many https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/where.html#joins
but doing thiswith m2m is not documented

the docs have some join examples https://piccolo-orm.readthedocs.io/en/latest/piccolo/schema/m2m.html#select-queries but it doesn't show how one should use a where clause

specifically, what i'm looking for (using the tables from docs) is to select Band.name where Genre.name is something specific, but not return anything from Genre itself

something like

Band.select(Band.name).where(Band.genre.name=="something")

sorry if this is already documented somewhere, i couldn't find anything

[sinisaos]

@amirreza-sf80 Yes, you're right, it's not documented anywhere. For M2M filtering you can use something like this using join_on and subquery to joining table or you can first create a filtering query and then use get_m2m to get data

genre = await Genre.objects().get(Genre.name == "Rock")
await genre.get_m2m(Genre.bands)

That's what you can use for now. Will there be any special API for that, I don't know.