query joining and filtering with table inheritance

[Iain Duncan]

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

- get back all Contacts that:

  - have name_last or name_first match, if the Contact is a Person

  - have name match, if the contact is an Organization

Thanks

Iain

[lena...@volny.cz]

Hello.

I am not at my development environment now so I cannot test it, but I think you want this:

http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html?highlight=of_type#basic-control-of-which-tables-are-queried

Ladislav Lenart

PS: I'm by no means a DB or SQLA expert too :-)

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Michael Bayer]

This is the right answer. if you query(Contact).with_polymorphic([Person, Organization]), you can refer to Person.<attribute> and Organization.<attribute> in your query. It will produce a LEFT OUTER JOIN from contact to each of the related tables.

[Iain Duncan]

On Fri, Sep 28, 2012 at 7:06 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

This is the right answer.   if you query(Contact).with_polymorphic([Person, Organization]), you can refer to Person.<attribute> and Organization.<attribute> in your query.   It will produce a LEFT OUTER JOIN from contact to each of the related tables.

You guys rock, thanks!

Iain