Aliased table in subsequent join and select statement does not work?

[Rogier Eggers]

I am wondering whether I use the incorrect functions in sqlalchemy or if I'm possibly just going the complete wrong way about this.

I have two Tables, "Tests" and "Signals", which are related through a many-to-many relationship (technically it could be one-to-many, but that decision has been made before me).

I would like to make a select statement in sqlalchemy that takes fields from two records in the Signal table and present them as separate columns in the output. I was thinking to achieve this with aliases as in the code below. But this fails already when trying to set an alias:

"Signals_1   = alias(Signals,'Signals_1')"

This delivers the follow (truncated) stracktrace:

"File "D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py", line 832, in alias
    return Alias(selectable, name=name)
  File "D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py", line 4054, in __init__
    self.supports_execution = baseselectable.supports_execution
AttributeError: type object 'Signals' has no attribute 'supports_execution'"

Why can I not set an alias?

Also, I realise that this design is probably not the best. In my case the Signals table is very large. By first doing two outerjoins, I am effectively working with a very large number of records. The whereclause could also be applied to the two aliased Signals tables earlier. How could I do that?

Note that I use this select statement as part of a view in Camelot: http://python-camelot.s3.amazonaws.com/gpl/default/pyqt/doc/doc/views.html#definition-of-the-view
This part however only uses sqlalchemy code.

More complete code is as follows:
...

    from model import Tests, Signals

    from sqlalchemy.orm import outerjoin
    from sqlalchemy.sql import select, and_
    from sqlalchemy.sql.expression import alias

    Signals_1   = alias(Signals,'Signals_1')
    Signals_2   = alias(Signals,'Signals_2')

    from_obj = outerjoin(Signals_1,Tests,Signals_1.Tests)
    from_obj = from_obj.outerjoin(Signals_2,Tests.Signals)   

    s = select([
                Signals_1.id,
                Tests.id.label('Tests.id'),
                Tests.TestNumber.label('TestNo'),
                Signals_1.id.label('Signals_1.id'),
                Signals_1.value.label('Signals_1_value),
                Signals_2.id.label('Signals_2.id'),
                Signals_2.value.label('Signals_2_value)
                ],
                from_obj = from_obj,
                whereclause = and_(
                                Signals_1.SignalName.like('sig1'),
                                Signals_2.SignalName.like('sig2')
                                )
                )

...

[Mike Bayer]

On 08/18/2016 11:41 AM, Rogier Eggers wrote:
> I am wondering whether I use the incorrect functions in sqlalchemy or if
> I'm possibly just going the complete wrong way about this.
>
> I have two Tables, "Tests" and "Signals", which are related through a
> many-to-many relationship (technically it could be one-to-many, but that
> decision has been made before me).
>
> I would like to make a select statement in sqlalchemy that takes fields
> from two records in the Signal table and present them as separate
> columns in the output. I was thinking to achieve this with aliases as in
> the code below. But this fails already when trying to set an alias:
>
> "Signals_1 = alias(Signals,'Signals_1')"
>
> This delivers the follow (truncated) stracktrace:
>
> "File
> "D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py",
> line 832, in alias
> return Alias(selectable, name=name)
> File
> "D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py",
> line 4054, in __init__
> self.supports_execution = baseselectable.supports_execution
> AttributeError: type object 'Signals' has no attribute 'supports_execution'"
>
> Why can I not set an alias?

Signals appears to be a mapped class, not a Table, so normally you would
use aliased():

http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased

alias() is a Core construct that expects something like a Table object
or Select object, and you'd get back an Alias object that has a .c.
attribute and doesn't act like a class at all, it acts like a core
selectable.

However, in version 1.1 of SQLAlchemy (due for release any day), the
alias() function *will* work with your class, but again you get back a
Core selectable, and not an object that acts like your class. Your
example seems to want to use Core ultimately which is fine, you'd just
need to keep straight between which objects are ORM entities (no .c.
namespace, all your methods and attributes remain available) and which
are Core selectables (.c. namespace, only columns).

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Rogier Eggers]

I was confused as some of the SQLA functions I used here are from Core (e.g. in the whereclause) and was therefore focussing on Core.

But indeed aliased work much better (and make sense because of the mapped classes).... Thanks!

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/G9Fmd440PWg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.