Sqlalchemy+elixir: How query with a ManyToMany relationship?

Question

Hi,

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

Answer 1

Read the error message with attention, it points to the source of problem. Did you mean CustomerList.query.filter_by(CustomerList.customers.contains(customer)).all()?

Update: When using declarative definition you can use just defined relation in class scope, but these properties are not visible outside class:

class MyClass(object):
    prop1 = 'somevalue'
    prop2 = prop1.upper() # prop1 is visible here

val2 = MyClass.prop1 # This is OK    

val1 = prop1.lower() # And this will raise NameError, since there is no 
                     # variable `prop1` is global scope

Answer 2

CustomerList.query.filter_by(CustomerList.customers.contains(customer)).all() should work fine.

Answer 3

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)