On Thu, Mar 3, 2016 at 12:01 PM, Krishnakant <krm...@openmailbox.org> wrote:
>
>
> On Thursday 25 February 2016 03:50 PM, Simon King wrote:
>
> On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant <krm...@openmailbox.org>
> wrote:
>
>> Hello,
>> I have a query where there are 2 alias for a single table.
>> This is because the table contains a self referencing foreign key.
>> the table is (groupcode integer primary key, groupname text, subgroupof
>> integer foreign key references groupcode).
>> Now let's say I wish to have a 2 column query with groups and their
>> respective subgroups, I need to join the table to itself making 2 aliases.
>> I know the raw query but need to do it through sqlalchemy core.
>> I don't use ORM for my project.and need this in the expression language.
>>
>>
> Something like this perhaps:
>
> import sqlalchemy as sa
> md = sa.MetaData()
> t = sa.Table(
> 't', md,
> sa.Column('groupcode', sa.Integer, primary_key=True),
> sa.Column('groupname', sa.Text()),
> sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
> )
>
> subgroup = t.alias('subgroup')
> j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
> print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)
>
>
> Output:
>
> SELECT t.groupcode, subgroup.groupcode
> FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode
>
>
> Hope that helps,
>
> Thanks a lot for the help.
> I have one query.
> do I not need to import alias? some thing like,
> from sqlalchemy import alias
> I tryed this and I get import error for the above mentioned line.
> and the query you provided did not work without alias.
>
In the example above, I was using the alias *method* of the Table object,
so it was not necessary to import anything. There is also a standalone
function, sqlalchemy.alias, which does the same thing. Given a table "t",
these are equivalent:
subgroup = t.alias('subgroup')
subgroup = sqlalchemy.alias(t, 'subgroup')
I don't know what your import error is about. "from sqlalchemy import
alias" works for me in SA 1.0.12.
Simon
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
