Using SQL queries with ActiveDataProvider

Yii 1.1 General Discussion
1

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

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

2

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

                                $sortQuery";

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

3

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

$criteria->params=array(':keyword'=>'%'.$keywords[0].'%');

// ...