SQLAlchemy/Elixir - querying to check entity's membership in a many-to-many relationship list

Question

I am trying to construct a sqlalchemy query to get the list of names of all professors who are assistants professors on MIT. Note that there can be multiple assistant professors associated with a certain course.

What I'm trying to do is roughly equivalent to:

uni_mit = University.get_by(name='MIT')
s = select([Professor.name],
           and_(Professor.in_(Course.assistants),
                Course.university = uni_mit))
session.execute(s)

This won't work, because in_ is only defined for entity's fields, not for the whole entity.. Can't use Professor.id.in_ as Course.assistants is a list of Professors, not a list of their ids. I also tried contains but I didn't work either.

My Elixir model is:

class Course(Entity):
    id = Field(Integer, primary_key=True)
    assistants = ManyToMany('Professor', inverse='courses_assisted', ondelete='cascade')
    university = ManyToOne('University')
    ..

class Professor(Entity):
    id = Field(Integer, primary_key=True)
    name = Field(String(50), required=True)
    courses_assisted = ManyToMany('Course', inverse='assistants', ondelete='cascade')
    ..

This would be trivial if I could access the intermediate many-to-many entity (the condition would be and_(interm_table.prof_id = Professor.id, interm_table.course = Course.id), but SQLAlchemy apparently hides this table from me.

I'm using Elixir 0.7 and SQLAlchemy 0.6.

Btw: This question is different from Sqlalchemy+elixir: How query with a ManyToMany relationship? in that I need to check the professors against all courses which satisfy a condition, not a single, static one.

It's not sqlalchemy that hides the secondary table; in vanilla sqlalchemy you are *required* to name and describe that table, but are advised not to map it to a class. The obfuscation occurs due to Elixir. — SingleNegationElimination, Jun 11 '11 at 15:43

Vinay Sajip · Accepted Answer · 2011-06-13T07:24:24.233

You can find the intermediate table where Elixir has hidden it away, but note that it uses fully qualified column names (such as __package_path_with_underscores__course_id). To avoid this, define your ManyToMany using e.g.

class Course(Entity):
    ...
    assistants = ManyToMany('Professor', inverse='courses_assisted',
                            local_colname='course_id', remote_colname='prof_id',
                            ondelete='cascade')

and then you can access the intermediate table using

rel = Course._descriptor.find_relationship('assistants')
assert rel
table = rel.table

and can access the columns using table.c.prof_id, etc.

Update: Of course you can do this at a higher level, but not in a single query, because SQLAlchemy doesn't yet support in_ for relationships. For example, with two queries:

>>> mit_courses = set(Course.query.join(
... University).filter(University.name == 'MIT'))
>>> [p.name for p in Professor.query if set(
... p.courses_assisted).intersection(mit_courses)]

Or, alternatively:

>>> plist = [c.assistants for c in Course.query.join(
... University).filter(University.name == 'MIT')]
>>> [p.name for p in set(itertools.chain(*plist))]

The first step creates a list of lists of assistants. The second step flattens the list of lists and removes duplicates through making a set.

Thank you! With that the solution is going to be straightforward but somewhat long and verbose. Is there a way of approaching this problem without accessing the intermediate table (e.g. some kind of `contains` / `in_` syntax I'm not aware of)? After all, ORMs are intended to abstract from the way the data is stored.. — Marcin, Jun 11 '11 at 18:09

SQLAlchemy/Elixir - querying to check entity's membership in a many-to-many relationship list

1 Answers1