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