Count database column text values - Laravel

Question

So, I have a database table that looks something like this:

id | title | category | author | post | created_at | updated_at

The category column is a text value, so it currently has values such as 'Tech', 'Web', and 'Animal'. What I need to do, is to count each category value in the category column and have an output such as:

Tech (2)    
Animal (2)   
Web (1)

Now, I do kind of have it working. It currently looks like:

Tech (1) (1)    
Animal (1) (1)    
Web (1)

The query in my controller is:

$categoryCount = Post::select(DB::raw('count(*) as cat_count, category'))
                       ->groupBy('category')
                       ->count();

My view currently looks like:

@if(count($category) > 0)
    @foreach($category as $cat)
        <a href="{{ URL('category/'.str_replace(' ', '-', $cat->name)) }}" 
                class="list-group-item">{{ $cat->name }}
                    @foreach($posts as $post)
                        @if($post->category == $cat->name)
                            ({{ $categoryCount }})
                        @endif
                    @endforeach
        </a>
    @endforeach
@endif

I am quite new to Laravel itself, so I'm still learning. Any help to achieve what I want would be appreciated.

Chilion · Accepted Answer · 2016-06-03 14:15:55Z

1

You do a DB raw count and then again a count. It will indeed then return always one. Change your query:

$categoryCount = Post::select(DB::raw('count(*) as cat_count, category'))
                       ->groupBy('category')
                       ->get();

And your results should be different. This has by the way nothing to do with Laravel, merely that you count the count :-)

answered Jun 3, 2016 at 14:15

Chilion

4,5304 gold badges36 silver badges48 bronze badges

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

1 Comment

Jon Over a year ago

I did originally have that as my query! I had to make a slight adjustment to my view, but now it works how it's intended to. Thank you for your help :)

Collectives™ on Stack Overflow

Count database column text values - Laravel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related