Putting A Nested Mysql Report Into The Correct Yii Context

[giles]

Hi all.

 

So I'm a Yii learner. Just completed YiiBook which I really enjoyed. I'm now trying to put its principles into practise, however i'm still not fully getting the  "mindset" so I need to turn to the experts for help. Goes like this

 

// assign	

$id=$model->id;


// get topic

$topic = Yii::app()->db->createCommand()

->select('id, parent_id, name, description, url')

->from('silo')

->where('silo.id = '.$id.'')

->queryAll();

?>


<img class="title" src="<?php echo $topic[0]['url']; ?>" width="300" height="200" /> 

<div id="headline"><strong><?php echo $topic[0]['name']?></strong><em> Magazine</em></div>


<?php

	// SUB TOPICS

	$subtopics = Yii::app()->db->createCommand()

	->select('id, parent_id, name, description')

	->from('silo')

	->where('silo.parent_id = '.$id.'')

	->queryAll();


	// read out

	for ($x = 0 ; $x < count($subtopics) ; $x++) 

	{	

		// query

		$entries = Yii::app()->db->createCommand()

		->select('*')

		->from('entrysilo')

		->Join('entry', 'entrysilo.entry_id = entry.id')

		->where('entrysilo.silo_id = '.$subtopics[$x]['id'].'')

		->queryAll();


		// title

		print "<fieldset>";

		print "<h3>" . $subtopics[$x]['name'] . "</h3>";

		print '<div id="h3description">' . $subtopics[$x]['description'] . '</div>';


		// read entries out

		for ($y = 0 ; $y < count($entries) ; $y++)

		{

			print '<a href="http://domain/index.php?r=entry/view&id='.$entries[$y]['id'].'">' . $entries[$y]['title'] . '</a>';

			print '<br/>';

			print '<div id="description">' . $entries[$y]['entry_description'] . '</div>';

		}

		print "</fieldset>";

	}

 

I have a report that runs a number of individual and nested MySQL queries before producing a newspaper type report. In my ignorance I first built this into a view utilising Query Builder. This works OK, but as Larry points out on page 20, this should really go into the model. trouble is that I can't find any examples of how I should go about building a nested query in the model, then reading it out to a layout.

 

I'd really appreciate your advise

thanks in advance

[Edward]

You need to get your SQL queries into model methods(functions), then call these from the controller then pass the data obtained to your views file from the controller.

 

May be i will have some examples in my project, ill take a look now.

[giles]

Yes, that was the first bit putting me off, was the fact that I hadn't seen anything like that in eamples or tutorials so didn't know if I was flying against convention.

 

thanks in advance

[Edward]

This stuff is definitely covered in Larry's Yii book, you really need to get more into it. I could give some examples but there are so many variations of doing something. I also need to correct some of my own code as later on i found better ways of doing things, Yii takes time and experience to learn, the best way is to get your hands dirty. Don't worry too much about being perfect right now, just have a play around and things will fall in to place. Yeah my best advise for you is to work through the Yii Book, i think the Yii Definitive guide is also good it can make you see things from another angle, which on some occasions can help. If i was to explain stuff to you here it might not make a lot of sense right now until you have a firm grasp of the basics of Yii and its Model View Controller infrastructure.