Say I have two iterations like this: <ul> <dtml-in ..> <li>.. </dtml-in> <dtml-in ..> <li>.. </dtml-in> </ul> That use two different SQL queries, but return rows from the same table. Logically, to the user, these iterations render just one lists. How would I eliminate duplicates created by querying the same table? One thought is, but I'm not sure how to go about doing it, is to store a list of keys already displayed. Something like this (create a list named foo) <ul> <dtml-in ..> <li>.. (add id to list foo) </dtml-in> <dtml-in ..> (check to see if id is in list foo from last dtml-in) (if id is in list, skip) </dtml-in> </ul> Any ideas on what syntax I would use for that? -- Ken Kinder 303.381.7631
Ken Kinder wrote:
lists. How would I eliminate duplicates created by querying the same table?
Any ideas on what syntax I would use for that?
What kind of SQL statements are your running? If you have the SQL queries returning the same data types, and you're using a database that supports it, you could actually combine the SQL statements into one with a UNION: select first, second from sometable where second='blahblah' union select first, second from sometable where third='foo' (Although, that statement could easily be reduced to "... where second='blahblah' or third='foo'") Depending on the flavor of SQL, you might have to add another keyword in there somehwere to remove duplicates. If you'd still like to build a list of keys though, you could do the following (untested): <dtml-let mylist="[]"> <ul> <dtml-in ..> <dtml-call "mylist.append(fieldname)"> <li>.. </dtml-in> <dtml-in ..> <dtml-try> <dtml-if "mylist.index(fieldname) > -1"> <li>.. </dtml-if> <dtml-except> Already had this item . . . </dtml-try> </dtml-in> </ul> </dtml-let> The dtml-try in there is because the "index" function on a list gives an error if the item is not found. I don't know of any function that'll just return -1 or something, but I'm sure you could write one in python. Hope that helps some, -CJ
I'm using Oracle flavor SQL. Can I use that SQL on more than two? This will eventually be three queries. "Christopher J. Kucera" wrote:
Ken Kinder wrote:
lists. How would I eliminate duplicates created by querying the same table?
Any ideas on what syntax I would use for that?
What kind of SQL statements are your running?
If you have the SQL queries returning the same data types, and you're using a database that supports it, you could actually combine the SQL statements into one with a UNION:
select first, second from sometable where second='blahblah' union select first, second from sometable where third='foo'
(Although, that statement could easily be reduced to "... where second='blahblah' or third='foo'") Depending on the flavor of SQL, you might have to add another keyword in there somehwere to remove duplicates.
If you'd still like to build a list of keys though, you could do the following (untested):
<dtml-let mylist="[]"> <ul> <dtml-in ..> <dtml-call "mylist.append(fieldname)"> <li>.. </dtml-in> <dtml-in ..> <dtml-try> <dtml-if "mylist.index(fieldname) > -1"> <li>.. </dtml-if> <dtml-except> Already had this item . . . </dtml-try> </dtml-in> </ul> </dtml-let>
The dtml-try in there is because the "index" function on a list gives an error if the item is not found. I don't know of any function that'll just return -1 or something, but I'm sure you could write one in python.
Hope that helps some, -CJ
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- Ken Kinder 303.381.7631
Ken Kinder wrote:
I'm using Oracle flavor SQL. Can I use that SQL on more than two? This will eventually be three queries.
Yes. For efficiency's sake, though, you'd probably want to see if you could just combine them into a single statement: select distinct whatever from table where first='foo' or second='bar' or third='baz' . . . This would probably be a much better solution if it's possible with the queries you're using. -CJ
Ken Kinder wrote:
Say I have two iterations like this:
<ul> <dtml-in ..> <li>.. </dtml-in> <dtml-in ..> <li>.. </dtml-in> </ul>
That use two different SQL queries, but return rows from the same table. Logically, to the user, these iterations render just one lists. How would I eliminate duplicates created by querying the same table?
Why not just use UNION at the SQL level and get just one list with duplicates already magically removed ? so instead select * from cats where catcolour='grey'; and select * from cats where legs >=3; do select * from cats where catcolour='grey' union select * from cats where legs >=3 order by name; and use it in _one_ <dtml-in ...> -------------- Hannu
participants (3)
-
Christopher J. Kucera -
Hannu Krosing -
Ken Kinder