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…
$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*/
}
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?
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.