[thyseus]

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'));?>

[zerone]

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);

   ..

      }

  }

 ?>

[jonah]

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

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

[eval]

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.

[thyseus]

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>';

      }

  }

?>

[jonah]

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

[Lucas]

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

eval, on 25 June 2009 - 04:17 PM, said:

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

<pre class='prettyprint'>
$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);</pre>

Which is a signle sql query.

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

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?

[thyseus]

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 ??

[jonah]

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

[pestaa]

jonah,

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

[Fire]

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

[DC Chen]

it is also a easy solution.
http://blog.anyurl.d...rder+by+random+

[Deepak Pradhan]

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);