在mySQL中查询,从每个'purpose'11中随机选择44个问题PHP/Laravel

$questions = collect();
$questions = $questions->merge(Question::where('purpose', 'purpose-1')->take(11)->inRandomOrder()->get());
$questions = $questions->merge(Question::where('purpose', 'purpose-2')->take(11)->inRandomOrder()->get());
$questions = $questions->merge(Question::where('purpose', 'purpose-3')->take(11)->inRandomOrder()->get());
$questions = $questions->merge(Question::where('purpose', 'purpose-4')->take(11)->inRandomOrder()->get());
$questions = $questions->shuffle();

我想到了三种方法:

$fourPurposes =Question::select('purpose')->get()->shuffle()->flatten()->unique();

$unions = Question::query();
for(%i=0;$i<4;$i++){
  $query = Question::where('prupose', $fourPurposes[$i])->limit(11);
  $unions = $unions->union($query);
}
$questions = $unions->get();

2-这很复杂,但是如果你有非常大的表和目的列索引,我认为这将是最好的。在这个方法中,您可以将问题表与另一个问题表连接起来。一方面,你限制了目标,另一方面,你限制了问题的数量。查询应该是这样的(我真的不确定,我希望我们可以编辑它使它更好)

$table1 = DB::table('questions')->groupBy('purpose')->limit(4);
$questions = DB::table('questions as q1)
->join(Db::raw('
  (select * from questions as q2 limit 11)
'), function($join){ 
      $join->on('q1.purpose','=','q2.purpose');
   })->limit(44)->get();

$fourPurposes =Question::select('purpose')->get()->shuffle()->flatten()->unique();

$questions =  new Collection();

for(%i=0;$i<4;$i++){
  $query = Question::where('prupose', $fourPurposes[$i])->limit(11)->get();
  $questions = $questions->union($query);
}