> If you don't care about or don't expect duplicates, do this instead:
>
> $foos =
> Foo::Manager->get_foos(fetch_only => [ 't1' ],
> require_objects => [ 'keywords' ],
> query => [ keyword => 'tra' ]);
>
> and the DISTINC keyword will be omitted:
>
> SELECT
> t1.id,
> t1.name,
> ...rest of the columns from the foo table only...
> FROM
> foos t1,
> foo_keywords t2
> WHERE
> t2.keyword = 'tra' AND
> t1.id = t2.foo_id
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
Or maybe:
SELECT * FROM foo 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")
Or maybe:
SELECT t1.*, count(t2.keyword) cnt FROM foo t1, foo_keywords t2
WHERE t1.id = t2.foo_id AND t2.keyword IN ('tra', 'pla')
GROUP BY t1.id HAVING cnt = 2
I'm just typing this stuff in; I've probably got problems in all of the
above, but hopefully you get the idea.
-mda
-------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
