Awesome!
I like the second approach better for the exact same reasons.
Thanks so much!
Kent
On Thursday, April 13, 2017 at 1:50:40 PM UTC-4, Mike Bayer wrote:
>
>
> it has nothing to do with joined table inheritance, in your example,
> your base mapper is already mapped to "preferences_union", so if you
> provide an alternative selectable that has no relationship to that, it
> does not see any of the required columns being provided. it's just
> like if your PreferencesBase were mapped to a view in the database, it
> would have no idea about the tables represented in that view. so while
> it renders your new polymorphic union, it also renders the old one
> because it still needs to load from preferences_union.c.preferenceid,
> preferences_union.c.value, etc. which are not being substituted.
>
> there's not a facility right now that can automatically handle the
> scenario of, given:
>
>
> SELECT a, b, c FROM (
> select a, b, c FROM table1
> UNION
> select a, b, c FROM table2
> ) AS p_alias_1
>
> that we want to make a whole new expression out of table1/table2:
>
>
> SELECT a, b, c FROM (
> select a, b, c FROM table1 WHERE b=1
> UNION
> select a, b, c FROM table2 WHERE c=2
> ) AS p_alias_2
>
>
> and then "collide" the second into the first, such that we can figure
> out that when our mapping wants p_alias_1.a, it can get that now from
> p_alias_2.a, because this requires understanding the semantics of the
> query. clause adaptation usually looks for the target columns you're
> trying to adapt from in the target selectable, rather than trying to
> match on a general "seems to link to the same common columns" as that
> produces a lot of ambiguous cases.
>
> *unless*, you adapt on the string name of the outer columns rather than
> trying to link them up semantically. In this case, you as the user are
> telling the system that you've already done the work of ensuring your
> new selectable links to the first one the way you want, and you've lined
> up the outermost column names as the means of doing this.
>
> There is an "adapt_on_names" feature that does this, which is provided
> as part of aliased(), where it matches p_alias_1.a to p_alias_2.a using
> the string name "a". adapt_on_names is accepted by aliased(), but
> currently not with_polymorphic() (this can be added, send a PR). So we
> can build up w/ the AliasedClass directly:
>
> from sqlalchemy.orm.util import AliasedClass
>
> a = AliasedClass(
> PreferenceBase,
> u,
> with_polymorphic_mappers=[
> inspect(GlobalPreference),
> inspect(SitePreference),
> inspect(UserPreference)
> ],
> with_polymorphic_discriminator=u.c.type,
> adapt_on_names=True
> )
>
> so that's one way.
>
> next approach, which I think is neater, is to do sort of what I
> suggested but do it by adapting your original polymorphic, so that it
> *does* line up. In the view metaphor, this means you're using the view
> but swapping out the insides. this looks like this:
>
> def apply_polymorphic_criteria(orig, target_table, criteria):
> from sqlalchemy.sql import visitors
>
> def provide_new_select(element):
> if target_table in element.froms:
> element.append_whereclause(criteria)
>
> return visitors.cloned_traverse(
> orig,
> {},
> {"select": provide_new_select}
> )
>
> u = apply_polymorphic_criteria(
> preferences_union,
> userpreferences_table,
> userpreferences_table.c.username == 'kb'
> )
> u = apply_polymorphic_criteria(
> u,
> sitepreferences_table,
> sitepreferences_table.c.siteid == '00'
> )
> a = with_polymorphic(PreferenceBase, '*', selectable=u,
> polymorphic_on=u.c.type)
>
>
> why I like that is because you don't have to build up the whole
> polymorphic_union all over again. also the reliance on names (which
> always makes me nervous) is not needed. I like that approach a lot
> better but there's a little more rocket science involved.
>
> also, I can't rememeber the last time I gave someone a neat little
> recipe to do something unusual and it immediately fails in four other
> ways and then I just have to write them a new feature, so, there's that,
> try out both of these.
>
>
>
>
>
>
>
>
>
> On 04/13/2017 12:39 PM, Kent wrote:
> > That was the first route I tried. with_polymorphic() seems to cater to
> > or assume joined table inheritance. When I pass a selectable, it always
> > ends up *joining *my base to that selectable instead of /using only my
> > selectable/.
> >
> > My problem might be that I'm trying to take advantage of
> > polymorphic_union() to render my union with all the CAST(NULL AS ...),
> > but it does exactly what I need for building a select on the fly.
> >
> > Attached script is my failing attempt.
> >
> > Is there a recipe or example using concrete inheritance and
> > with_polymorphic?
> >
> >
> > On Thursday, April 13, 2017 at 10:43:15 AM UTC-4, Mike Bayer wrote:
> >
> >
> >
> > On 04/13/2017 10:24 AM, Kent wrote:
> > > Suppose we have the documentation's example of *Concrete Table
> > > Inheritance, *where
> > >
> > > session.query(Employee).all()
> > >
> > >
> > > produces this:
> > >
> > > SELECT pjoin.type AS pjoin_type,
> > > pjoin.manager_data AS pjoin_manager_data,
> > > pjoin.employee_id AS pjoin_employee_id,
> > > pjoin.name <http://pjoin.name> AS pjoin_name, pjoin.engineer_info
> > AS pjoin_engineer_info
> > > FROM (
> > > SELECT employees.employee_id AS employee_id,
> > > CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name
> > <http://employees.name> AS name,
> > > CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS
> > type
> > > FROM employees
> > > UNION ALL
> > > SELECT managers.employee_id AS employee_id,
> > > managers.manager_data AS manager_data, managers.name
> > <http://managers.name> AS name,
> > > CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS
> type
> > > FROM managers
> > > UNION ALL
> > > SELECT engineers.employee_id AS employee_id,
> > > CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name
> > <http://engineers.name> AS name,
> > > engineers.engineer_info AS engineer_info, 'engineer' AS type
> > > FROM engineers
> > > ) AS pjoin
> > >
> > >
> > > Suppose we want to*filter certain managers*, which we can do with:
> > >
> > > session.query(Employee)\
> > >
> > > .filter(or_(
> > >
> > > Employee.manager_data == u'whatineed',
> > >
> > > Employee.manager_data == None))\
> > >
> > > .all()
> > >
> > >
> > > If manager_data is indexed, many databases can no longer use this
> > index.
> > > What we really want is:
> > >
> > > SELECT pjoin.type AS pjoin_type,
> > > pjoin.manager_data AS pjoin_manager_data,
> > > pjoin.employee_id AS pjoin_employee_id,
> > > pjoin.name <http://pjoin.name> AS pjoin_name, pjoin.engineer_info
> > AS pjoin_engineer_info
> > > FROM (
> > > SELECT employees.employee_id AS employee_id,
> > > CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name
> > <http://employees.name> AS name,
> > > CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS
> > type
> > > FROM employees
> > > UNION ALL
> > > SELECT managers.employee_id AS employee_id,
> > > managers.manager_data AS manager_data, managers.name
> > <http://managers.name> AS name,
> > > CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS
> type
> > > FROM managers
> > >
> > > *WHERE manager_data = 'whatineed'*
> > > UNION ALL
> > > SELECT engineers.employee_id AS employee_id,
> > > CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name
> > <http://engineers.name> AS name,
> > > engineers.engineer_info AS engineer_info, 'engineer' AS type
> > > FROM engineers
> > > ) AS pjoin
> > >
> > >
> > > Is there a way to accomplish this?
> >
> >
> > Certainly, construct the complete UNION query that you want, most
> > likely
> > using Core select() and union(), and supply it to Query using
> > with_polymorphic; see the "custom selectable" example in
> >
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried
>
> > <
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried>.
>
>
> >
> >
> > Automating this process, perhaps you could compose some enhanced
> > version
> > of the polymorhic_union() feature that accepts additional criteria.
> >
> > However, I would suggest that if the "manager_data is NULL" part is
> > what
> > screws up the index, you might want to query like this instead:
> >
> > session.query(Employee)\
> > .filter(or_(Employee.manager_data == u'whatineed',
> > pjoin.c.type != 'manager')).all()
> >
> >
> > >
> > > Thanks in advance,
> > > Kent
> > >
> > > --
> > > SQLAlchemy -
> > > The Python SQL Toolkit and Object Relational Mapper
> > >
> > > http://www.sqlalchemy.org/
> > >
> > > To post example code, please provide an MCVE: Minimal, Complete,
> and
> > > Verifiable Example. See http://stackoverflow.com/help/mcve
> > <http://stackoverflow.com/help/mcve> for a full
> > > description.
> > > ---
> > > You received this message because you are subscribed to the Google
> > > Groups "sqlalchemy" group.
> > > To unsubscribe from this group and stop receiving emails from it,
> > send
> > > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>
> <javascript:>>.
> > > To post to this group, send email to sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at https://groups.google.com/group/sqlalchemy
> > <https://groups.google.com/group/sqlalchemy>.
> > > For more options, visit https://groups.google.com/d/optout
> > <https://groups.google.com/d/optout>.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
