Class::DBI::Mysql::FullTextSearch

Taking an example from the Using joins page, I have two CDBI classes: Host and HostInfo, where Host has a has_many relationship to HostInfo. HostInfo basically contains a Host identifier and system information data. (We keep many types of host information, including things like the output of 'rpm -qa', and system files like /etc/hosts</tt>, /etc/resolv.conf</tt>, etc.)

In an attempt to provide full text index search capabilities, I was able to do the following using set_sql in my Host</tt> class:

__PACKAGE__->set_sql(data_items => <<'SQL'); SELECT __ESSENTIAL__ FROM __TABLE(Host=h)__, __TABLE(HostInfo=hd)__ where h.id = hd.host and match (hd.data) against (?) order by h.hostname asc SQL

Then I can invoke the search simply as

$hosts = Host->search_data_items('somestring')

and we get an iterator of Host</tt>s in which any of their data items matches the string 'somestring'.

--EricBerg