php - 如何在 Laravel 中编写这个复杂的查询

Question

这是我的 SQL 查询

select sum(stock.total_in_stock) as total_in_stock,stock.name,stock.inventory_id FROM ( SELECT i.store_id,i.model_id,i. total_in_stock,i.id as inventory_id, m.* from `inventory` as `i` left join `model_store` as `ms` on `ms`.`store_id` = `i`.`store_id` left join `model` as `m` on `m`.`id` = `ms`.`model_id` where `i`.`model_id` = m.id and `m`.`status` = 1 and `ms`.`status` = 1 and `i`.`created_at` = (select max(si.created_at) from `inventory` as `si` where si.model_id = i.model_id AND si.store_id = i.store_id AND si.status=1 ORDER BY si.created_at DESC LIMIT 1 )) as stock group by `stock`.`model_id` 

基本上，我正在尝试检索特定商店在最近日期的库存。

我在 Laravel 中编写了这个查询，如下所示:-

$results = DB::table('inventory as i')
                      ->selectRaw( 'sum(stock.total_in_stock) as total_in_stock,stock.name,stock.inventory_id FROM ( SELECT i.store_id,i.model_id,i. total_in_stock,i.id as inventory_id, m.* ')               
                      ->leftJoin('model_store as ms','ms.store_id','=','i.store_id')
                      ->leftJoin('model as m','m.id','=','ms.model_id')
                      ->where('i.model_id','=', 'm.id')
                      ->where('m.status','=', 1)
                      ->where('ms.status','=', 1)
                      ->where('i.created_at','=',function($query){
                          $query->from('inventory as si')
                                ->selectRaw('max(si.created_at)')
                                ->whereRaw('si.model_id = i.model_id AND si.store_id = i.store_id AND si.status=1 ORDER BY si.created_at DESC LIMIT 1 )) as stock ')
                                ->groupBy('stock.model_id');
                          })->get();

这给了我以下错误:-

 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 
(SQL: select sum(stock.total_in_stock) as total_in_stock,stock.name,stock.inventory_id FROM ( SELECT i.store_id,i.model_id,i. total_in_stock,i.id as inventory_id, m.* from `inventory` as `i` left join `model_store` as `ms` on `ms`.`store_id` = `i`.`store_id` left join `model` as `m` on `m`.`id` = `ms`.`model_id` where `i`.`model_id` = m.id and `m`.`status` = 1 and `ms`.`status` = 1 and `i`.`created_at` = (select max(si.created_at) from `inventory` as `si` where si.model_id = i.model_id AND si.store_id = i.store_id AND si.status=1 ORDER BY si.created_at DESC LIMIT 1 )) as stock group by `stock`.`model_id`)) 

我需要应用分页。所以我必须像上面那样编写这个查询。我尝试使用 DB::select() 编写查询。它返回正确的记录，但不幸的是，我不能使用分页。

请帮助我在 Laravel 中编写正确的查询。

Answer 1

尝试让 PDO 实例直接执行查询，如下所示:

    $PDO=DB::connection('mysql')->getPdo();
    $stmt=$PDO->prepare("
        select 
            sum(stock.total_in_stock) as total_in_stock,
            stock.name,
            stock.inventory_id
        FROM
            (SELECT 
                i.store_id,
                    i.model_id,
                    i.total_in_stock,
                    i.id as inventory_id,
                    m . *
            from
                `inventory` as `i`
            left join `model_store` as `ms` ON `ms`.`store_id` = `i`.`store_id`
            left join `model` as `m` ON `m`.`id` = `ms`.`model_id`
            where
                `i`.`model_id` = m.id
                    and `m`.`status` = 1
                    and `ms`.`status` = 1
                    and `i`.`created_at` = (select 
                        max(si.created_at)
                    from
                        `inventory` as `si`
                    where
                        si.model_id = i.model_id
                            AND si.store_id = i.store_id
                            AND si.status = 1
                    ORDER BY si.created_at DESC
                    LIMIT 1)) as stock
        group by `stock`.`model_id`

    ");

    // you can use prepared statments too,
    // you will need to place :status
    // accordingly in your query

   //$stmt->bindParam(':status', 1);


    $stmt->execute();

    $result = $stmt->fetchAll();

祝你好运!