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,
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.
