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_cust

Solution 1:

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)

Solution 2:

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:

classMyClass(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` isglobal scope

Solution 3:

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