Unknown Column - Multiple Joins in CDbCriteria

Question

I am trying to get data from several tables and I ended up with this error: SQL: SQLSTATE [42S22]: Column not found: 1054 Unknown column "p.firstname" in the "list of fields"

$criteria = new CDbCriteria; $criteria->select = 'ohu_id, hash, p.firstname, p.surname, p.city, u.email AS Email'; $criteria->join = 'LEFT JOIN `profiles` p ON p.user_id = user_id'; $criteria->join = 'LEFT JOIN users u ON user_id = u.id'; $criteria->condition = 'offer_id = :oID'; $criteria->params = array(':oID' => $_GET['id']); $model = MyModel::model()->findAll($criteria); 

Does anyone know what I'm doing wrong? Or is there a better way to get related data?

Answer 1

You make the same mistake as me.

You rewrite the first connection with the second, instead of adding the second connection .

 $criteria->join = "join ...."; //first join $criteria->join .= "join ...."; //second join 

amuses

Answer 2

In fact, his way is better to use a “c” clause for the user like this:

 $criteria->with = array( 'profiles '=>array( 'select'=>'profiles.user_id', 'together'=>true ), 'users'=>array( 'select'=>'users.id', 'together'=>true ) ); 

You can use this also in the model search for the CGridView DataProvider.

Answer 3

Better if you show your database structure. But here is a solution for joining multiple tables using left join

Code for joining tables:

 $criteria->select = 'ohu_id, hash, p.firstname, p.surname, p.city, u.email AS Email'; $criteria->alias = 'c'; $criteria->join = 'LEFT JOIN profiles p ON (p.user_id = c.user_id) LEFT JOIN users u ON (c.user_id = u.id)'; 

Hope this helps you.