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