findAllBySql issue in selecting data from joined table

I have this piece of code which extracts some values from a table and a row from a joined table. In phpMyAdmin the row from the joined table is extracted without issues but in Yii I can’t get it out as it says:

Property WB_Roles.user_id is not defined

This makes me think that it doesn’t extract from joined tables. I tried even with placing full table names in front of rows instead of shortcuts such as ur or r.

$roles = WB_Roles::model ()->findAllBySql ( 'SELECT r.id, r.name, ur.user_id FROM wb_roles r LEFT JOIN wb_user_roles ur ON (ur.role_id = r.id AND ur.user_id = :user_id) WHERE r.id != :guest_id', array ( 

			

	':guest_id' => WB_Roles::WB_GUEST_ROLE, 

	':user_id' => $_GET [ 'id' ] 

) );

It’s probably because I use WB_Roles::model () to query the database but what should I use for custom queries like this.

You should add all the columns that return from the join as attributes in your WB_Roles model:

public $user_id;

Good idea Spyros, thank you. That’s a little too restrictive since the user_id field won’t be needed anywhere else besides this query and has absolutely no relation with the roles model so it doesn’t fit quite well within the context but…if I must, I will.

You could inherit everything from the model and add the attribute to the subclass.

class WB_RolesEx extends WB_Roles

{

  public $some_attribute;


  public static function model($className=__CLASS__)

  {

    return parent::model($className);

  }

}

/Tommy