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

[Casey Duncan]

On Thu, 06 May 2004 09:20:06 +0200
Charlie Clark <charlie at egenix.com> wrote:

> 
> On 2004-05-06 at 00:04:58 [+0200], Jim Penny wrote:
> > AND ALSO
> > ...
> > where username=<dtml-sqlvar name="username" type="string">
> > <dtml-if useroffice>
> >   and useroffice = <dtml-sqlvar name="useroffice" type="string">
> > </dtml-if>
> > 
> > Note that the conjunction has moved inside the dtml-if guard.
> > I claim this is MUCH easier to read.
> > 
> > In general, it is easy to introduce a dummy clause in SQL which
> > permits you to have a static first clause, with very little loss of
> > efficiency and a large gain in readability.
> 
> Total agreement and if you use the following it is totally flexible:
> 
> SELECT * FROM table
> WHERE 1
> <dtml-if var1>
> AND myvar = <dtml-sqlvar var1 type="string">
> </dtml-if>
> ...
> 
> It is very important to understand that ZSQL-methods should be as
> close to straight SQL as possible just in case you need to talk to a
> DB expert if you have problems or after running EXPLAIN or similar.

This can also be written as:

SELECT * FROM table
<dtml-sqlgroup where>
  <dtml-sqltest myvar type="nb" optional>
</dtml-sqlgroup>

Using type="nb" (non-blank) with optional allows you to dispense with
the <dtml-if> checks since the sqltest will be automatically omitted if
myvar is empty and therefore the where clause is thus omitted by
<dtml-sqlgroup>.

This can be expanded to something like:

SELECT * FROM contacts
<dtml-sqlgroup where>
  <dtml-sqltest fname type="nb" optional>
<dtml-and>
  <dtml-sqltest lname type="nb" optional>
<dtml-and>
  <dtml-sqltest state type="nb" optional>
<dtml-and>
  <dtml-sqltest city type="nb" optional>
</dtml-sqlgroup>

This would allow you to search contacts by any combination of name,
state or city, or also select all contacts.

At some point in the complexity though it is better to break it into
separate sql methods. Basically don't make it more flexible then it
needs to be for each use. More simple SQL methods is usually better than
a few god-awful complex ones.

-Casey