Advanced use of CDbCriteria

Hi All,

I want to perform an SQL query using CDbCriteria. It is based on the Doctrine Project’s searchable behavior (please see here).




SELECT

  COUNT(keyword) AS relevance,

  i.id,

  d.title

  // column list excluded from join table

FROM Index i

JOIN Documents d ON (i.id=d.id)

WHERE d.id IN

(

  SELECT id

  FROM Index

  WHERE keyword = 'word'

)

GROUP BY id

ORDER BY relevance DESC



I want the results to be used as CActiveDataProvider criteria hence the need to this type of query to be done in CDbCriteria.

EDIT: Changed the SQL code to include Join statement…

This is the CDbCriteria you need:




$criteria=new CDbCriteria;

$criteria->alias='i';

$criteria->select= 'COUNT(keyword) AS relevance, i.id, d.title';

$criteria->join= 'JOIN Documents d ON (i.id=d.id)';

$criteria->condition=" d.id IN

(

  SELECT id

  FROM Index

  WHERE keyword = 'word'

)

";

$criteria->group='id';

$criteria->order=' relevance DESC';



You should use this criteria for a findAll() or in a dataProvider of "index" models.

You have to define ‘title’ and ‘relevance’ as public property in the model index if you want to access them




class index extends CActiveRecord

{

   public $title;

   public $relevance;

   /*etc etc*/


}



Thanks for the suggestion.

For now it looks to have worked. If I count() the results the amount is as expected, however I can only access the index table columns and not the joined table. Should I declare a relationship between the two table or supply the CDbCriteria with a with() statement?

Actually I figured it out…yes, I do need to add the relation to my model.

Thanks for your help. Appreciated.

Help me understand this. I thought the whole reason to use active record was to make queries database-agnostic. Here all we have done is the rearrange the native sql into CDbCriteria properties. If the sql was database-agnostic to begin with, why even use active record? Heck, there is an entire subquery in explicit native sql. Why can’t the whole thing be in native sql?

The main concept behind Active Record is to encapsulate data and business logic. Database-abstraction is more a (welcome) side-effect than a goal, but no ORM can be fully database-agnostic.