Hi, I am building a data intensive application which requires me to execute complex SQL queries such as:
select *, ((title like '%$keywords%')*2 + (description like '%$keywords%')) as rank from " . self::tableName() . " where (title like '%$keywords%' or description like '%$keywords%') $whereQuery $timeQuery $companyQuery $sortQuery
I am also building a frontend for which I require Pagination. Intially, I tried using ActiveDataProvider but it requires us to use DBCriteria. Then, I tried ArrayDataProvider, which works fine however, I am not able to access the relations when I use that. I have two tables : Job and Site. Job table has a Many to One relation with the Site table. I need to access the site data along with the job data in a single query.
P.S. Can you also suggest some better way to perform search. I cannot use MySQL full text search since it has a 3 character restriction. I plan to use SOLR or Zend Lucene later. Any other better alternative for relevance based searches.