Using SQL queries with ActiveDataProvider

Hi, I am building a data intensive application which requires me to execute complex SQL queries such as:

select *, ((title like '%$keywords[0]%')*2 + (description like '%$keywords[0]%')) as rank

                                from " . self::tableName() . "

                                where (title like '%$keywords[0]%'

                                or description like '%$keywords[0]%')

                                $whereQuery $timeQuery $companyQuery


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.

The best idea is to use CActiveDataProvider, you can do this query with CDbCriteria without problems:

$criteria=new CDbCriteria;

$criteria->select="select *, ((title like '%$keywords[0]%')*2 + (description like '%$keywords[0]%')) as rank";

$criteria->condition = "(title like '%$keywords[0]%'

                                or description like '%$keywords[0]%')

                                $whereQuery $timeQuery $companyQuery


No problems. This allows you to use all relation as you wish

Why don’t you like CDbCritieria? Your query can be described with a criteria, too. For example add public $rank to your model and try:

$criteria=new CDbCriteria;

$criteria->select='*,(title like :keyword)*2 + (description like :keyword)) as rank';


// ...