2016/02/25

Laravel order random faster solution

source: MySQL select 10 random rows from 600K rows fast

using Eloquent's Query Scope

add method to model
public function scopeRandom($query){
 //source: http://stackoverflow.com/a/4329447

 $table = $this->getTable();
 $key = $this->getKeyName();

 $subQuery = sprintf(
     "(SELECT CEIL(RAND() * (SELECT MAX(`%s`) FROM `%s`)) AS id) as random", 
     $key, 
     $table
    );

 return $query->join(DB::raw($subQuery), function($join) use($table, $key){
  $join->on( $table.'.'.$key, '>=', 'random.'.$key );
 });
}

Using:
Post::random()->where(...)->get();

沒有留言:

張貼留言