Laravel sql命令到Laravel QueryBuilder

i'm trying to convert this SQl query to QueryBuilder but i can't do it.

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

for example:

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();

how to fix this method in laravel. thanks

Try getting the PDO instance to execute your query directly:

    $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();

I have a query like that in one of my projects running on laravel 4.

Good Luck!