Laravel sql command to Laravel QueryBuilder

本文关键字：Laravel QueryBuilder to command sql | 更新日期: 2023-09-27

我正试图将此SQl查询转换为QueryBuilder，但我不能这样做。

SELECT a.id, a.category_name, cat.Count
FROM `categories` a
LEFT OUTER JOIN (
SELECT `categories`.`category_parent` , COUNT(*) AS Count
    FROM `categories`
    GROUP BY category_parent
    )
cat ON a.id = cat.category_parent
WHERE a.category_parent = 1

例如:

DB::table('users')
->join('contacts',DB::raw("a.id = cat.category_parent"),function($query){
   $query->select(DB::raw("`categories`.`category_parent` , COUNT( * ) AS Count"))
                  ->from('contacts')
                  ->groupBy('category_parent')
->where(DB::raw("a.category_parent = 1"))
->get();

如何在laravel中修复此方法。由于

尝试让PDO实例直接执行您的查询:

    $PDO=DB::connection('mysql')->getPdo();
    $stmt=$PDO->prepare("
      SELECT a.id, a.category_name, cat.Count
      FROM `categories` a
      LEFT OUTER JOIN (
      SELECT `categories`.`category_parent` , COUNT(*) AS Count
          FROM `categories`
          GROUP BY category_parent
          )
      cat ON a.id = cat.category_parent
      WHERE a.category_parent = :category_parent
    ");
    $stmt->bindParam(':category_parent', 1);

    $stmt->execute();
    $result = $stmt->fetchAll();

我在laravel 4上运行的一个项目中有一个这样的查询。

祝你好运!