使用数据库构建器或雄辩在Laravel中编写复杂的SQL

Question

如何使用数据库构建器或雄辩的Laravel编写此SQL？

SELECT * FROM product WHERE product.CategoryID IN
 (SELECT CategoryID FROM (SELECT CategoryID FROM category where GroupCategoryID="12" AND category.IsActive="1" ORDER BY RAND() LIMIT 10) t)
 AND product.IsActive="1" ORDER BY RAND() LIMIT 3;

update

有3个表：产品，类别，group_category我想通过GroupCategoryId

获得3个随机产品

表产品包含类别ID，类别表包含groupCategoryId。

SQL对我有用...但是我想在Laravel中重写我的项目，我想使用现代方法

更新2

...这似乎有效...但是它不是那么优雅：(

$result = DB::select('SELECT CategoryID FROM category where GroupCategoryID="4" AND category.IsActive="1"');
    $categories = array();
    foreach ($result as $r){
        array_push($categories, $r->CategoryID);
    }
    $randProducts = DB::table('product')
        ->whereIn('CategoryID', $categories)
        ->inRandomOrder()
        ->limit(3)
        ->get();

Answer 1

SQL的优雅方式：

$categoryIDs = Category::where('IsActive', 1)
    ->where('GroupCategoryID', 12)
    ->orderBy(DB::raw('rand()'))
    ->take(10)
    ->select('CategoryID')
    ->get()
    ->pluck('CategoryID')
    ->toArray();
$products = Product::whereIn('CategoryID', $categoryIDs)
    ->where('IsActive', 1)
    ->orderBy(DB::raw('rand()'))
    ->take(3)
    ->get();