On 1/29/06 12:28 AM, Mark D. Anderson wrote:
> Thanks John. Now what about multi-keyword search?
> I would want to get all Foo rows that appear in the FooKeyword
> table with *all* supplied terms, not just "any", which is what
> I'd get with keyword => ['tra', 'pla'].
> The desired SQL would be something like this I guess:
>
> SELECT t1.* FROM foo t1, foo_keywords t2, foo_keywords t3
> WHERE
> t2.keyword = 'tra' AND t1.id = t2.foo_id AND
> t3.keyword = 'pla' AND t1.id = t3.foo_id
I was about to suggest using the "clauses" escape-hatch parameter for adding
literal SQL to the WHERE clause, but then I realized that this should work:
$foos =
Foo::Manager->get_foos(
distinct => 1,
require_objects => [ 'keywords', 'keywords' ],
query =>
[
't2.keyword' => 'kw a',
't3.keyword' => 'kw b',
]);
And sure enough, it does, producing this SQL:
SELECT DISTINCT
t1.id,
t1.name,
..rest of the columns from the foo table only...
FROM
foos t1,
foo_keywords t2,
foo_keywords t3
WHERE
t2.keyword = 'kw a' AND
t3.keyword = 'kw b' AND
t1.id = t2.foo_id AND
t1.id = t3.foo_id
If you prefer the EXISTS approach, there's currently no abstract syntax for
that. As I said, you'd have to use the "clauses" parameter.
$foos =
Foo::Manager->get_foos(
clauses =>
[
'EXISTS (SELECT * FROM foo_keywords WHERE ' .
'foo_keywords.foo_id = foo.id AND keyword = "tra")',
'EXISTS (SELECT * FROM foo_keywords WHERE ' .
'foo_keywords.foo_id = foo.id AND keyword = "pla")',
]);
Which would produce this SQL:
SELECT
id,
name
FROM
foos
WHERE
EXISTS (SELECT * FROM foo_keywords WHERE foo_keywords.foo_id = foo.id
AND keyword = "tra") AND
EXISTS (SELECT * FROM foo_keywords WHERE foo_keywords.foo_id = foo.id
AND keyword = "pla")
Unfortunately, some databases do not support the EXISTS keyword. (MySQL,
for example.)
-John
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
