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

[Laura McCord]

Wow. Makes perfect sense and it worked. Thanks for helping me. I have
never used some of those dtml keywords before. They will definitely come
in handy.

Thanks Again,
 Laura



-----Original Message-----
From: zope-db-bounces+laura.mccord=doucet-austin.com at zope.org
[mailto:zope-db-bounces+laura.mccord=doucet-austin.com at zope.org] On
Behalf Of Kent Hoxsey
Sent: Wednesday, May 05, 2004 2:40 PM
To: zope-db at zope.org
Subject: Re: [Zope-DB] Z sql Method and using 'OR'


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






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