SQL join query

Archives Yii 1.0.x Discussions
1

I posted this question in a thread yesterday and I made some progress but I’ve ran in to some difficulty again.




Table Application:

-----------------

application_id

status_id




Table Audit:

------------

audit_id

application_id

action_id

action_details

action_date

Basically every time a row in Table Application is updated, a new row is created in Table Audit. One Application can have many Audits, in my view I want to output Audit.action_details for the MOST RECENT ENTRY as follows:


Application.application_id = Audit.application_id

Application.status_id = Audit.action_id

Select and output the last entry. I also want to output Audit.action_date as follows:


Application.application_id = Audit.application_id

Audit.action_id = "001"

I have done the following in my relations:




'app_status'=>array(self::BELONGS_TO, 'Audit', '', 'on'=>'Application.status_id = Audit.action_id', 'alias'=>'Audit'),


'app_date'=>array(self::BELONGS_TO, 'Audit', '', 'on'=>'Application.application_id = Audit2.application_id AND Audit2.action_id=001', 'alias'=>'Audit2'),

And the following in my controller:


$models=Application::model()->with('app_status', 'app_date')->findAll();

But it does not always work as expected. Can anyone advise? Thanks.

2

is practically impossible to work your code

nothing matches in relations

Application.application_status?

Application.id?

Audit2.app_id?

:angry:

but I dare say that the problem is:

change self::BELONGS_TO for self::HAS_MANY

and change

Audit2.action_id=001

for

Audit2.action_id=‘001’

3

Hi Horacia, sorry I’ve just modified the original code - can you take another look now? Thanks.

4

I tried using HAS_MANY but that gave me error: Trying to get property of non-object

I output in my view like this: <?php echo CHtml::encode($model->app_status->action_details); ?>

5

This is becaus of HAS_MANY $model->app_status is an array.

You must do something like:




foreach($model->app_status as $status){

   echo CHtml::encode($status->action_details);

}
6

Thanks Pol. The thing is I don’t want to generate an array, I only want to retrieve ONE row (the most recent).

7

Yep! But because the relation is HAS_MANY the array is generated automatically regardless of it contains 1 or 100 rows.

Then your problen is how to say that you only want the most recent one. (Order by id desc and apliying a limit??)

8

Yes that’s right - ORDER BY id desc LIMIT 1

9

I did this:

In my model:


'app_status'=>array(self::HAS_MANY, 'Audit', '', 'on'=>'Application.application_id = Audit.app_id AND Application.status_id = Audit.action_id', 'order'=>'Audit.id desc', 'alias'=>'Audit'),

In my view:


<?php echo CHtml::encode($model->app_status[0]->action_details); ?>

It works at the moment, what do you think?

10

I think is ok…

But $model->app_status still contains all the rows in the array, not just the las one…

if you don’t need the others, then you can declare your relation this way (not tested, but i think it works)




'app_status'=>array(self::HAS_MANY, 'Audit', '', 'on'=>'Application.application_id = Audit.app_id AND Application.status_id = Audit.action_id', 'order'=>'Audit.id desc','limit'=>1, 'alias'=>'Audit'),

altought, to acces the action_details proeperty you must use the same as before (your code without changes):




<?php echo CHtml::encode($model->app_status[0]->action_details); ?>
11

Yeh I already tried the ‘limit’ parameter but it does not seem to have any effect! Still returns all rows…

12

Ah! Ok… is good to keep in mind! Thanks!

So, your last solution seems to be the best one!!!