pythonsqlalchemypython-elixir

Sqlalchemy+elixir: How query with a ManyToMany relationship?

I'm using sqlalchemy with Elixir and have some troubles trying to make a query..

I have 2 entities, Customer and CustomerList, with a many to many relationship.

customer_lists_customers_table = Table('customer_lists_customers', 
                      metadata,
                      Column('id', Integer, primary_key=True),
                      Column('customer_list_id', Integer, ForeignKey("customer_lists.id")),
                      Column('customer_id', Integer, ForeignKey("customers.id")))

class Customer(Entity):
  [...]
  customer_lists = ManyToMany('CustomerList', table=customer_lists_customers_table)

class CustomerList(Entity):
  [...]

  customers = ManyToMany('Customer', table=customer_lists_customers_table)

I'm tryng to find CustomerList with some customer:

customer = [...]
CustomerList.query.filter_by(customers.contains(customer)).all()

But I get the error: NameError:

global name 'customers' is not defined

customers seems to be unrelated to the entity fields, there's an special query form to work with relationships (or ManyToMany relationships)?

Thanks

Solution

  • You can use regular filter: query.filter(CustomerList.customers.contains(customer)). See SQLAlchemy documentation for more examples. It's actually filter_by that's a special case. The query.filter_by(**kwargs) shorthand works only for simple equality comparisons. Under the cover query.filter_by(foo="bar", baz=42) is delegated to the equivalent of query.filter(and_(MyClass.foo == "bar", MyClass.baz == 42)). (There's actually slightly more magic to figure out which property you meant you have many entities, but it still uses simple delegation)