my $mquery =  "INSERT INTO Search_Results ( search_id, page_id )
         SELECT ?, P.id
         FROM Pages P, Documents D, Dirs DI
         WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE )
              AND P.doc_id = D.id
              AND D.dir_id = DI.id ";
[download]

For example, here's what the query code looks like for a more complex case, when the user might be searching within an existing result set.

my $mquery =  "INSERT INTO Search_Results ( search_id, page_id )
                  SELECT ?, P.id
                  FROM Pages P, Documents D, Dirs DI ";

if( $ref_sid ) {
    # constrain search to a previous result set.
    $mquery .= ", Search_Results SR ";
}

$mquery .=     "WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE )
                    AND P.doc_id = D.id
                   AND D.dir_id = DI.id ";

if( $ref_sid ) {
    $mquery .= "AND P.id = SR.page_id AND SR.search_id = ? ";
    push @query_args, $ref_sid;
}
[download]

I got myself into this hole and now I want to climb out by rewriting the search stuff over the coming holiday. I have a few ideas for how I can handle this better.

1. Use Template Toolkit to generate the SQL.
   I've used this strategy elsewhere, though not for an application as complex as this one. At the end of the day, I don't know if my SQL templates will be any easier to maintain as the Perl code now.
2. Use Class::DBI, passing in a hash of constraints to find. This seems like it might be rather slow, and it would be disastrous if I have to retrieve all the records and then insert them into the Search_Results table. Fulltext searching is very expensive and the server is quite underpowered. With more than half a million entries and many searches that return tens of thousands of rows, this might not be a valid solution.
3. Write a separate query for every possible search situation. There are currently eleven basic permutations of options, so I would write eleven separate queries. This has the disadvantage of duplicating a lot of SQL, making it difficult to maintain if the table structure is adjusted or new metadata fields are added.

I'm sure the problem of dynamically generating SQL is a common one. Any experience you can share, even if not directly related, could be helpful. Thanks.

friedo