Random Banner Portlet

Hello everyone,

i just added a small Portlet to the Blog demo. It display one of many Images & a link randomly at each Page Request.

At first, we create the DB-Table:



CREATE TABLE IF NOT EXISTS `Banner` (


  `id` int(11) NOT NULL auto_increment,


  `image` varchar(80) NOT NULL,


  `link` varchar(80) NOT NULL,


  `comment` varchar(255) NOT NULL,


  PRIMARY KEY  (`id`)


) DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;





after that, we run the model and the crud Actions in the CLI to generate our needed classes.

After that, we create the components/BannerPortlet.php :



<?php





class BannerPortlet extends Portlet {


        


    public function renderContent() {


      $bannerList = Banner::model()->findAll();


      $bannerCount = Banner::model()->count();


      $randomnumber = (rand()%$bannerCount);


      echo '<a target="_blank" href="' . $bannerList[$randomnumber]->link . '">' . $bannerList[$randomnumber]->comment . '<br><img src="' . $bannerList[$randomnumber]->image . '"></img></a>';


      }


  }


 ?>


Now, we can use our Portlet like this:



  <?php $this->widget('BannerPortlet', array('title'=>'Advertise'));?>


That's inefficient. You should make the random before retrieving the rows. And then you only get one row.

<?php





class BannerPortlet extends Portlet {


        


    public function renderContent() {


      $bannerCount = Banner::model()->count();


      $randomNumber = (rand()%$bannerCount);


      $bannerList = Banner::model()->find($randomNumber);


   ..


      }


  }


 ?>

zerone, that won't work either, you may not have consecutive IDs.

http://akinas.com/pa…sql_random_row/

I've searched around a week ago for something similar and what i've ended up is:



$sql = "SELECT * FROM " . tnt::model()->tableName()  . " T JOIN ( SELECT FLOOR( MAX( tnt_id ) * RAND() ) AS Rand_ID FROM " . tnt::model()->tableName()  . " ) AS x ON T.tnt_id >= x.Rand_ID LIMIT 1;";





$randomTNT = tnt::model()->findBySql($sql);

Which is a signle sql query.

But speaking about performance on these different methods … i think it only matters on tables > 50K records.

I used this Solution, since i are not a fan of moving programming logic to the database:



<?php


class BannerPortlet extends Portlet {        


    public function renderContent() {


      $bannerCount = Banner::model()->count();


      $randomnumber = (rand()%$bannerCount + 1); // 0 shouldn't be possible


      $banner = Banner::model()->findByPk($randomnumber);


      echo '<a target="_blank" href="' . $banner->link . '">' . $banner->comment . '<br><img src="' . $banner->image . '"></img></a>';


      }


  }


?>


It should be noted that that only works if the IDs are consecutive in the database, with no gaps.

Sorry for reviving an old thread, but since it’s exactly the same topic I thought it wouldn’t be a problem.

The correct and faster SQL for doing that would be "SELECT * FROM table ORDER BY RAND() LIMIT 1;"

I guess there’s no builtin ActiveRecord method that does this then? Perhaps a findRandom($count=1) method could be added to CActiveRecord?

Haha, i can’t believe it…

"SELECT * FROM table ORDER BY RAND() LIMIT 1;"

Just so simple! And it just works. And it isn’ t really moving the programming logic to the database - because there is no programming :)

I don’t think that this feature needs to be implemented in ActiveRecord. It is a very special use case. Someone could note this possibility in one of the Tutorials or the Manual, that’s enough.

Or just add something like $model = $model::getRandom(); or $myfiverandomobjects = $model::getRandom($count = 5); ? Maybe that’s useful?

Here is an (very primitive, but working) implementation:




 public function getRandom($count = 1) {

		if(!isset($count)) $count = 1;

		$tablename = $this->tableSchema->name;

		$query = "select * from " . $tablename . " order by RAND() limit " . $count;

		return $this->findBySql ($query);

        }



Just place it in CActiveRecord … any Optimization ideas? Does postgreSQL have the same syntax ??

Why not just find(array(‘order’=>new CDbExpression(‘RAND()’), ‘limit’=>1))

jonah,

‘limit’=>1 can be omitted as well, find() always overrides limit with 1.

Anyone have a banner ad system for yii which can track impressions?

it is also a easy solution.

http://blog.anyurl.de/index.php/post/17/find+data+by+yii+framework+with+the+order+by+random+

Glad I found this old thread. Really helpful.

Since I needed to get random row(s) from TableA, TableB or TableC. Instead of writing methods for each, a common component seems to work fine.





class mRandomSelect extends CActiveRecord {


    public static function GetAR($modelName, $limit) {

        if ($limit == 1)

            $model = $modelName::model()->find(array(

                'order' => new CDbExpression('RAND()')));

        else

            $model = $modelName::model()->findAll(array(

                'order' => new CDbExpression('RAND()'),

                'limit' => $limit));


        return ($model) ? $model : false;

    }


}



Usage: $model = mRandomSelect::GetAR($modelName=‘TableB’, $limit=10);