|
|
Posted by hotgazpacho on 09/21/05 16:54
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');
$media_has_keyword->joinAdd($keyword);
$media->joinAdd($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?
Navigation:
[Reply to this message]
|