Hi!
I have a table with a self-reference of two columns that represents a tree
structure.
I was trying to build an outerjoin to select all the nodes have children
but NO grandchildren, but the SQL constructed was incorrect, as it was not
aliasing properly one of the columns.
note_t Table('node_t', metadata,
Column('id', Integer, primary_key=True),
Column('project_id', Integer),
Column('parent_id', Integer),
sa.ForeignKeyConstraint(
['project_id', 'parent_id'],
['node_t.project_id', 'node_t.id]))
mapper(Node, node_t, properties= {
'children' : relationship(Node,
remote_side=[note_t.c.id, node_t.c.project_id]
)
})
*print str(session.query(Node).outerjoin(Node, Node.children,
aliased=True)))*
Generated (simplified):
SELECT node.id, node.project_id, node.parent_id
FROM node
LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id
AND node.project_id = *node*.project_id
Expected:
SELECT node.id, node.project_id, node.parent_id
FROM node
LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id
AND node.project_id = *parent*.project_id
*
*
Making the join condition explicit generates the correct SQL
*Parent = aliased(Node)*
*print str(session.query(**Node**).outerjoin(**Parent**, (Node.parent_id ==
Parent.id) & (Node.project_id == Parent.project_id)))*
*
*
I have attached a small test file (test.py) that shows this behaviour and
is based on one of your tests suites (test_relationships).
Thanks in advance!
Pau.
--
----------------------------------
Pau Tallada Crespí
Dep. d'Astrofísica i Cosmologia
Port d'Informació Científica (PIC)
Tel: +34 93 586 8233
----------------------------------
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
#!/usr/bin/env python
import sqlalchemy as sa
from sqlalchemy import Integer, String, ForeignKey, MetaData,\
and_, Table, Column, engine_from_config
from sqlalchemy.orm import mapper, relationship, relation, \
backref, create_session, configure_mappers, \
clear_mappers, sessionmaker, attributes,\
Session, composite, column_property, \
scoped_session, joinedload, aliased
metadata=MetaData()
company_t = Table('company_t', metadata,
Column('company_id', Integer, primary_key=True),
Column('name', sa.Unicode(30)))
employee_t = Table('employee_t', metadata,
Column('company_id', Integer, primary_key=True),
Column('emp_id', Integer, primary_key=True),
Column('name', sa.Unicode(30)),
Column('reports_to_id', Integer),
sa.ForeignKeyConstraint(
['company_id'],
['company_t.company_id']),
sa.ForeignKeyConstraint(
['company_id', 'reports_to_id'],
['employee_t.company_id', 'employee_t.emp_id']))
class Company(object):
pass
class Employee(object):
def __init__(self, name, company, emp_id, reports_to=None):
self.name = name
self.company = company
self.emp_id = emp_id
self.reports_to = reports_to
mapper(Company, company_t)
mapper(Employee, employee_t, properties= {
'company' : relationship(Company, backref='employees'),
'reports_to' : relationship(Employee,
_local_remote_pairs = [
(employee_t.c.reports_to_id, employee_t.c.emp_id),
(employee_t.c.company_id, employee_t.c.company_id)
],
foreign_keys=[employee_t.c.reports_to_id],
backref=backref('employees', foreign_keys=None)
)
})
c1 = Company()
c2 = Company()
e1 = Employee(u'emp1', c1, 1)
e2 = Employee(u'emp2', c1, 2, e1)
e3 = Employee(u'emp3', c1, 3, e1)
e4 = Employee(u'emp4', c1, 4, e3)
e5 = Employee(u'emp5', c2, 1)
e6 = Employee(u'emp6', c2, 2, e5)
e7 = Employee(u'emp7', c2, 3, e5)
engine = engine_from_config({
"sqlalchemy.url" : "sqlite:///memory",
"sqlalchemy.echo" : False,
})
sess = scoped_session(sessionmaker(bind=engine))()
metadata.bind=engine
metadata.create_all()
sess.add_all((c1, c2))
sess.flush()
sess.expunge_all()
print "SQL for: sess.query(Employee).outerjoin(Employee, Employee.reports_to)\n"
print str(sess.query(Employee).outerjoin(Employee, Employee.reports_to))
print "\n\nSQL for: sess.query(Employee).outerjoin(Employee, Employee.reports_to, aliased=True)\n"
print str(sess.query(Employee).outerjoin(Employee, Employee.reports_to, aliased=True))
print "\n\nSQL for: Boss = aliased(Employee); sess.query(Employee).outerjoin(Boss, (Employee.reports_to_id == Boss.emp_id) & (Employee.company_id == Boss.company_id))\n"
Boss = aliased(Employee)
print str(sess.query(Employee).outerjoin(Boss, (Employee.reports_to_id == Boss.emp_id) & (Employee.company_id == Boss.company_id)))
