[SQLObject] Re: Form fields and SQLObject

[Chris G. <ch...@il...>]

mar...@ge... wrote:
> So far I have a findPerson method that looks like this:
> 
> def findPerson(self, id, fn, mi, ln):
> 	persons = Person.select(
> 						AND(Person.q.firstName.startswith(fn),
> 							Person.q.middleInitial.startswith(mi),
> 							Person.q.lastName.startswith(ln)))
> 	return persons
> [...]
> What I would like is to implement a findPerson method that, based on the field values [...] Is there a elegant preferably generic solution for the above ? by generic I mean somthing that would work irrespective of the specific class.


The best way to do this is, I believe, is not to use those Person.q 
methods. "q" is an SQLBuilder object, which creates regular SQL queries 
when you compare it with strings and stuff. Example:

 >>> fname = "John"
 >>> lname = "Doe"
 >>> AND(Person.q.first_name == fname, person.last_name == lname)
(person.first_name = 'John AND person.last_name = 'Doe')
^^-- the comparison returns that string. Any expression involving 
Person.q's will be converted into a string, which is what the 
Person.select() method actually uses to do the query... its parameter is 
a regular SQL expression (everything after the WHERE part of "SELECT 
whatever FROM table").

So, the best way to do this is to write a clever SQL query that uses the 
"LIKE" operator, which can do fuzzy matches. Here's how it works:

http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1250

It would be something like: Person.select("firstname LIKE %"+fn+"%").
BUT, be careful that the variables you give the SELECT statement (fn, 
mi, etc) are properly quoted and escaped, otherwise someone could hax0r 
your database. :)