How To Retrieve The Related Data From The Same Table

Hi.

I have two tables PROGRAM and PROJECTS. Each project belongs to a specific program. And on the program detail page I have displayed the related projects.

My question is that, how can I display the related projects on the project detail page ?

Thanks in advance.

You should use ActiveRecord relations for this, works perfect.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship

Just declare the relationships.




    public function relations()

    {

        return array(

            'project'=>array(self::HAS_MANY, 'Project', 'program_id'),

        );

    }




And then you can access them like:




$program = Program::model()->findAll();

foreach($program->project as $project)

{

  echo $project->name;

}



Just a simple example, you could use CGridview or CListView for showing the projects.

I have already tried your method but not working. Here is my code. If you understand it then please sort out it.

[model/Programs.php]


public function relations() {

        return array(

            'activities' => array(self::HAS_MANY, 'Activities', 'program_id'),

            'projects' => array(self::HAS_MANY, 'Projects', 'program_id'),            

        );

    }

[model/Projects.php]


public function relations() {

        return array(

            'program' => array(self::BELONGS_TO, 'Programs', 'program_id'),

        );

    }

[and the view is. but here all projects are displaying. while I want to display only related projects]


<div  id="flexslider-latest-posts-133" class="flexslider widget_recent_posts_2-container clearfix">

    <ul class="slides">

        <?php

        $criteria = new CDbCriteria;

        $criteria->addCondition('t.status = :status');

        $criteria->params = array(':status' => Post::STATUS_PUBLISHED);

        $criteria->limit = '10';

        $criteria->order = 'create_time DESC';




        $model = Projects::model()->findAll($criteria);


        foreach ($model as $data):

            ?>


            <li>

                <div class="item">

                    <div class="item-con-t1">

                        <div class="container-t1">

                            <div class="container-t1-margin">


                                <footer class="visible-on-hover">

                                    <div class="bg-black-045"></div>

                                    <div class="widget_recent_posts_2-fac">

                                        <div class="date">

                                            <span><?php echo XHtml::formatDate($data->create_time, 'd M Y'); ?></span>

                                        </div>

                                    </div>

                                </footer>                             

                                <div class="facilities visible-on-hover">

                                    <div class="bg-black-020"></div>

                                    <div class="image-links">

                                        <a rel="" title="<?php echo $data->title; ?>" href="<?php echo $data->url; ?>"><span class="add"></span></a>

                                    </div>

                                </div>


                            </div>

                        </div>


                    </div>

                    <div class="item-body">

                        <header>

                            <h1><?php echo CHtml::link(XHtml::truncate($data->title, 35, ''), $data->url); ?></h1>

                        </header>

                    </div>

                </div>

            </li>

            <?php

        endforeach;

        ?>


    </ul>

</div>

Seems logic, in your view you are selecting all your projects who has the status published.

You’re relations seems to be OK.

I think this is what you want:


<div  id="flexslider-latest-posts-133" class="flexslider widget_recent_posts_2-container clearfix">

    <ul class="slides">

        <?php


        // I think this line should be in your controller btw.

        $model = Programs::model()->findByPk($program_id);


        foreach ($model as $data):

            ?>


            <li>

                <div class="item">

                    <div class="item-con-t1">

                        <div class="container-t1">

                            <div class="container-t1-margin">


                                <footer class="visible-on-hover">

                                    <div class="bg-black-045"></div>

                                    <div class="widget_recent_posts_2-fac">

                                        <div class="date">

                                            <span><?php echo XHtml::formatDate($data->projects->create_time, 'd M Y'); ?></span>

                                        </div>

                                    </div>

                                </footer>                             

                                <div class="facilities visible-on-hover">

                                    <div class="bg-black-020"></div>

                                    <div class="image-links">

                                        <a rel="" title="<?php echo $data->projects->title; ?>" href="<?php echo $data->projects->url; ?>"><span class="add"></span></a>

                                    </div>

                                </div>


                            </div>

                        </div>


                    </div>

                    <div class="item-body">

                        <header>

                            <h1><?php echo CHtml::link(XHtml::truncate($data->projects->title, 35, ''), $data->projects->url); ?></h1>

                        </header>

                    </div>

                </div>

            </li>

            <?php

        endforeach;

        ?>


    </ul>

</div>

Well, I was reading your question again, and I think I just misunderstood it. If so, sorry about that :rolleyes:.

My question is that, how can I display the related projects on the project detail page?

If I understand it correctly, you just want to show the projects that have the same program_id as the selected project?

Then it could be as simple as:

In the controller:




	// View project.

	public function actionView($id)

	{

		$model = $this->loadModel($id);	


		$this->render('view',array(

			'model'=>$model,

		));

	}



In your View:




        $criteria = new CDbCriteria;

        $criteria->addCondition('t.status = :status');

        $criteria->addCondition('t.program_id = :program_id');

        $criteria->params = array('program_id'=>$model->program_id, ':status' => Post::STATUS_PUBLISHED);

        $criteria->limit = '10';

        $criteria->order = 'create_time DESC';




        $relatedProjects = Projects::model()->findAll($criteria);



Yes. its working. thanks a lot dear.