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

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!

210771

小开

最佳答案

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