Basics of making a component

Archives Yii 1.0.x Discussions
1

I've been looking through the documentation for a pretty long time now, and am finding myself a bit confused. This is the first MVC I've worked with: I understand the concept, but I'm having a difficult time sorting out what should go into what.

For example, I'm trying to migrate an earlier application to Yii; one thing it did was query the Google Geocoding service with an address, return the lat/lng, and then I would query my database based on the lat/lng. I have the gMaps extension running, which uses the same service.

So what I'm asking: could anybody walk me through how I would sort this out? Would I make a component that would retrieve the lat/lng and include it in a controller? Would I have all the data operations take place in a model? I'm a bit lost… if someone can help me get past the first hurdle, I think the framework will be pretty clear to me.

Thanks in advance  :)

2

This sounds to me like a helper. You could write a class like the following:



class Geocoding


{


     public static function locate($address)


     {


           // ...call Google API


           // may query DB here, depending on your spec.


     }


}

Save this class in Geocoding.php  under protected/components

Then you should be able to call the method anywhere using Geocoding::locate().

3

Hey, thanks Qiang.

One follow up question: how would I use javascript/AJAX with this? Just using the script tags, or is there a resource anywhere explaining this? (I was having some trouble finding it).

If anybody's interested, I can post the finished code - somebody else must be wanting to geocode, right?

4

Take a look in the guide:

Quote

assetManager: CAssetManager - manages the publishing of private asset fles.
5

Thanks for the tip - didn't realize that was an asset.

6

One more question: in my component, I'm dealing with a relatively complex SQL query (below):

SELECT info.id, info.name, info.city, info.state, info.snapreview, info.price, vll.id, vll.vs1, vll.vs2, vll.vs3, vll.vs4, ( 3959 * acos( cos( radians('$this->latitude') ) * cos( radians( vll.lat ) ) * cos( radians( vll.lng ) - radians('$this->longitude') ) + sin( radians('$this->latitude') ) * sin( radians( vll.lat ) ) ) ) 


AS distance FROM vll, info 


WHERE info.id = vll.id AND (vll.vs1 = $this->venue OR vll.vs2 = $this->venue OR vll.vs3 = $this->venue OR vll.vs4 = $this->venue) 


HAVING distance <= $this->rnge ORDER BY distance LIMIT $this->lowerlimit, $this->upperlimit

Now, I read over the information about active record and Yii’s (awesome  :)) database functionalities, but here’s my question: would it be worth my time to split this query up for the functionality? I’m not sure if the query structure Yii has is for convenience and readability, or if it has some sort of magical (cache, sanitizer, whatever) properties too.

Thanks for the help! Soon enough I’ll be past the noob stage and helping you folks hopefully  ;)

7

The straightforward way is to run this query directly using:



$command=Yii::app()->db->createCommand($sql);


$command->bindValue(...);


$command->bindValue(...);


$rows=$command->queryAll();

We use bindvalue above because you have quite some parameters in the SQL. By using bindvalue, you can avoid SQL injection attacks.

Later when you model your database in terms of model classes, you can exploit AR's functionality and avoid writing these lengthy complex queries.

8

So optimally, I would want my controller calling on multiple models for that query?

9

That is until you can identify the model classes needed to represent your database (or at least in this query).

10

Quote

That is until you can identify the model classes needed to represent your database (or at least in this query).

Wow! Talk about a speedy response -  ;D - so right now, I have the query taking place in a component (called on by the controller); but it should be taking place in a model that can represent the database eventually? If these are standard MVC concepts now, I can just look around and answer them myself instead of taking your time - thanks for the help though.

11

If your query is too complex, using the current approach is fine since AR is not meant to solve all db problems. AR is helpful when dealing with relatively simple queries. And using AR is good because it naturally introduces the M layer in your application.

12

Quote

If your query is too complex, using the current approach is fine since AR is not meant to solve all db problems. AR is helpful when dealing with relatively simple queries. And using AR is good because it naturally introduces the M layer in your application.

Sounds good. Thanks for the help!

13

Things have been moving along quite well, until I realized that I needed to paginate the results of this query (modified from the one above):

 $sql = 


"SELECT id, name, city, state, smallsum, price, url, cat1, cat2, cat3, cat4, lat, lng, (3959*acos(cos(radians(:baselat)) * cos(radians(lat))*cos(radians(lng)-radians(:baselng))+sin(radians(:baselat))*sin(radians(lat)))) 


AS distance 


FROM info 


WHERE (cat1 = :venue OR cat2 = :venue OR cat3 = :venue OR cat4 = :venue) 


HAVING distance <= :rnge 


ORDER BY distance 


LIMIT :lower, :upper";

Any suggestions on how I can make this play nicely with AR? Most of it seems a bit more complex than the AR documentation mentions.  :-\ I found the other thread about pagination here, but the person who found a workound mentioned a performance hit (not something I want).

14

If I have understood well, one approach will be this:



//Models





class Info extends CActiveRecord {


    public static function model($className=__CLASS__) {


        return parent::model($className);


    }


    	/**


	 * @return string the associated database table name


	 */


    public function tableName() {


        return 'Info';


    }


}





//Controller


 public function actionGetInfo() {





	//get your post data from form here





        $criteria = new CDbCriteria;





        $criteria->condition = "Put where here conditions";


	//$criteria->having = ... //see CDbCriteria


	//etc








        $pages=new CPagination(Info::model()->count($criteria));


        $pages->pageSize=Yii::app()->params['postsPerPage']; //or $pages->pageSize= 10;


        $pages->applyLimit($criteria);





        $data=Info::model()->findAll($criteria);





        $this->layout = 'your layout';


        $this->render('your view', array('data'=>$data,'pages'=>$pages));


}
15

So I just parsed up my query to work nicely with the CDbCriteria. Unfortunately, I couldn't figure out how to implement the 'AS distance' part into it…

… then I found this, under "Performance Tuning" in the Yii documentation:

"For complex queries, it is recommended to create a database view for it instead of issuing the queries inside the PHP code and asking DBMS to parse them repetitively."

What is a database view, in context of Yii? I searched Google, but what I was finding didn't seem like what was meant in the above directive. I would hate to spend even more time trying to make this query play nicely with Yii's database tools, then find that it's not optimal.

I just haven't got a clue how Yii's paginator would work without AR, since those are the only examples I could find.

16

Your query is a simple one. ;)

Just do as what abajja suggested. For the "distance" field, you can declare such a public member in the Info class, then after find call, it will be populated with the number you want.

17

Quote

Your query is a simple one. ;)

Excellent  :o How would I parse the alias part into the CDbCriteria then? I can’t seem to figure it out. Here’s the last thing I tried:



$cdbcriteria = array(


        'select'=>'id, name, city, state, smallsum, price, url, cat1, cat2, cat3, cat4, lat, lng',


	'alias'=>'(3959*acos(cos(radians(:baselat)) * cos(radians(lat))*cos(radians(lng)-radians(:baselng))+sin(radians(:baselat))*sin(radians(lat)))) AS distance',


	'order'=>'distance',


	'condition'=>'cat1 = :venue OR cat2 = :venue OR cat3 = :venue OR cat4 = :venue',


	'having'=>'distance <= :rnge',


        'params'=>array(':baselat'=>$criteria['baselat'],


			      ':baselng'=>$criteria['baselng'],


			      ':venue'=>$criteria['venue'],


			      ':rnge'=>$criteria['rnge']


		              )


	                );

Which is to say, I’m the metaphorical fly in the coke bottle who’s bouncing off the sides trying to find a way out  :-\.

Quote

Just do as what abajja suggested. For the "distance" field, you can declare such a public member in the Info class, then after find call, it will be populated with the number you want.

The first problem I'm running into however is an error, politely informing me that it can't find a column 'distance' - probably, because I have no idea how to express the alias.

18

Try this (just an idea to get something working optimization after ;)):

Model



class Info extends CActiveRecord {


    public static function model($className=__CLASS__) {


        return parent::model($className);


    }





    public $distance;


       /**


    * @return string the associated database table name


    */


    public function tableName() {


        return 'info';


    }


}

Controller



class InfoController extends CController{


    public function actionTest() {





        $proximity = -60; //fictitious 


        $criteria = new CDbCriteria;





        $criteria->select = "name, lat, lng, (lat+lng) as distance";


        $criteria->condition = "lat+lng > $proximity"; //fictitious formula


        $criteria->order = "(lat+lng) DESC";





        $pages=new CPagination(Info::model()->count($criteria));


        $pages->pageSize=10; 


        $pages->applyLimit($criteria);





        $sql = "select $criteria->select from info"


                ." where $criteria->condition "


                ." order by $criteria->order"


                ." limit $criteria->limit"


                ." offset $criteria->offset"


                ;





        $data = Info::model()->findAllBySql($sql);


        


        $this->render('test', array('data'=>$data,'pages'=>$pages));


    }


}

View



<table>


<?php foreach($data as $row): ?>


    <tr>


        <td><?php echo '<td>'.$row['name']?></td>


        <td><?php printf ("%5.6f", $row['distance']);?></td>


    </tr>


<?php endforeach; ?>


</table>


<?php $this->widget('CLinkPager',array('pages'=>$pages)); ?>
19

I can't guarantee that it works completely correctly yet, but at least this code isn't giving me errors:

		$criteria = new CDbCriteria;


		


		$formula = '(3959*acos(cos(radians(:baselat))*cos(radians(lat))*cos(radians(lng)-radians(:baselng))+sin(radians(:baselat))*sin(radians(lat))))';


		


		$criteria->select = "id, name, city, state, smallsum, price, url, cat1, cat2, cat3, cat4, lat, lng, $formula as distance";


		$criteria->order = "distance DESC";


		$criteria->condition = "(cat1 = :venue OR cat2 = :venue OR cat3 = :venue OR cat4 = :venue)";


		$criteria->having = 'distance <= :rnge';


		


		$params = array(':baselat'=>$params['baselat'],


						':baselng'=>$params['baselng'],


						':venue'=>$params['venue'],


						':range'=>$params['range']


						);


		


		$sql = "SELECT $criteria->select FROM info"


                ." WHERE $criteria->condition "


                ." ORDER by $criteria->order";





		$pages=new CPagination(Info::model()->countBySql($sql,$params));


		$pages->pageSize = 10;


		$pages->applyLimit($criteria);


		


		$sql .= " LIMIT $criteria->limit OFFSET $criteria->offset";





        $venues = Info::model()->findAllBySql($sql,$params);

Apparently the problem was that my parameters weren’t getting assigned correctly, not the AS part. Figures  :-\ Thanks for the help!

20

I didn't get the whole thing, but a DB view is an SQL statement stored in the DB that display it's results as a table.

In this way you can use the results of a view like a normal DB Table.