Jump to content
Larry Ullman's Book Forums

Best Method For Converting This To Yii Query


Recommended Posts

Hi Larry,

 

I'm going to use the following query



select c.name, count(i.category_id)
from category as c left outer join
item as i
on c.id = i.category_id
group by c.name
order by c.name ASC

 

What would be you preferred method for interacting with the database here?

 

I wrote this:

 


$cmd = Yii::app()->db->createCommand();
$cmd->select(array('c.name, COUNT(i.category_id) as numItems'));
$cmd->from('category as c');
$cmd->leftJoin('item as i', 'c.id=i.category_id');
$cmd->group('c.name');
$cmd->order('c.name', 'ASC');
$result = $cmd->query();

 

This is for use on my index page.

 

Is it correct to place for this code within my SiteController.php???

 

public function actionIndex()
{

$this->layout = '//layouts/home'; // uses the home layout for index page


$cmd = Yii::app()->db->createCommand();
$cmd->select(array('c.name, COUNT(i.category_id) as numItems'));
$cmd->from('category as c');
$cmd->leftJoin('item as i', 'c.id=i.category_id');
$cmd->group('c.name');
$cmd->order('c.name', 'ASC');

$model = $cmd->query();

// render index view
$this->render('index',array(
'model'=>$model
));

 

I wasn't sure how to pass the object to the view. I got the impression from the book that it doesn't return usable objects. I used

print_r($model);

 

in my index.php view and it returned a CDbDataReader Object which I expected. But not much else. I know when I made a really simple AR query and passed the model off to the view it returned a lot more info. So i'm questioning a few things.

 

1) Am I using the wrong DB method. (This one seemed easier for me to comprehend when constructing an aggregated SQL query).

Perhaps I should just use DAO as none of the information is generated from user information.

 

2) Am I placing the code for the SQL in the right place (SiteController.php)

 

3) Am I passing the information to the view correctly.

Link to post
Share on other sites

Ok, I've made some progress with this. But wanted to know if I was going about things the right way. My controller code hasn't changed. Just in my view I have

 


<?php foreach($model as $row){

echo '<li><a>' . $row['name'] . '</a><span class="muted"> ' . $row['numItems'] . '</span></li>';
}
?>

 

Seems to output what I wanted. But the main thing for me is that things are going in the right places and the better practices are being used. If someone can just let me know that would be great. :)

Link to post
Share on other sites

Some of your question above also depends on what the tables you have on your site have in relation to one another. If you have related tables you can use the relational active record, this will save you having to do the joining yourself. I know joins are really fun but if you work with Yii you can get the stuff done faster, the only downfall with Active record is that the queries are slower. I think working with the active record on these join statements would be the best way for now then when your site gets busy you can work on performance tuning later on and make your own raw sql statements etc.

 

If you wanted your controller to be better managed you could move that sql code into a model method and just have it return the results the other thing would be to put a class into the components folder that would contain related methods.

 

I have done a lot of AR and RAR stuff now, but the bitch is when you are doing the Relational join with a has many relationship you get an array of objects, which for me now is a pain to access.

Link to post
Share on other sites

So if I am going to move the SQL to a model. Which model as it's for the index page? I'm not sure then how to pass it along to the site/index controller/view.

 

Inside SomeModel.php

 

public function returnSomeResults()
{

$cmd = Yii::app()->db->createCommand();
$cmd->select(array('c.name, COUNT(i.category_id) as numItems'));
$cmd->from('category as c');
$cmd->leftJoin('item as i', 'c.id=i.category_id');
$cmd->group('c.name');
$cmd->order('c.name', 'ASC');
$result = $cmd->query();

return $model;
}

 

Then inside Controller.php

 

how would you then go about passing the model to view?


public function actionIndex()
{


$model = SomeModel::model()->returnSomeResults();

$this->render('index',array(
'model'=>$model,
));
}

 

Like that?

 

That seems to give me a workable $model in my view that I can use a foreach lop to print data.

Link to post
Share on other sites

If that is for drawing out a list of categories for index put it into the category model. Also how you pass the data to the view file is fine. You could also put the method name in the array in the render method. Personally i think it would be better just calling the model method from views because you do the same thing with dropdownlist.

Link to post
Share on other sites

Not sure where you stand on this at the moment, but just to say: if I had a relatively complicated query that was relatively static, I would use DAO to run it. And if it's used for the home page, you would run it from the SiteController, yes.

Link to post
Share on other sites

Thanks Larry + Edward

 

I ended up moving the code out of SiteController and into the Category model for better separation of code. I would much prefer to use DAO on a whole because I like being able to use SQL. It's a bit harder to use AR with more complicated queries i find, but i suppose that's to be expected with anything new. But the added security and a couple of other things are nice the other methods provide.

 

Thanks for your help

 

Jonathon 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...