Pony ORM how to query in many-many relation table

Question

Pony entity relation has been defined as below. Many-to-Many entity relation has been defined as below. Each country can be have multiple companies and each company can be in multiple countries, hence many-to-many relation has been choosen.

class Country(database.Entity):

    country_code = orm.PrimaryKey(str)
    country_name = orm.Required(str, unique=True)
    companies = orm.Set('Company', reverse='countries')

class Company(database.Entity):

    company_id = orm.PrimaryKey(str)
    company_name = orm.Required(str, unique=True)
    countries = orm.Set(Country, reverse='companies')

Assume case Company A is in Country X, Y and Company B in X, Y, Z and Company C in X

In the above case i want filter all the companies that are registered in country X, Y Result should be company A and company B

Answer 1

I got solution for my problem with support from pony author. Here is the solution is provided and it works well.

query = orm.select(s for s in Company)
query = query.filter(lambda s: orm.exists(country for country in s.countries if country.country_code in countries))