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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///', echo=True)
metadata = MetaData(engine)
session = sessionmaker(bind=engine)()
globalpreferences_table = Table("globalpreferences", metadata,
Column("preferenceid", Unicode(255), primary_key=True),
Column("value", Unicode(255))
)
sitepreferences_table = Table("sitepreferences", metadata,
Column("preferenceid", Unicode(255), primary_key=True),
Column("siteid", Unicode(255), primary_key=True),
Column("value", Unicode(255))
)
userpreferences_table = Table("userpreferences", metadata,
Column("preferenceid", Unicode(255), primary_key=True),
Column("username", Unicode(255), primary_key=True),
Column("value", Unicode(255))
)
def repr_attrs(obj, *attrs):
"""
build a safe __repr__ string with obj.__dict__.get() so we don't
accidentally set sqla attributes by referencing them
"""
return '<%s: ' % obj.__class__.__name__ + \
' '.join('{0[%s]}=[{1[%s]}]' % (i,i) for i in range(len(attrs)))\
.format(attrs, map(obj.__dict__.get, attrs)) + ">"
class PreferenceBase(object):
@classmethod
def query(cls):
"""
return a query
"""
return session.query(cls)
def __init__(self, prefid, val, **attrs):
self.preferenceid = prefid
self.value = val
self.__dict__.update(attrs)
class GlobalPreference(PreferenceBase):
def __repr__(self):
return repr_attrs(self,'preferenceid','value')
class SitePreference(PreferenceBase):
def __repr__(self):
return repr_attrs(self,'preferenceid','siteid','value')
class UserPreference(PreferenceBase):
def __repr__(self):
return repr_attrs(self,'preferenceid','username','value')
preferences_union = polymorphic_union({
'user': userpreferences_table,
'site': sitepreferences_table,
'global': globalpreferences_table
},
'type',
'preferences_union')
preferencebase_mapper = mapper(PreferenceBase, preferences_union,
polymorphic_on=preferences_union.c.type
)
mapper(GlobalPreference, globalpreferences_table,
inherits=preferencebase_mapper,
concrete=True,
polymorphic_identity='global'
)
mapper(SitePreference, sitepreferences_table,
inherits=preferencebase_mapper,
concrete=True,
polymorphic_identity='site',
)
mapper(UserPreference, userpreferences_table,
inherits=preferencebase_mapper,
concrete=True,
polymorphic_identity='user',
)
metadata.create_all(engine)
map(session.add, [
GlobalPreference('prefA', 'gvalA'),
GlobalPreference('prefB', 'gvalB'),
GlobalPreference('prefC', 'gvalC'),
SitePreference('prefB', 'svalB', siteid='00'),
UserPreference('prefC', 'svalC', username='kb'),
UserPreference('prefA', 'uvalA', username='kb'),
UserPreference('prefB', 'uvalB', username='kb'),
UserPreference('prefC', 'uvalC', username='jb'),
])
session.flush()
session.expunge_all()
u = polymorphic_union({
'user': userpreferences_table.select().where(userpreferences_table.c.username=='kb'),
'site': sitepreferences_table.select().where(sitepreferences_table.c.siteid=='00'),
'global': globalpreferences_table
},
'type')
a = with_polymorphic(PreferenceBase, '*', selectable=u, polymorphic_on=u.c.type)
print session.query(a)
