CODEWITHSUNDEEP
phpmysqllaravel
Jon
Jon

Reputation: 295

Count database column text values - Laravel

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.

Upvotes: 0

Views: 396

Answers (1)

Chilion
Chilion

Reputation: 4500

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 :-)

Upvotes: 1

Related Questions