Jonathon Posted January 24, 2013 Share Posted January 24, 2013 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 comment Share on other sites More sharing options...
Jonathon Posted January 24, 2013 Author Share Posted January 24, 2013 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 comment Share on other sites More sharing options...
Edward Posted January 25, 2013 Share Posted January 25, 2013 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 comment Share on other sites More sharing options...
Jonathon Posted January 25, 2013 Author Share Posted January 25, 2013 I couldn't really even fathom how to create the required the SQL for AR. QB felt more convenient to me. I feel like I should move the code in to the model though. Link to comment Share on other sites More sharing options...
Jonathon Posted January 25, 2013 Author Share Posted January 25, 2013 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 comment Share on other sites More sharing options...
Edward Posted January 25, 2013 Share Posted January 25, 2013 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 comment Share on other sites More sharing options...
Larry Posted January 28, 2013 Share Posted January 28, 2013 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 comment Share on other sites More sharing options...
Jonathon Posted January 28, 2013 Author Share Posted January 28, 2013 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 comment Share on other sites More sharing options...
Recommended Posts