return a comments_spam_count property in this query

I am following along with Andre Madarang in Build a Voting App. I would like to add a property that returns like comments_count toward the bottom called comments_spam_count.

There is a table comments with a field spam_reports that holds the number of times the comment has been reported as spam. I would like to return the number of spam reports per idea. I for the life of me have not been able to figure this out.

Comments table has field idea_id that is a foreign key to table idea field id.

return view('livewire.ideas-index', [
    'ideas' => Idea::with('user', 'category', 'status')
        ->when($this->status && $this->status !== 'All', function ($query) use ($statuses) {
            return $query->where('status_id', $statuses->get($this->status));
        })
        ->when($this->category && $this->category !== 'All Categories',
            function ($query) use ($categories) {
                return $query->where('category_id', $categories->pluck('id', 'name')->get($this->category));
            })
        ->when($this->filter === 'Top Voted', function ($query) {
            return $query->orderBy('votes_count', 'DESC');
        })
        ->when($this->filter === 'My Ideas', function ($query) {
            return $query->where('user_id', Auth::id());
        })
        ->when($this->filter === 'Spam Ideas', function ($query) {
            return $query->where('spam_reports', '>', 0)->orderByDesc('spam_reports');
        })
        ->when($this->filter === 'Spam Comments', function ($query) {
            return $query->whereHas('comments', function ($query) {
                $query->where('spam_reports', '>', 0);
            })->orderByDesc('spam_reports');
        })
        ->when($this->search && strlen($this->search) >= 3, function ($query) {
            return $query->where('title', 'like', '%'.$this->search.'%');
            //->orWhere('description', 'like', '%' . $this->search . '%');
        })
        ->addSelect([
            'voted_by_user' => Vote::select('id')
                ->where('user_id', Auth::id())
                ->whereColumn('idea_id', 'ideas.id')
        ])
        ->withCount('votes') // creates 'votes_count' attribute
        ->withCount('comments') // creates 'comments_count' attribute
        ->orderBy('id', 'desc')
        ->paginate()
        ->withQueryString(),
    'categories' => $categories->pluck('name'),
]);

LaryAI

1 year ago

Best Answer

Level 58

To add a comments_spam_count property to your query, you can use the withCount method with a condition. This will allow you to count only the comments that have been reported as spam for each idea. Here's how you can modify your query to include this:

return view('livewire.ideas-index', [
    'ideas' => Idea::with('user', 'category', 'status')
        ->when($this->status && $this->status !== 'All', function ($query) use ($statuses) {
            return $query->where('status_id', $statuses->get($this->status));
        })
        ->when($this->category && $this->category !== 'All Categories', function ($query) use ($categories) {
            return $query->where('category_id', $categories->pluck('id', 'name')->get($this->category));
        })
        ->when($this->filter === 'Top Voted', function ($query) {
            return $query->orderBy('votes_count', 'DESC');
        })
        ->when($this->filter === 'My Ideas', function ($query) {
            return $query->where('user_id', Auth::id());
        })
        ->when($this->filter === 'Spam Ideas', function ($query) {
            return $query->where('spam_reports', '>', 0)->orderByDesc('spam_reports');
        })
        ->when($this->filter === 'Spam Comments', function ($query) {
            return $query->whereHas('comments', function ($query) {
                $query->where('spam_reports', '>', 0);
            })->orderByDesc('spam_reports');
        })
        ->when($this->search && strlen($this->search) >= 3, function ($query) {
            return $query->where('title', 'like', '%'.$this->search.'%');
        })
        ->addSelect([
            'voted_by_user' => Vote::select('id')
                ->where('user_id', Auth::id())
                ->whereColumn('idea_id', 'ideas.id')
        ])
        ->withCount('votes') // creates 'votes_count' attribute
        ->withCount('comments') // creates 'comments_count' attribute
        ->withCount(['comments as comments_spam_count' => function ($query) {
            $query->where('spam_reports', '>', 0);
        }]) // creates 'comments_spam_count' attribute
        ->orderBy('id', 'desc')
        ->paginate()
        ->withQueryString(),
    'categories' => $categories->pluck('name'),
]);

In this solution, the withCount method is used with a closure to count only the comments where spam_reports is greater than zero. This adds a comments_spam_count attribute to each Idea model in the result set, which represents the number of spam reports for comments associated with that idea.

Please or to participate in this conversation.