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.

score 137 · Accepted Answer · answered Nov 30 '12 at 02:29

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

In modern Laravel you can use `selectRaw` and pass in a string of your SQL selectors like `->selectRaw('issues.*, COUNT(issue_subscriptions.issue_id) as followers')`: https://laravel.com/docs/8.x/queries#selectraw — Connor Leech, Oct 01 '21 at 17:31

Sinan Eldem · Answer 2 · 2013-06-28T09:45:47.407

51

$count = DB::table('category_issue')->count();

will give you the number of items.

For more detailed information check Fluent Query Builder section in beautiful Laravel Documentation.

edited Jun 28 '13 at 09:45

answered Nov 05 '12 at 11:44

Sinan Eldem

5,564
3
36
37

1

ahh yes, I was aware of this. Except, I was hoping to do it in a single query similar to the raw sequel I posted. This approach would require me to loop through my data and count it for each row (each row's count can and will be different). Any other ideas? Thanks for the help! – Alex Naspo Nov 05 '12 at 13:28
1

How do you access count? `$count[0]->count` ? – Dan Aug 01 '14 at 10:45
2

@Silver89 just like this: echo $count. – hkucuk May 03 '16 at 17:46

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

2 Answers2

Linked