the issue is that the built-in relationship joining facilities dont
know how to generate joins for self-referential relationships that
you could then reference externally in your criterion. while its
easy enough for the join to add in an "alias" there, all subsequent
operations on the query would need some way of identifying a
criterion as applied to the original table or the aliased table. so
its an API issue. I just added an error message that will be raised
as of r2456.
so the explicit way to do it is this:
nodealias = nodeTable.alias('nodealias')
query.select_from(nodeTable.join(nodealias,
onclause=nodealias.c.node_id==nodeTable.c.console_id)).select
(nodealias.c.name=='console1.xxxx.com')
we can perhaps add some helpers to "join()" such as :
query.join('consoleNodes', using=nodealias)
so that at least the onclause construction wouldnt be needed.
but we'd have to define things like this mean:
query.join(['a', 'b, 'c', 'd'], using=[alias1, alias2, alias3])
On Mar 28, 2007, at 12:42 PM, Karthik Krishnamurthy wrote:
> #!/usr/bin/env python
>
> from sqlalchemy import *
>
> metadata = BoundMetaData('sqlite:///', name='opsdb')
> metadata.engine.echo = True
> session = create_session()
>
> class Node(object):
> def __repr__(self):
> return "<%s %s>" % (self.__class__.__name__, self.name)
>
> nodeTable = Table(
> 'node', metadata,
> Column('node_id', Integer, primary_key=True, unique=True),
> Column('name', String(255), unique=True),
> Column('console_id', Integer, ForeignKey('node.node_id')),
> Column('switch_id', Integer, ForeignKey('node.node_id')),
> )
>
> mapper (Node, nodeTable,
> properties = {
> 'id': nodeTable.c.node_id,
> 'console': relation(
> Node,
> primaryjoin = nodeTable.c.console_id ==
> nodeTable.c.node_id,
> remote_side = [nodeTable.c.node_id],
> backref = 'consoleNodes',
> ),
> 'switch': relation(
> Node,
> primaryjoin = nodeTable.c.switch_id ==
> nodeTable.c.node_id,
> remote_side = [nodeTable.c.node_id],
> backref = 'switchNodes',
> ),
> },
> )
>
> rows = (
> { 'node_id': 1, 'name': 'console1.xxxx.com', 'console_id':
> None, 'switch_id': 2 },
> { 'node_id': 2, 'name': 'switch1.xxxx.com', 'console_id': 1,
> 'switch_id': None },
> { 'node_id': 3, 'name': 'node1.xxxx.com', 'console_id': 1,
> 'switch_id': 2 },
> { 'node_id': 4, 'name': 'node2.xxxx.com', 'console_id': 1,
> 'switch_id': 2 },
> { 'node_id': 5, 'name': 'node3.xxxx.com', 'console_id': 1,
> 'switch_id': 2 },
> { 'node_id': 6, 'name': 'node4.xxxx.com', 'console_id': 1,
> 'switch_id': 2 },
> { 'node_id': 7, 'name': 'node5.xxxx.com', 'console_id': 1,
> 'switch_id': 2 },
> )
>
> nodeTable.create()
> i = nodeTable.insert()
> for row in rows:
> print row
> i.execute(**row)
>
> # now query
> query = session.query(Node)
> console = query.select_by(name='console1.xxxx.com')[0]
> print console.consoleNodes
>
> # Isn't this a valid use of select_by with the relation mapper above ?
> print query.select_by(console=console)
>
> # trying to get list of rows/Node instances where console_id =
> console.node_id
> # given console node name, preferably in one step.
> # I need to chain such conditions depending on the argument.
> query.join('consoleNodes').select_by(name='console1.xxxx.com')
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
