DB_DataObject Complex joins

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

I have a query where I need to join several tables, one of which is
variable (otherwise I'd just hand-code the query). The main table is
called media, and has fields common to all media in my application
(like project_id, description, comments, etc). I have another couple of
tables, one provides a listing of keywords, and another maps keywords
to media.

I want to enable a search of media by keyword, allowing for multiple
keywords. For a query where ANY of the terms (OR) is acceptable, the
following code snippet works just fine:

$keyword = DB_DataObject::factory('keyword');
foreach ($terms as $term) {
  $term = trim($term);
  // $data['values']['searchPage']['whereAdd'] is either OR or AND
  $keyword->whereAdd("keyword.term LIKE '%" . $keyword->escape($term) .
"%'", $data['values']['searchPage']['whereAdd'] );

// Add the Joins
$media_has_keyword = DB_DataObject::factory('media_has_keyword');

This will not work when $data['values']['searchPage']['whereAdd'] =
'AND', because one keyword.term can never be like A and like B at the
same time (unless A == B).

One way I thought about getting around this was creating a new $keyword
object for each search term, and LEFT or RIGHT joining them to one
$media_has_keyword.   This did not work, but maybe I did not implement
it correctly.

Any one have any thoughts on how I can use DB_DataObjects to require
the media to have a mapping with ALL the keywords a user might specify?

Site Timeline