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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
