Re: [SQLObject] Search for Full Name

[Oleg B. <ph...@ph...>]

On Wed, Apr 14, 2010 at 01:09:41PM -0500, ji...@ql... wrote:
> I've got a model with a Contact object.  It has separate attributes for firstName and lastName.  In various parts of my app I'd like to be able to provide a search function where a user could type in part of a person's name that included the first name and part of the last and then display a list of those matching.
> 
> I know I can search on just the firstName using:
> 
> contacts = Contact.select(Contact.q.firstName.contains(searchText.encode('utf8')))
> 
> But, if someone types 'Jim Steil' in the search box, this filter would exclude the record for me because of the last name being there.  I'm wondering if there is some magic in SQLObject that will allow me to apply this search string against the combination of the firstName and lastName attributes of the Contact object.

   SQLObject doesn't abstract away string concatenation, and that's bad
because most databases except MySQL use '||' operator, not a function.

   You want something like

from sqlobject.sqlbuilder import SQLOp, CONTAINSSTRING
Contact.select(CONTAINSSTRING(SQLOp('||', SQLOp('||', Contact.q.name, ' '), Contact.q.surname), 'Test1 One'))

   SQL query is

SELECT id, name, surname FROM contact WHERE ((((name) || (' ')) || (surname)) LIKE ('%Test1 One%'))

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.