On Nov 6, 4:29 am, Alexandre CONRAD [EMAIL PROTECTED] wrote:
Hello,
what would be the best way to perform a search against columns of a
table ? I have the following code that works fine:
pattern = %bla%
client_list = self.query.select(or_(model.Client.c.name.like(pattern),
model.Client.c.contact.like(pattern), model.Client.c.email.like(pattern)))
This generates the following SQL:
SELECT clients.name AS clients_name, clients.contact AS clients_contact,
clients.email AS clients_email, clients.id_client AS clients_id_client
FROM clients
WHERE clients.name LIKE %s OR clients.contact LIKE %s OR clients.email
LIKE %s ORDER BY clients.name
['%bla%', '%bla%', '%bla%']
Is this the correct way to do it ? Isn't there a way I could give a list
of columns to search in rather than doing it like my example ? Or have a
short way to search for the given pattern in all columns ?
I suppose you could write a function that provided the list of columns
for you.
example
class myclass(object):
def search(self,pattern):
# pseudo code
for column in [ 'a', 'b', 'c', 'd' ...etc ]:
somequery.appendorclause ( column, pattern )
return somequery (or somequery.execute() ) etc.
If you were to take that approach though.. the sql that is generated
would be the same as what you already came up with.
The only other approach I can think of is to use your databases (if
applicable) full text indexing feature and create a FTI on all of the
columns you want searched. I'm currently doing that with Postgresql
(tsearch2) for a project and it works quite well.
-Dennis
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---