Hi,
I am trying to expriment on using fts and sql at the same time. Anyway I guess you can, this is my test query:
select * from (
select name, parent_id,
item_id,
case when i.content_type = 'content_folder' then content_folder__get_label(i.item_id) else bcms__get_title(i.item_id, 'live') end as title,
live_revision
from cr_items i,
(select tree_sortkey from cr_items where item_id = '326') r , txt
where tree_ancestor_p(r.tree_sortkey, i.tree_sortkey) and txt.fts_index @@ '\'test\'' and txt.tid = i.live_revision
) results
where name like '%km%''
Its a test query. Anyway using txt table and txt.fts_index and txt.tid with your sql does seem to work.
If anyone is trying to use openfts on with other sql please share you notes.