Jonathon Posted March 8, 2013 Share Posted March 8, 2013 Hi, After reading the pars on widgets I need to use them more for easy pagination and sorting. So i'm rewriting some functionality. I currently have this code in my Area Controller. public function actionIndex($id) { //->with('areaItems')->find('t.id=:id', array(':id'=>$id)) $dataProvider=new CActiveDataProvider('Area', array( 'criteria' => array( 'with' =>'areaItems', 'condition' => 't.id=:area_id', 'params' => array(':area_id'=>$id), ) )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); What this does is take the url say localhost/area/2/birmingham and gets all the items from the Items table that have the area_id (FK) 2. I've printed the returned object and relations and I'm getting the right information. In my index.php view file I have the default code <?php $this->widget('zii.widgets.CListView', array( 'dataProvider'=>$dataProvider, 'itemView'=>'_view', 'sortableAttributes'=>array('date_added') )); ?> "date_added" was me just playing with the sortable aspect of the widget. However I have a small problem, I'm only able to use the column names of the primary model it seems, in this case Area(). What i'd like to be able to do is use column names from the related model such as "price". How would I do this? Have I got things in the wrong model (Again)? Additionally my index.php was made up of this loop // There are items, iterate through model foreach($model['areaItems'] as $row):?> // do stuff to present data I took this out of the index.php view and put it within the _view.php. As I felt that was the right place for it? Thanks Link to comment Share on other sites More sharing options...
Jonathon Posted March 12, 2013 Author Share Posted March 12, 2013 Ok, i've made some progress here. public function actionIndex($id) { //->with('areaItems')->find('t.id=:id', array(':id'=>$id)) $dataProvider=new CActiveDataProvider('Area', array( 'criteria' => array( 'with' =>'areaItems', 'condition' => 't.id=:area_id', 'params' => array(':area_id'=>$id), ), 'sort'=>array( 'attributes'=>array( 'title'=>array( 'asc'=>'title', 'desc'=>'title DESC' ), 'price'=>array( 'asc'=>'price', 'desc'=>'price DESC' ) ) ) )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); That now makes the links on teh clistView display as links. Which is a good step. My problem now is that when I try to sort these results I always get a CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'price' in 'order clause'. I've tried referencing the related table by relation ("AreaItems.price") and also by table name ("item.price"). But all methods seem to fail. Link to comment Share on other sites More sharing options...
Larry Posted March 13, 2013 Share Posted March 13, 2013 How are your relations defined? Link to comment Share on other sites More sharing options...
Jonathon Posted March 13, 2013 Author Share Posted March 13, 2013 Hi Larry Area Model 'areaItems' => array(self::HAS_MANY, 'Item', 'area_id'), Item Model 'Area' => array(self::BELONGS_TO, 'Area', 'area_id'), Link to comment Share on other sites More sharing options...
Larry Posted March 18, 2013 Share Posted March 18, 2013 Hey Jonathon, I was off for the weekend. Are you still having issues with this? Link to comment Share on other sites More sharing options...
Jonathon Posted March 18, 2013 Author Share Posted March 18, 2013 Hi Larry, I think you're allowed the weekend off! :-) But yes I am. Thanks Jonathon Link to comment Share on other sites More sharing options...
Larry Posted March 20, 2013 Share Posted March 20, 2013 I'm winging this a bit, but what if you change the attributes (in your data provider) to areaItems.price? Link to comment Share on other sites More sharing options...
Jonathon Posted March 21, 2013 Author Share Posted March 21, 2013 Yeah, I tried that and also just the table directly - no luck. Could it be that I need to reference the "Item" table first at this line?? $dataProvider=new CActiveDataProvider('Area', array( /* rest of code */ Even though I'm using an "Area" view + controller? Link to comment Share on other sites More sharing options...
Jonathon Posted March 21, 2013 Author Share Posted March 21, 2013 I actually rewrote the code and it looks like this. This code works fine, displays the correct number of records (It didn't before it just showed 1 record of 1, ven though it returned more than 1 record) and is sortable. Controller $dataProvider=new CActiveDataProvider('Item', array( 'criteria' => array( 'with' =>'Area', 'condition' => 't.area_id=:id', // primary Item table `area_id` = id passed in url 'params' => array(':id'=>$id) ), 'sort'=>array( 'attributes'=>array( 'title'=>array( 'asc'=>'title', 'desc'=>'title DESC' ), 'price'=>array( 'asc'=>'price', 'desc'=>'price DESC' ) ) ), 'pagination'=>array( 'pageSize'=>5, ) )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); In the _view I just accessed the data like so $data->title; // etc This is an Area controller and view however. Not sure if that matters?? I have 1 final thing to tidy this up In the index.php view I just want to set the <h1> I am having a little problem doing that I am using the $dataProvider object that is passed to index view. But I can't seem to be able to access the relationName or related models directly like $dataProvider->Area->name so I can print the <h1> tag. Should I just add another $model when i render the view?? I feel like I shouldn't need to do this to access the information, but maybe i'm wrong. Hopefully that progress will be ok in Yii terms and helpful to others. Link to comment Share on other sites More sharing options...
Larry Posted March 22, 2013 Share Posted March 22, 2013 Sounds like you're making great progress and learning how to figure things out. To set the , you should be able to refer to $dataProvider[0]->Area->name, although that's just off the top of my head and untested. But the goal is to access the first item in the $dataProvider. Link to comment Share on other sites More sharing options...
Jonathon Posted March 23, 2013 Author Share Posted March 23, 2013 Thanks for the nice words Larry and the help that you and Antonio have helped me with recently. I appreciate it. As for the answer to this piece. You can't access the $dataProvider as an array like that, but I have figured out how to do it. $dataProvider->Data[0]['Area']['name'] If an area has no items then this causes an index error as the array doesn't exist. So I just test for it's presence before loading the page and if it doesn't exist I show a "No records message" Jonathon Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 25, 2013 Share Posted March 25, 2013 My suggestion is to leave that bit of logic in the controller. I pass my data to static helpers for sorting/filtering and other such tasks in the controller. I also set my titles there. I'm currently building in CodeIgniter, so I don't have the nice structure you guys have. However, in such special cases, placing logic in the controller makes sense. Doing a copy of the zeroth item and assigning that appropriately makes a lot of sense to me. I've also put a little bit of logic in my header views. Placing something like this is not shameful at all: <title>Sitename <?= isset($title) ? '-'.$title : ""; ?></title> This will obviously look a bit different for you guys, but you get my point. Link to comment Share on other sites More sharing options...
Jonathon Posted March 25, 2013 Author Share Posted March 25, 2013 Hi Thomas, Yes I have just moved my logic to the controller. The logic now checks that the $dataProvider isn't empty adn depending on that it passes other models or errors to the view thanks Link to comment Share on other sites More sharing options...
Jonathon Posted April 9, 2013 Author Share Posted April 9, 2013 This question is somewhat related to the start of this thread. In my Area/index I am listing all the items that are available from a particular Area by using the Area PK to search the Item table. I have quite a lot of related information. I've recently started using Giix and it has brilliant views (in general) that it will list all the data from a table and it's related table. How would I be able to pass this model to the CListView widget instead of the $dataProvider. I can do this with a pure sql statement that was rather complex, but then I thought there must be a better way than this. My area of confusion here is that the model in the nice view is the item Model (Not Area Model) and is loaded through the actionView() method and loadModel function. Which takes an Item($id) and loads that data and related tables info. But for the CListView widget I am just using Area id to find related items that have the same area_id. Therefore missing out on the nicely preloaded related model values. To refresh this is the current Area code $dataProvider=new CActiveDataProvider('Item', array( 'criteria' => array( 'with' =>'Area', 'condition' => 't.area_id=:id', // primary Item table `area_id` = id passed in url 'params' => array(':id'=>$id) ), 'sort'=>array( 'attributes'=>array( 'title'=>array( 'asc'=>'title', 'desc'=>'title DESC' ), 'price'=>array( 'asc'=>'price', 'desc'=>'price DESC' ) ) ), 'pagination'=>array( 'pageSize'=>5, ) )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); This only way I can think of currently, is calling the Area id and using it find all the Item records that have that area_id, then using each record's PK (id) and then prefroming the loadModel() function to get all the related information and return it as one huge multidimensional array or array of objects. But this seems like it would complicated, messy and beyond me. Although it is 1.15AM so I might just be tired. Is there an easier way or should I just use my sql query to call it all? Thanks Jonathon Link to comment Share on other sites More sharing options...
Larry Posted April 12, 2013 Share Posted April 12, 2013 I'm sorry, but I'm not following you. Getting a bit lost in the logic. Could you simplify/rephrase what's going on here? Link to comment Share on other sites More sharing options...
Jonathon Posted April 13, 2013 Author Share Posted April 13, 2013 (edited) Sorry Larry, Basically the actionView() for my Item Controller has a lovely view using the CDetailView widget. It shows all the related table info using Giix. As you know. I need to show this data using a CListView widget as it's multiple records not a CDetailView one. It's meant to list all the Items for one particular area. I basically wasn't sure how to do that. or if what i'm trying to is possible. I could make an SQL query that loads it all, it is fairly long and has 4 or 5 joins. Which I don't mind doing. But I wondered if there was a better way within Yii using relations etc. That is my question I guess. My logic of how it fits together is. Which please discard if it confuses you was: I go to the example.com/area/areaID/area-name - Yii uses the areaID from url to start the query to find all Items that are from this area. - Yii returns all the Item id numbers - I then use the loadModel() method to load each record into an array - Pass this array to the page that lists all the items from an area Does that make sense? Sorry for the confusion Please ignore me for the moment. I think i've sorted this. It was far simpler, I don't think I used the relations properly. Apologies! I just need to tidy up some of the lazy loading which is what I'd initially expect with so many relations. Edited April 13, 2013 by Jonathon Link to comment Share on other sites More sharing options...
Larry Posted April 16, 2013 Share Posted April 16, 2013 No need to apologize. Glad to hear you've sorted it out. Unfortunately, it's not always clear whether an issue is simple and you're just overcomplicating it, or it's actually complicated! Link to comment Share on other sites More sharing options...
Jonathon Posted April 16, 2013 Author Share Posted April 16, 2013 I think the former generally applies to me. I enjoy how easy the model relations are with Yii to use (For the most part, sometimes they can be a little complex). I'd like to cut down on the lazy loading of aspects of a query with lots of joins. For the moment It will do though. Yii is still very quick I find! Link to comment Share on other sites More sharing options...
Recommended Posts