Yii Framework Forum: findAllBySql issue in selecting data from joined table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

findAllBySql issue in selecting data from joined table Rate Topic: -----

#1 User is offline   manilodisan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 80
  • Joined: 17-September 09
  • Location:Bucharest

Posted 18 October 2009 - 07:26 AM

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.
0

#2 User is offline   Spyros 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 461
  • Joined: 14-April 09
  • Location:Athens - Greece

Posted 18 October 2009 - 07:49 AM

You should add all the columns that return from the join as attributes in your WB_Roles model:
public $user_id;
0

#3 User is offline   manilodisan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 80
  • Joined: 17-September 09
  • Location:Bucharest

Posted 18 October 2009 - 08:24 AM

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.
0

#4 User is online   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 18 October 2009 - 08:50 AM

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users