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

mysql activerecord fluent laravel eloquent

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!

TLGreg

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'): laravel.com/docs/8.x/queries#selectraw

Sinan Eldem

$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.

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!

How do you access count? $count[0]->count ?

@Silver89 just like this: echo $count.

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US