On 2009-09-16T20:12:13, Jean-Louis Leroy wrote:
Like this :
select((t.id)).from(t).where(t.name.like("pattern"))
select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id)
I recently played with most of the perl modules to generate SQL, and none really worked well for some semi-fancy reporting queries. On one hand I wanted a data structure so I could build a query dynamically: add column X to the select and group by expression, then do it for Y. Here is a collection of conditions for the where clause which should be 'AND'ed together etc. However if you build your entire query you are forced into using this new API even if most of features add no value, or when it adds overhead to something you can easily just type out: "select count(*) from tbl" "select (select max(value) from tbl1 where tbl1.id = tbl2.id) from tbl2 where ... group by tbl2.name" It would be great if you could start with a string of SQL, have the library parse it and provide features to refine expressions. Let the library optionally bind and escape values. Provide control over how the output should be normalized (case of keywords, sub query indent, field separators etc), and allow the SQL to be dumped. mysql, for instance, caches the exact string of the query. The SQL generation library should be separate from whatever talks to the database. You may not need to talk to the database, or there might be lots of magic with database handlers (load balancing, pooling, keep-alive etc). I hope it helps. /Allan -- Allan Wind Life Integrity, LLC <http://lifeintegrity.com>