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.
Paul
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
