How to select count with Laravel's fluent query builder?

Question

Here is my query using fluent query builder.

    $query = DB::table('category_issue')         ->select('issues.*')         ->where('category_id', '=', 1)         ->join('issues', 'category_issue.issue_id', '=', 'issues.id')         ->left_join('issue_subscriptions', 'issues.id', '=', 'issue_subscriptions.issue_id')         ->group_by('issues.id')         ->order_by(DB::raw('COUNT(issue_subscriptions.issue_id)'), 'desc')         ->get(); 

As you can see, I am ordering by a count from the joined table. This is working fine. However, I want this count returned with my selections.

Here is the my raw sequel query that works fine.

Select issues.*, COUNT(issue_subscriptions.issue_id) AS followers  FROM category_issue JOIN Issues ON category_issue.issue_id = issues.id  LEFT JOIN issue_subscriptions ON issues.id = issue_subscriptions.issue_id WHERE category_issue.category_id = 1 GROUP BY issues.id ORDER BY followers DESC 

How would I go about this select using Laravel's fluent query builder? I am aware I can use a raw sql query but I would like to avoid that if possible. Any help would be appreciated, thanks in advance!

Answer 1

You can use an array in the select() to define more columns and you can use the DB::raw() there with aliasing it to followers. Should look like this:

$query = DB::table('category_issue')     ->select(array('issues.*', DB::raw('COUNT(issue_subscriptions.issue_id) as followers')))     ->where('category_id', '=', 1)     ->join('issues', 'category_issue.issue_id', '=', 'issues.id')     ->left_join('issue_subscriptions', 'issues.id', '=', 'issue_subscriptions.issue_id')     ->group_by('issues.id')     ->order_by('followers', 'desc')     ->get();