How Can I Write This Query

Yii 1.1 General Discussion
1

I have 3 tables:


catalog [id, title, price_id, tm]

comment [id, text, catalog_id, user_id, user_ip]

price [id, title, price]

I must get this query:


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)

Maybe I must use with() and relations

Catalog Model:


return array(

            'price' => array(self::BELONGS_TO, 'Price', 'price_id'),

            'comment' => array(self::HAS_MANY, 'Comment', 'catalog_id'),

		);
2

This should work:

$criteria = new CDbCriteria;

$criteria->with = array(‘price’, 'comment);

$criteria->together = true;

$criteria->condition = ‘((t.tm > 0 AND comment.user_id != :user_id AND comment.user_ip != :user_ip) OR (t.tm>0 AND comment.id IS NULL))’;

$criteria->params = array(’:user_ip’=> $userIP,’:user_id’ => $userID );

$models = CreateJob::model()->findAll($criteria);

3

It’s not work.

This code use ‘distinct’. I need get all result and I can not select from comment table.

4

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();

Read more here:

http://www.yiiframework.com/doc/guide/1.1/en/database.dao