[Zope] marshall zsql results

[Tino Wildenhain]

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.