Howto Create an AR with custom sql

Yii 1.1 General Discussion
1

Hey all,

i’ve been searching the forum - wiki for about an hour now, it possible it was already posted but i couldn’t find anything about it

i wanted to create an AR (read only) that’s based on custom sql.

basecly it would be a model based on a view but i cannot make a view (no access in my db)

so i wanted to make an AR (or another model) based on this sql:




SELECT t1.pnr as nr, t2.emailpr as Email, t1.inlog as Paswoord, t1.standplaats as Stad, t2.naam as Naam, t2.voornaam as Voornaam

FROM adres_personeel t1

LEFT JOIN adressen t2 ON t1.plink = t2.nr

the problem is that i have a table with all adresses and name etc, but the passwords are in a different table

because only the people who are in the password table should be able to login

the easy way out is to create it on UserIdentity level and just have 2 models and do the join there.

is there anyway to do it the hard way ?

2

You can use the a model based on the table adres_personeel:

In the search function just do:




$criteria=new CDbCriteria;

$criteria->select='t.pnr as nr, t1.emailpr as Email, t.inlog as Paswoord, t.standplaats as Stad, t1.naam as Naam, t1.voornaam as Voornaam';

$criteria->join="LEFT JOIN adressen t1 ON t.plink = t1.nr ";

If you declare a relation, instead of join you can use width. Remember that standard Yii alias are t for the main table, t1, t2 and so on for related table (anyway there is a property alias for use your custom one if needed).