CODEWITHSUNDEEP
phpyii
lacas
lacas

Reputation: 14066

Yii select with multiple tables with conditions

I am fairly new in yii, how can I do this query with yii?

the query is:

select table1.userid as userid, table2.name as username, table3.name as tourname from table1, table2, table3 where table1.userid=table2.id and table1.tid=table3.id order by table1.weight, table1.result

Thanks, Leslie

Upvotes: 0

Views: 720

Answers (2)

Felipe
Felipe

Reputation: 11887

You could also use Yii's Query Builder for that.

It would look something like this:

$results = Yii::app()->db->createCommand()
    ->select('t1.userid as userid,t2.name as username,t3.name as tourname')
    ->from('table1 t1')
    ->join('table2 t2','on'=> 't1.userid=t2.id') // could be ->leftJoin() as well
    ->join('table3 t3,'on'=>'t1.tid=t3.id')
    ->order('t1.weight,t1,result')
    ->queryAll()

Note that I've used joins instead of naming both tables in the where clause. These are two different ways of performing relational queries in SQL. Your method is called implicit joins and mine is called explicit join.

I used to write implicit joins but now I use mostly explicit joins as they are easier to maintain, read and change if needed.

More info on CDBCommand here.

Upvotes: 2

David Moritz
David Moritz

Reputation: 92

Ineersa was correct. The most direct answer to the question is:

Yii::app()->db->createCommand("select table1.userid as userid, table2.name as username, table3.name as tourname from table1, table2, table3 where table1.userid=table2.id and table1.tid=table3.id order by table1.weight, table1.result")->queryAll();

But I think you should take advantage of Yii's ability to have relationships so that you can access this information better.

Upvotes: 1

Related Questions