I'm trying to define a column_property on a mixin class for a joined-table
polymorphic class but I'm having issues with the from/joins.
These are my classes:
class MomBase(Base):
__tablename__ = 'mom_objects'
guid = Column(Text, primary_key=True)
index = Column(Integer, index=True)
name = Column(Text, index=True)
discriminator = Column('type', Text)
__mapper_args__ = {'polymorphic_on': discriminator}
class MomBaseMixin(object):
@declared_attr
def oid(cls):
return column_property(select([func.json_build_array(cls.guid, cls.
name, cls.index)]).where(
cls.guid==MomBase.guid).correlate_except(None))
class Tag(MomBase, MomBaseMixin):
__tablename__ = 'tags'
__mapper_args__ = {'polymorphic_identity': __tablename__[:-1]}
guid = Column(Text, ForeignKey('mom_objects.guid', onupdate='cascade'),
primary_key=True)
color = Column(Text)
If i query for the column_property alone I get this error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) more than one row
returned by a subquery used as an expression
'SELECT (SELECT json_build_array(tags.guid, mom_objects.name,
mom_objects.index) AS json_build_array_1 \nFROM tags, mom_objects \nWHERE
tags.guid = mom_objects.guid) AS anon_1' {}
The problem is, that the from clause needs to be on the outer query.
This very similar sql query works as intended (I just moved up the ") as
anon_1"):
xmsdb=# SELECT (SELECT json_build_array(tags.guid, mom_objects.name,
mom_objects.index) AS json_build_array_1) as anon_1
xmsdb-# FROM tags, mom_objects WHERE tags.guid = mom_objects.guid;
anon_1
-------------------------------------------------
["c8562ab44cf0469694137b224e4854ff", "tag1", 1]
["d0a29db0ed7f41ccbfb4a1698ff187da", "tag2", 2]
(2 rows)
If i query for an additional column, then it produces the correct sql and
works, presumably because the inner from clause gets correlated out:
2015-11-09 13:05:34,544 INFO sqlalchemy.engine.base.Engine.xmsdb SELECT (SELECT
json_build_array(tags.guid, mom_objects.name, mom_objects.index) AS
json_build_array_1
WHERE tags.guid = mom_objects.guid) AS anon_1, tags.color AS tags_color
FROM mom_objects JOIN tags ON mom_objects.guid = tags.guid
2015-11-09 13:05:34,544 INFO sqlalchemy.engine.base.Engine.xmsdb {}
2015-11-09 13:05:34,545 DEBUG sqlalchemy.engine.base.Engine.xmsdb Col (
'anon_1', 'tags_color')
2015-11-09 13:05:34,545 DEBUG sqlalchemy.engine.base.Engine.xmsdb Row ([u
'c8562ab44cf0469694137b224e4854ff', u'tag1', 1], None)
2015-11-09 13:05:34,545 DEBUG sqlalchemy.engine.base.Engine.xmsdb Row ([u
'd0a29db0ed7f41ccbfb4a1698ff187da', u'tag2', 2], None)
[([u'c8562ab44cf0469694137b224e4854ff', u'tag1', 1], None), ([u
'd0a29db0ed7f41ccbfb4a1698ff187da', u'tag2', 2], None)]
The question is, how can i ensure that join on the outer clause is always
included?
Thanks
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
