CODEWITHSUNDEEP
pythonjoinsqlalchemyclass-table-inheritance
Iain Duncan
Iain Duncan

Reputation: 1772

SQLAlchemy, filtering joined queries with table inheritance

First off, sorry if it turns out that what I'm trying to do is smoke crack here, I'm by no means a DB or SQLA expert. =)

I have two classes, Person and Organization, that use table inheritance to inherit from Contact. ( Person.id is an fkey to Contact.id ), which is working fine.

I want to search from a search box and get a list of all contacts. Normally, when I'm after a set of things, I do this:

def build_query(self):
  return self.session.query(Person)

def filter_query(self, query)
    if self.search_form_values.get('name_last',None):
        query = query.filter(
            Person.name_last==self.search_form_values.get('name_last') 
        )
    ...
    return query

And then elsewhere the query gets executed. The problem is that I want to get back a list of Contact objects, but I want to filter on Organization.name, Person.name_last, and Person.name_first, all from the values typed into the 'name' box on the search form. I can't figure out how to do this with one query, not sure if it's even possible. I'd sure like to avoid having two separate queries with interleaved results though.

Is there some filtering kung fu that would allow me to do the above? ie

Thanks

Iain

Upvotes: 0

Views: 403

Answers (2)

zzzeek
zzzeek

Reputation: 75117

per the thread at:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/b45ccc630739ae78#

If you query(Contact).with_polymorphic([Person, Organization]), you can refer to Person. and Organization. in your query. It will produce a LEFT OUTER JOIN from contact to each of the related tables.

Upvotes: 1

9000
9000

Reputation: 40884

I think it is not easily achievable in SQL at all. A single SQL query can only return a fixed number of columns, and every row must have all the same columns. Your Person and Organization are obviously different.

Of course one can always return a union of columns of two tables, setting non-existent columns in a particular record to null. I don't think SQLAlchmey objects would be easy to recreate from such data. I'm not saying it's impossible, but I'd bet $10 it's impractical.

If I were you I'd rather use two queries, merge the results, and see if performance is bad enough to ever care.

Of course, fetching and merging two queries makes paginated output a pain. If I cared so much about search responsiveness, I might consider fetching excessive records and caching them in RAM or a temporary table, linked to a particular search request (e.g. its hash). Few users that need more than 2-3 pages would have to wait for heavy queries to be run, but most users will be served fast.

Upvotes: 0

Related Questions