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

Laura McCord Laura.McCord at doucet-austin.com
Wed May 5 17:20:15 EDT 2004


Actually, Kent Hoxsey clued me in on using <dtml-sqlgroup>, <dtml-and>
....

This is what resulted and it works:

select 
users.userid,
users.username,
users.useremail,
users.useroffice,
users.userpwd,
users.userpwdchg,
pcTable.pcname from users left join pcTable on users.pcid = pcTable.pcid

<dtml-sqlgroup where> users.userid = users.userid
<dtml-and>
<dtml-if username>
 <dtml-sqltest username column="users.username" type="string"
optional="Yes">
</dtml-if> 
 <dtml-and> 
 <dtml-if useroffice> 
  <dtml-sqltest useroffice column="users.useroffice" type="string"
optional="Yes"> 
 </dtml-if>
</dtml-sqlgroup>
order by users.useroffice, users.username asc

-Laura

P.S I didn't even know about <dtml-sqlgroup> or <dtml-and> until now. I
have been looking at a couple of zope books and neither had these.

-----Original Message-----
From: zope-db-bounces at zope.org [mailto:zope-db-bounces at zope.org] On
Behalf Of Charlie Clark
Sent: Wednesday, May 05, 2004 4:08 PM
To: Kent Hoxsey
Cc: zope-db at zope.org
Subject: Re: [Zope-DB] Z sql Method and using 'OR'


Eek! This is one of the reasons I really don't like DTML this is
essentially 
duplicating SQL functionality with a lot of impenetrable syntax making 
debugging more difficult because SQL is going to be generated.

On 2004-05-05 at 21:39:57 [+0200], Kent Hoxsey wrote:
> 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...

I think a good compromise can be achieved like this:

select * from users
where
<dtml-if username>
(username = <dtml-sqlvar name="username" type="string"> </dtml-if>
<dtml-if useroffice> useroffice = <dtml-sqlvar name="useroffice"
type="string"> </dtml>

I've got so used to this I haven't even started looking at SQLs own
approach 
to flow control...

Now, this only works for either "username" OR "useroffice" but will give
a 
syntax error if both are passed. There is an elegant solution to this 
problem...

Charlie

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



More information about the Zope-DB mailing list