I am trying to port an existing application to the yii framework.
I have the following sql query:
SELECT t1.*, t2.username AS usern, t3.value AS voted, CASE WHEN t4.value IS NULL THEN 0 ELSE t4.value END AS val, t5.date AS faved, t6.date AS visited
FROM (SELECT * FROM entries ORDER BY id DESC LIMIT 0,300)
AS t1 JOIN users AS t2
ON t1.user = t2.id
LEFT JOIN votes AS t3
ON t3.user_id = '$user' AND t3.type = '0' AND t3.type_id = t1.id
LEFT JOIN(
SELECT SUM(value) AS value, type_id, type
FROM votes
WHERE type = 0
GROUP BY type, type_id
) AS t4
ON t4.type = 0 AND t4.type_id = t1.id
LEFT JOIN favs AS t5
ON t5.type = 0 AND t1.id = t5.type_id AND '$user' = t5.user_id
LEFT JOIN visited AS t6
ON t6.user_id = '$user' AND t1.id = t6.entry_id
ORDER BY val DESC, t1.date DESC
LIMIT $page, $entrycount
Is there a possibility in yii for implementing this query in a clear and simple way without losing performance? What would be the best way to approach this?
I have tried to start small with using CActiveDataProvider :
$dataProvider = new CActiveDataProvider('Entries', array(
'criteria'=>array(
'select' => array('t.*', 'SUM(votes.value) AS test'),
'join' => 'join votes ON votes.type_id = t.id',
'condition' => 'votes.type = 0',
'group' => 't.id',
'with'=>array('userm'),
),
'pagination'=>array('pageSize' => 30,)
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
Relations in Model:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'comments' => array(self::HAS_MANY, 'Comments', 'entry_id'),
'userm' => array(self::BELONGS_TO, 'Users', 'user'),
'visiteds' => array(self::HAS_MANY, 'Visited', 'entry_id'),
'favs' => array(self::HAS_MANY, 'Favs', 'type_id', 'condition' => 'favs.type = 0'),
'votes' => array(self::HAS_MANY, 'Votes', 'type_id', 'condition' => 'votes.type = 0'),
'voteSum'=>array(self::STAT, 'Votes', 'type_id', 'condition' => 'type = 0', 'select' => 'SUM(value)'),
'commentSum'=>array(self::STAT, 'Comments', 'entry_id', 'select' => 'Count(id)')
);
}
But I am not really sure how to handle multiple joins with subqueries in combination with calculated queries in yii.
Any help would be greatly appreciated.