Complex search with DB Search component of DA
I want to be able to do a keyword search across multiple fields. For example, I want the client to be able to search for -
red widgets
in fields A, B, and C.
I have a form with a single text field called 'keywords'. If I add more than one item to that field ('red widgets'), I get a query like this -
SELECT * FROM items WHERE (((item_primary LIKE '%red%') OR (item_secondary LIKE '%red%') OR (item_content LIKE '%red%')) OR ((item_primary LIKE '%widgets%') OR (item_secondary LIKE '%widgets%') OR (item_content LIKE '%widgets%'))) ORDER BY item_id ASC
And what I really want is a query like this -
SELECT * FROM items WHERE (((LOWER(item_primary) LIKE LOWER('%red%')) OR (LOWER(item_secondary) LIKE LOWER('%red%')) OR (item_content LIKE '%red%')) AND ((item_primary LIKE '%widgets%') OR (item_secondary LIKE '%widgets%') OR (item_content LIKE '%widgets%'))) ORDER BY item_id ASC
(I didn't propagate the LOWER() usages for simplicity)
How can I get that?
Thanks,
Murray