Class::DBI::Mysql::FullTextSearch

From ClassDBI

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, /etc/resolv.conf, etc.)

In an attempt to provide full text index search capabilities, I was able to do the following using set_sql in my Host 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 Hosts in which any of their data items matches the string 'somestring'.

--EricBerg