Re: [sqlalchemy] finding if a table is already join in a query
What if your query already has a join yet you need to add another WHERE
clause to the join? This fails with This query already has a join for Table
xxx. Any way to modify your join to a query after you join it?
--
Thadeus
On Wed, Dec 1, 2010 at 8:08 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Dec 1, 2010, at 1:28 AM, James Neethling wrote:
if you would like multiple references to Address to all work from the
same join, your routine needs to track which entities have already been
joined as a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
You can iterate through q._from_obj(), and for each object that is a
join(), recursively descend through j.left and j.right looking for Table
objects. Table objects can be embedded in subqueries and alias objects
too but I'm assuming your query buildup here is simple enough that gray
areas like that aren't expected.
If it were me, I'd not be passing a raw Query around, I'd have it wrapped
inside a facade that is doing the abovementioned tracking of important state
explicitly (and also ensuring that those more grayish areas aren't occurring
with this particular Query). That way any other interesting facts about
the query as built so far can be tracked as well. Also easier to unit
test.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this
Re: [sqlalchemy] finding if a table is already join in a query
To the ON clause ? you'd need to find the join() and surgically replace its
on clause, which again likely has issues in more complex cases, such as if
any kind of aliasing is going on, joined table inheritance in use, etc.
I'll reiterate that this is not the way I'd be approaching this problem.
On Dec 5, 2010, at 3:32 PM, Thadeus Burgess wrote:
What if your query already has a join yet you need to add another WHERE
clause to the join? This fails with This query already has a join for Table
xxx. Any way to modify your join to a query after you join it?
--
Thadeus
On Wed, Dec 1, 2010 at 8:08 AM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Dec 1, 2010, at 1:28 AM, James Neethling wrote:
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined
as a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
You can iterate through q._from_obj(), and for each object that is a join(),
recursively descend through j.left and j.right looking for Table objects.
Table objects can be embedded in subqueries and alias objects too but I'm
assuming your query buildup here is simple enough that gray areas like that
aren't expected.
If it were me, I'd not be passing a raw Query around, I'd have it wrapped
inside a facade that is doing the abovementioned tracking of important state
explicitly (and also ensuring that those more grayish areas aren't occurring
with this particular Query). That way any other interesting facts about the
query as built so far can be tracked as well. Also easier to unit test.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
Re: [sqlalchemy] finding if a table is already join in a query
On Dec 1, 2010, at 1:28 AM, James Neethling wrote:
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as
a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
You can iterate through q._from_obj(), and for each object that is a join(),
recursively descend through j.left and j.right looking for Table objects.
Table objects can be embedded in subqueries and alias objects too but I'm
assuming your query buildup here is simple enough that gray areas like that
aren't expected.
If it were me, I'd not be passing a raw Query around, I'd have it wrapped
inside a facade that is doing the abovementioned tracking of important state
explicitly (and also ensuring that those more grayish areas aren't occurring
with this particular Query). That way any other interesting facts about the
query as built so far can be tracked as well. Also easier to unit test.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
[sqlalchemy] finding if a table is already join in a query
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] finding if a table is already join in a query
On Nov 30, 2010, at 11:13 AM, James Neethling wrote:
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as a
destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] finding if a table is already join in a query
On Tue, 2010-11-30 at 11:52 -0500, Michael Bayer wrote:
On Nov 30, 2010, at 11:13 AM, James Neethling wrote:
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as
a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
