On 11/09/2015 01:17 PM, Uri Okrent wrote:
> 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:
>
> |
> classMomBase(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}
>
>
> classMomBaseMixin(object):
> @declared_attr
> defoid(cls):
>
> returncolumn_property(select([func.json_build_array(cls.guid,cls.name,cls.index)]).where(
> cls.guid==MomBase.guid).correlate_except(None))
>
>
> classTag(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 bya subquery used asan 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.
my immediate diagnosis is that this has to do with selecting from a
magic PG function that doesn't need a FROM clause, you need to use the
recipe at
https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs#comment-22842678
for now.
>
> 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]
> (2rows)
> |
>
>
> 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-0913:05:34,544INFO 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-0913:05:34,544INFO sqlalchemy.engine.base.Engine.xmsdb {}
> 2015-11-0913:05:34,545DEBUG sqlalchemy.engine.base.Engine.xmsdb
> Col('anon_1','tags_color')
> 2015-11-0913:05:34,545DEBUG sqlalchemy.engine.base.Engine.xmsdb
> Row([u'c8562ab44cf0469694137b224e4854ff',u'tag1',1],None)
> 2015-11-0913:05:34,545DEBUG 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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.
