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.

TLGreg · Accepted Answer · 2012-11-30 02:29:30Z

142

+50

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

answered Nov 30, 2012 at 2:29

TLGreg

8,5193 gold badges26 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Connor Leech Over a year ago

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 · Accepted Answer · 2013-06-28 09:45:47Z

57

$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, 2013 at 9:45

answered Nov 5, 2012 at 11:44

Sinan Eldem

5,7663 gold badges40 silver badges38 bronze badges

3 Comments

Alex Naspo Over a year ago

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!

Dan Over a year ago

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

hkucuk Over a year ago

@Silver89 just like this: echo $count.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related