如何高效地将嵌套的SQL转换为查询构建器

9 浏览
0 Comments

如何高效地将嵌套的SQL转换为查询构建器

我有一个嵌套的SQL查询：

SELECT
   city_id,
   town_id,
   SUM(IF(total_visit >= 2, 1, 0)) visited_twice,
   SUM(IF(total_visit >= 3, 1, 0)) visited_thrice 
FROM
   (
      SELECT
         c.city_id,
         c.town_id,
         c.id,
         COUNT(v.id) AS total_visit 
      FROM
         VISITS v 
         LEFT JOIN
            CUSTOMERS c 
            ON v.customer_id = c.id 
      WHERE
         c.customer_type = 1 
         AND MONTH(v.visit_date) = 6 
         AND YEAR(v.visit_date) = 2021 
      GROUP BY
         c.town_id,
         c.id,
         MONTH(v.visit_date),
         YEAR(v.visit_date) 
      HAVING
         total_visit > 1
   )
GROUP BY
   town_id

如何将其转换为查询构建器模式，使代码更可读？

我尝试将普通查询转换为查询构建器，但是对于嵌套查询，我正在寻求建议。

编辑：

$visitTable = Visit::$TABLE_NAME;
$customerTable = Customer::$TABLE_NAME;
$sub = Visit::with($with)
    ->selectRaw("$customerTable.city_id, $customerTable.town_id, 
                     $customerTable.id, COUNT($visitTable.id) total_visit")
    ->leftJoin("$customerTable", "$customerTable.id", '=', "$visitTable.customer_id")
    ->where("$customerTable.customer_type_id", 1)
    ->whereMonth("$visitTable.visit_date", $month)
    ->whereYear("$visitTable.visit_date", $year)
    ->groupBy("$customerTable.town_id, MONTH($visitTable.visit_date), YEAR($visitTable.visit_date)")
    ->havingRaw('total_visit > 1');
$query = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->selectRaw("city_id, town_id,
                SUM(IF(total_visit >= 2, 1, 0)) visited_twice, SUM(IF(total_visit >= 3, 1, 0)) visited_thrice ")
    ->mergeBindings($sub->getQuery())
    ->groupBy("town_id");

但最后得到的结果是：

"connection": {},

"grammar": {},

"processor": {},

"bindings": {

"select": [],

"join": [],

"where": [

1,

"6",

"2021"

],

"having": [],

"order": [],

"union": []

},

"aggregate": null,

"columns": [

{}

],

0

1 答案

0 Comments

问题的原因是在使用Query builder时，将嵌套的SQL转换为Query builder的语法时出现了错误。解决方法是使用正确的语法来查询子查询表，可以使用以下任意一种语法选项：

1. `DB::table(Closure, alias)`

2. `DB::table(Builder, alias)`

3. `DB::query()->from(Closure, alias)`

4. `DB::query()->from(Builder, alias)`

在给出的示例中，使用了第二种选项。首先构建子查询（不获取结果），然后将其作为表来使用。然后可以对该表进行进一步的查询操作。

如果想要强制结果为特定的模型（Visit模型），需要使用`->query()->from(...)`语法。

在问题中，用户在使用这些查询构建器时遇到了错误消息"Object of class Illuminate\Database\Query\Builder could not be converted to string"。这个错误是因为将Query builder对象转换为字符串时出现了问题。

用户在使用存储库模式和存储库级别的API时遇到了这个错误，在Postman上测试API时出现了问题。

为了解决这个问题，用户可以尝试使用`mergeBindings`方法来解决。但是，由于具体情况未知，无法确定问题的具体解决方案。

用户提到这个问题可能与Laravel的版本有关，因此解决方法可能取决于Laravel的版本。

文章结束。

0