basic SQL query

Archives Yii 1.0.x Discussions
1

This is just a very simple query but my mind is totally blank after the xmas break!

User table

id

name

Application table

id

<attributes>

user_id

I am outputting the values from the Application table, and I want to return the NAME value from the User table.

2

I suppose that you have a relation definded in your Application model (lets says that the relation is called user).

Then you use this way:

echo $application->user->name;

3

Where application is an instance of Application model.

4

I see that Christmas has left you knockout, ha ha ha

only use the relation name (must be defined in the model)

in the controller


 $model= Application table::model()->with('relName')->find();

in the view


$model->relName->name

greetings

beware of the new year!!

5

LOL thanks Horacia.

OK I cannot get this to work. This is what I have:

Model:




public function relations()

{

	return array(

	'name'=>array(self::HAS_ONE, 'User', 'id'),

	);

}

Controller:


$model=Application::model()->with('name')->findAll($criteria

);

View:


<?php echo CHtml::encode($model->name->name); ?>

I think I may have done my relations function wrong!

6

I think the relation should be:




'name'=>array(self::BELONGS_TO, 'User', 'user_id'),
7

OK last question of the day:

Table Application:

id

action_id

Table Audit:

id

application_id

action_id

action_details

action_date

I want to retrieve action_details and action_date matching on each Application action_id

I have tried defining the relations but it does not work correctly:


'action_details'=>array(self::BELONGS_TO, 'Audit', 'action_id'),
8

An application can have many Audits?

9

Yes that’s right it can.

10

Then you must define the relation this way:

I suppose that The relation is done throught Application.action_id = Audit.action_id




'action_details'=>array(self::HAS_MANY, 'Audit', '','on'=>'Application.action_id = Audit.action_id'),

And to retrieve the data:




$application = Application::model()->with('action_details')->findAll();


foreach($application->action_details as $action_detail){

   echo $action_detail->actions_details; //The field action_details of the Audit table.

   echo $action_detail->action_date; // The field action_date of the Audit table.

}

Hope this help you…

The code is what I have in my mind but is not tested…

11

I got error:

Column not found: 1054 Unknown column ‘Audit.action_id’ in ‘on clause’

12

My mistake:

Use this in the relations method:




'action_details'=>array(self::HAS_MANY, 'Audit', '','on'=>'Application.action_id = Audit.action_id','alias'=>'Audit'),

Question: what yii version are you using?

13

Thanks. Actually HAS_MANY did not work but BELONGS_TO is working for me…

I’m using version 1.0.10