Posted by richard on 07/30/05 18:38

Tim Van Wassenhove wrote:

> On 2005-07-30, richard <richard@localhost.localdomain> wrote:
>> I work for a company called zedcore.com, and we code php for money. We
>> have solved this issue. I'll explain what we have done.
>
> [snip code]
>
> These are a couple of questions i have with a lot of classes/functions
> that proclaim the query generation:
>
> - Does it allow you add a column LIKE 'value%' instead of
> column='value'?
>
> - Does it allow you to have a "complex" condition like
> column=MAX(column) or is that translated to column='MAX(column)'?
>
> - How does it handle with values like O'Reilly? (It would be silly if
> you have to escape the value yourself.)

Well, the where bit is a little so so. But the sql array tells a recordset
object that contains field objects to load from the db. Each field object
has function GetValue() GetHTMLValue() GetSQLValue() etc. But sometimes you
want like sometimes you want ilike, sometimes you want like, sometimes you
want =. This is not a field dependent thing.

"Where"s are complex for that very reason. MySQL,Postgresql and SQLLite are
all convergent on the where's (except with sub queries) so you can follow
them across.

When doing complex searching for recordset based stuff - where say a field
is an integer and the search is a range between two values you have two
cases. One, is that the query is a system query and so a little bit of
query building may be required. The other is that the search is based on
user input. In which case we build a form using an administration block (a
type of class) that can also show the results in a Sorter-Pager.

We have a couple of these, and you specify like or whatever in a definition,
which of course, you can override after object creation should you so
decide.

It is worth saying that we have some complex queries that are crammed into
the sql array (as using our sql arrays allows sorter - pagers to work
correctly), building up a tool to autogenerate such queries would be
counter productive.

for example:


(SELECT url.*, docs.blockid, docs.title, docs.startdatetime, docs.ref,
docs.summary, docs.keywords, docs.filename, docs.filesize, docs.filecode ,
lower(docs.title) as titlelower, 0 as isurllink, lower(docs.ref) as
reflower, blocks.createddatetime, blocks.updateddatetime, blocks.def FROM
(url JOIN docs USING (blockid)) JOIN blocks USING (blockid) WHERE
(docs.usestopdatetime=false OR docs.stopdatetime>current_date) and
url.type='Page' AND url.subtype LIKE 'Document%' AND url.parenturlid=1744
UNION SELECT url.*, docs.blockid, docs.title, docs.startdatetime, docs.ref,
docs.summary, docs.keywords, docs.filename, docs.filesize, docs.filecode ,
lower(docs.title) as titlelower, 1 as isurllink, lower(docs.ref) as
reflower, blocks.createddatetime, blocks.updateddatetime, blocks.def FROM
((urllinks JOIN url ON (urllinks.urlid=url.urlid AND
urllinks.parenturlid=1744)) JOIN docs USING (blockid)) JOIN blocks USING
(blockid) WHERE (docs.usestopdatetime=false OR
docs.stopdatetime>current_date) and url.type='Page' AND url.subtype LIKE
'Document%') ORDER BY startdatetime DESC, titlelower DESC, reflower DESC

Thats 5 joins and a union using 4 tables. Sometimes its hard enough to come
up with the sql let alone code something to build it.

[Back to original message]