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:>>.
> 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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
