Re: [sqlalchemy] sqlalchemy.select() VS Session.query() with aliases?

[Michael Bayer]

On Jun 7, 2012, at 10:41 PM, Danosaure wrote:

I'm confused at how to use orm.aliased(). I'm using declarative base.

I have a class defined from 

Base.metadata.reflect(engine)

class Summary(Base):

    __table__ = Base.metadata.tables['summary']

Now, I want to use that class in a self join

class JoinSummary(Base):

   s1 = orm.aliased(Summary, name='summary1')

   s2 = orm.aliased(Summary, name='summary2')

   __select__ = sqlalchemy.select([...], from_obj=s1.__table__.outerjoin(s2.__table__, s1.attr1 == s2.attr2)).where(...)

   __table__ = __select__.alias('my_join_summary')

But as soon as I do Session.query(Join.Summary.__select__), I get the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1066, "Not unique table/alias: 'summary'", None)

I must not been using the aliased() as it should.

Your help will be much appreciated.

Thanks.

I am not sure how to do what I want: create a view class that I can use later in Session.query().

How come you're making some kind of mapping to this new __select__, but then when you use query() you're not making this query towards the JoinSummary class ?  the example seems a little torn as to what it wants to do.

The error means that the generated SQL contains a FROM leading to the name "summary" more than once.

Basically the use case of deriving __table__ from AliasedClass, which would then be expected to be the "aliased" table, was never anticipated - aliased(orm_object) was only meant to produce a new class that itself was the "alias" object that can be used in a query.   So __table__ is still the "summary" table here.    It might not be a bad idea to change this.    (though not mid-0.7, would be another 0.8 thing).

But anyway yeah that's not really how I anticipated aliased(orm_object) being used.  If you want to work with SQL Expression level aliases I'd build that directly:

a1 = Summary.__table__.alias('summary1')

a2 = Summary.__table__.alias('summary2')

my_select = select(..)... etc

result = session.execute(my_select)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Jk83OT3u0N8J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Danosaure]

On Friday, June 8, 2012 8:56:45 AM UTC-4, Michael Bayer wrote:

How come you're making some kind of mapping to this new __select__, but then when you use query() you're not making this query towards the JoinSummary class ?  the example seems a little torn as to what it wants to do.

Sorry, it was a typo... I do actually want to use Session.query(JoinSummary).all() (hand-typed from my non-programming computer), but since the table produced the error, I tried with the __select__.

 

The error means that the generated SQL contains a FROM leading to the name "summary" more than once.

Basically the use case of deriving __table__ from AliasedClass, which would then be expected to be the "aliased" table, was never anticipated - aliased(orm_object) was only meant to produce a new class that itself was the "alias" object that can be used in a query.   So __table__ is still the "summary" table here.    It might not be a bad idea to change this.    (though not mid-0.7, would be another 0.8 thing).

If printing the Session.query() with orm.aliased(Summary, name='s1'), it seems to be ok, but using it with sqlalchemy.select(), it didn't work anymore.  No wonder why I was confused :)

 

But anyway yeah that's not really how I anticipated aliased(orm_object) being used.  If you want to work with SQL Expression level aliases I'd build that directly:

a1 = Summary.__table__.alias('summary1')

a2 = Summary.__table__.alias('summary2')

my_select = select(..)... etc

result = session.execute(my_select)

Ok, I got it. And then I would simply use a1.c.myColumn in the rest of the select().  Thanks for your help.

I would have though it would have been natural to do it that way...

Hopefully, my vision would get to improve the module...

D.