Convert Sql To Yii Query

Yii 1.1 General Discussion
1

hi all ,

i want to write following sql query using yii help me?

SELECT user. * , uc.value, ct.description

FROM users AS user

LEFT JOIN user_contacts AS uc ON uc.id = user.id

LEFT JOIN contact_types AS ct ON ct.id = uc.type_id

WHERE user.id =28

2 

Yii::app()->db->createCommand()

    ->select('user. * , uc.value, ct.description')

    ->from('users user')

    ->leftJoin('user_contacts uc', 'uc.id = user.id')

    ->leftJoin('contact_types ct', 'ct.id = uc.type_id')

    ->where('user.id =:id', array(':id'=>28))

    ->queryAll();

http://www.yiiframework.com/doc/guide/1.1/pl/database.query-builder

3

Hi

yoy can write a something look like this




            $criteria = new CDbCriteria;

            $criteria->select = 'user. *, uc.value,ct.description';

            $criteria->join = ' LEFT JOIN user_contacts AS uc ON uc.id = user.id

			       LEFT JOIN contact_types AS ct ON ct.id = uc.type_id';

            $criteria->addCondition("user.id=".28);

            $resultSet    =  Users::model()->findAll($criteria);

            p($resultSet);

Note: If you can fetch all record you can use findAll method otherwise you can use find method

4

Hi Friends,

please do not use createCommnad condition becuse yii is provied the AR and DAO,you can use both of you.Below is diffrent

Active Records means your object has one-one,many-to-many…etc… relation if you can use using Gii and generate the code.

Data Access Object means you can write the complex query… using createCommnad but performance is very slow…

site perfomance is important so you can use every time write query using


$criteria = new CDbCriteria;

Hope you can understand me.

Best Regards,

Ankit Modi

5

Sry, but I do not understand You, do You mean that AR is faster than DAO?

6

Hi All,

Please help me to write the query in yii 1

SELECT COUNT(DISTINCT “t”.TRNSCTN_NMBR) AS num
FROM “TRNSCTN_MODE_DTLS_ONLINE” “t”
LEFT JOIN “REPUSH_TRANSACTION” “repushTrans”
ON (“t”.“TRNSCTN_NMBR” = “repushTrans”.“TRAN_ID”)
WHERE ((((((“t”.TRNSCTN_NMBR LIKE 100000013650)
AND (CLRNCE_FLAG =‘F’))
AND (“repushTrans”.“TRN_STATUS” IS NULL))
OR (“repushTrans”.TRN_STATUS IN (‘F’, ‘R’)))
AND (“t”.“TRNSCTN_NMBR” =100000013650)))

Note : where t denotes current value