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