SELECT t.title,t2.id, t2.user_ip, t2.user_id, t.tm, t3.title
FROM `catalog` as t
LEFT JOIN `comment` as t2 ON t.id = t2.catalog_id
LEFT JOIN price as t3 ON t.price_id = t3.id
WHERE ((t.tm>2 AND t2.user_id != 1
AND t2.user_ip != '127.0.0.1')
OR(t.tm>0 AND t2.id IS NULL))
But I can write this query with yii
this is my code:
$userIP = "127.0.0.1";
$userID = 1;
$criteria = new CDbCriteria;
$criteria->select = array('t.title', 't3.title as tt','t2.text');
$criteria->join = 'LEFT JOIN comment as t2 ON t.id = t2.catalog_id LEFT JOIN price as t3 ON t.price_id = t3.id ';
$criteria->condition = '((t.tm>0 AND t2.user_id != :user_id AND t2.user_ip != :user_ip)OR(t.tm>0 AND t2.id IS NULL))';
$criteria->params = array(':user_ip'=> $userIP,':user_id' => $userID );
$models = Catalog ::model()->findAll($criteria)
If you want you can basically paste your query right into Yii.
$db = Yii::app()->db;
$sql = 'SELECT t.title,t2.id, t2.user_ip, t2.user_id, t.tm, t3.title
FROM `catalog` as t
LEFT JOIN `comment` as t2 ON t.id = t2.catalog_id
LEFT JOIN price as t3 ON t.price_id = t3.id
WHERE ((t.tm>2 AND t2.user_id != 1
AND t2.user_ip != "127.0.0.1")
OR(t.tm>0 AND t2.id IS NULL))';
$command = $db->createCommand($sql);
$result = $command->queryAll();
$result will be an array of results, not a object.
I’m not sure if your conditions change, or if you’ll be taking user input as parameters, if so, you’ll want to use parameter binding.
$db = Yii::app()->db;
$sql = 'SELECT t.title,t2.id, t2.user_ip, t2.user_id, t.tm, t3.title
FROM `catalog` as t
LEFT JOIN `comment` as t2 ON t.id = t2.catalog_id
LEFT JOIN price as t3 ON t.price_id = t3.id
WHERE ((t.tm>:a AND t2.user_id != :b
AND t2.user_ip != :c)
OR(t.tm><img src='http://www.yiiframework.com/forum/public/style_emoticons/default/biggrin.gif' class='bbc_emoticon' alt=':D' /> AND t2.id IS NULL))';
$command = $db->createCommand($sql);
$command->bindParam(":a",$a,PDO::PARAM_INT);
$command->bindParam(":b",$b,PDO::PARAM_INT);
$command->bindParam(":c",$c,PDO::PARAM_STR);
$command->bindParam("<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/biggrin.gif' class='bbc_emoticon' alt=':D' />",$d,PDO::PARAM_INT);
$result = $command->queryAll();