thyseus
(Thyseus)
June 25, 2009, 12:51am
1
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'));?>
system
(system)
June 25, 2009, 12:58am
2
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
(Poppitypop)
June 25, 2009, 2:39am
3
zerone, that won't work either, you may not have consecutive IDs.
http://akinas.com/pa…sql_random_row/
eval
(Zamanfoo)
June 25, 2009, 9:17pm
4
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
(Thyseus)
June 26, 2009, 12:23pm
5
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
(Poppitypop)
June 26, 2009, 3:02pm
6
It should be noted that that only works if the IDs are consecutive in the database, with no gaps.
Lucas
(Lucas Aerbeydt)
August 6, 2009, 12:53pm
7
Sorry for reviving an old thread, but since it’s exactly the same topic I thought it wouldn’t be a problem.
eval:
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
(Thyseus)
September 17, 2009, 9:43pm
8
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
(Poppitypop)
September 18, 2009, 4:53pm
9
Why not just find(array(‘order’=>new CDbExpression(‘RAND()’), ‘limit’=>1))
pestaa
(Pestaa)
September 20, 2009, 5:57pm
10
jonah,
‘limit’=>1 can be omitted as well, find() always overrides limit with 1.
fire
(Fire)
November 18, 2010, 11:02am
11
Anyone have a banner ad system for yii which can track impressions?
chendchng
(Chendacheng)
July 1, 2011, 6:56pm
12
foe_1
(foe#1)
September 30, 2012, 8:10pm
13
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);