On May 31, 2013, at 5:06 AM, Paul Johnston <paul....@gmail.com> wrote:
> Hi,
>
> Often you want to offer the user a text box which will search through
> multiple fields. If the user is looking at the list of orders, they want a
> search box that will search: order id, customer name, product names, etc. I'm
> trying to put together a recipe for this, although it's becoming more
> complicated than I planned.
>
> The recipe will take three inputs: mapped class, list of fields, search term.
> The list of fields will be like ['id', 'customer.name', 'products.name'] -
> where there is a dot in the field name, that indicates the search should walk
> a relation. For starters the matching will be an ilike with % characters put
> around the search term.
>
> This is what I came up with so far:
>
> def text_search(cls, fields, search):
> queries = []
> for field in fields:
> query = cls.query.order_by(None)
> parts = field.split('.')
> cur_cls = cls
> for part in parts[:-1]:
> attr = getattr(cur_cls, part)
> cur_cls = attr.property.mapper.class_
> query = query.outerjoin(attr)
> queries.append(query.filter(getattr(cur_cls,
> parts[-1]).ilike('%'+search+'%')))
> return queries[0].union(*queries[1:])
>
> The problem is I'm getting PostgreSQL syntax errors, because some order_by
> clauses are still appearing in the queries, which don't play nice with the
> union. Any suggestions for fixing this would be welcome!
>
> Once that's fixed, and with a few more refinements, I think this would be a
> very handy recipe to keep around, or even put in the SQLAlchemy core.
what's the purpose of cls.query.order_by(None) ? you're not using
mapper.order_by i hope ?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
