Re: [sqlalchemy] Filters on specific tables of concrete polymorphic union

[Kent]

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 

Re: [sqlalchemy] Filters on specific tables of concrete polymorphic union

[mike bayer]

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  AS 

Re: [sqlalchemy] Filters on specific tables of concrete polymorphic union

[Kent]

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 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 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 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 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 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 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 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 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.
>  
>
>
> 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 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  
> > . 
> > To post to this group, send email to sqlal...@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 

Re: [sqlalchemy] Filters on specific tables of concrete polymorphic union

[mike bayer]

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 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 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 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 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 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 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 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 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.


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 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.


--
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.

[sqlalchemy] Filters on specific tables of concrete polymorphic union

[Kent]

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 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 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 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 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 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 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 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 AS name,
engineers.engineer_info AS engineer_info, 'engineer' AS type
FROM engineers
) AS pjoin


Is there a way to accomplish this?

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 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.