Am Sonntag, den 30.10.2005, 17:11 -0700 schrieb Christopher Rivard:
Hello,
I am having a little difficulty with a result object from ZSQL. The MySQL form input is a series of checkboxes that are creating a "mapping table".
I have gone round and round and landed here:
The checkboxes are marshalled as: list_map_dir_id:list:int
SQL like this:
<dtml-in list_map_dir_id> insert into listing_map (list_map_dir_id, list_map_list_id) values (<dtml-sqlvar sequence-item type=int>, <dtml-sqlvar list_map_list_id type=string> ) <dtml-var sql_delimiter> </dtml-in>
This isnt that bad - you just get a bounch of insert statements which should work.
MySQL data type is int for this column.
All of this works fine, but ideally I would like to manipulate the id's as a list.
SQL knows no list, so the above is probably best you can do. Otoh, you could manipulate the values as ARRAY and use the database functions to select from array to table - depending on your database.
I have tried a few variations and regularly run into "len of unsized object" if I try to insert as a varchar or insert the full list as [0,1,2,3,4] I have tried a few different MySQL data types, but no luck.
Retrieving the results like this: select * from listing_map where list_map_list_id = <dtml-var list_id>
You should get into the habit of specifying the column names literally to avoid changes of order and case of your columns as they come from the database. Also you dont retrieve columns you might not need (if you extend the table later).
And mapping them back to the directory table like this:
select t1.* from directory as t1 left join listing_map as t2 on t1.dir_id = t2.list_map_dir_id where t1.dir_id = t2.list_map_dir_id && t2.list_map_list_id = <dtml-var list_id>
Write AND instead of && - && isnt SQL standard and makes it harder for you to change the database.
Is there a way to marshall the results of this ZSQL object back into a list?
ZSQL objects is a bit confusing. You probably mean the DTML elements of the ZSQL Methods. As I said, there is no list in SQL and Array is a bit specific to databases. For example you could do something like <dtml-sqlvar expr="'{%s}' % ','.join([str(x) for x in list_id])" type=string> would render '{1,3,4,5,6}' to the database which in Postgres would be an array.
I am branching the subsequent forms based on the above query results. The difficulty I am running into are exclusion situations
A and B not C
Seems like it would be easier if I could loop through and pull id's from a list. Maybe I'm asking the wrong question.
You should be able to optimize this by means of stored functions in your database.