[Zope-DB] Z sql Method and using 'OR'

[Kent Hoxsey]

If I understand correctly, you should not be trying to solve this
problem in SQL, but rather in the ZSQL method itself. I know that
sounds a bit confusing, but stick with me here.

As I understand your problem statement, you want to give the
user a search form which provides a variety of different attributes
the user can enter. You would then like to generate the appropriate
SQL to perform the search, based on the values the user entered.

This is common in the world of client-server tools, usually called
QBE (Query By Example).

I would handle this example by offering the user a form providing 
the two possible data entry fields 'username' and 'useroffice', and
pass whatever the user enters to parameters in my ZSQL.

The ZSQL method would have two input parameters, 'username'
and 'useroffice', and would look like this:

select * from users u
<dtml-sqlgroup where> u.user_id = u.user_id
<dtml-and>
<dtml-if username>
<dtml-sqltest username column="u.username" type="string" optional="Yes">
</dtml-if>
<dtml-and>
<dtml-if useroffice>
<dtml-sqltest useroffice column="u.useroffice" type="string" optional="Yes">
</dtml-if>

I know this is a bit complicated and messy, but that's due to ZSQL methods
using DTML to dynamically define the SQL (switch ZSQL to PageTemplates
and TAL? we'll save that discussion for another time...) . Stick with me
and I'll break it down...

The first part of the where clause (u.user_id = u.user_id) is based on the
assumption that the table has a unique primary key named user_id, and
allows my user to simply click the Search button without entering any
parameters. The DTML will still generate valid SQL, and will return all
users in my table.

The second part of the where clause is a combination of dtml-and, dtml-if,
and dtml-sqltest. If the user provides a username, this clause will generate
SQL that looks like this:

   and username='Jill'

The third part of the where clause repeats the function defined above, but
for the useroffice input.

Kent
-----Original Message-----
From: Laura McCord <Laura.McCord at doucet-austin.com>
Sent: May 5, 2004 12:00 PM
To: zope-db at zope.org
Subject: [Zope-DB] Z sql Method and using 'OR'

I wrote a sql method and the OR is not really working. I want to be able
to either search for a user by name or search by location. But the way
it is now is if I only enter a name such as 'Jill' in the name field and
click search. The query ends up searching for anyone whose name is
'Jill' and anyone with NULL in the office field.

What am I doing wrong because now I can see why I am getting that error
but unsure how to revise my query.

select * from users
where
(username = '<dtml-var name="username" sql_quote>' OR
useroffice = '<dtml-var name="useroffice" sql_quote>')

-Laura

_______________________________________________
Zope-DB mailing list
Zope-DB at zope.org
http://mail.zope.org/mailman/listinfo/zope-db