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'.