CODEWITHSUNDEEP
phpmysqllaraveleloquent
Ludwig
Ludwig

Reputation: 1811

Laravel - getting the count from two pivot tables as one number

I have a table with articles and two pivot tables views and votes for articles that have columns article_id and user_id. For articles I have a column publish where I check if the article is published. I need to make a query by popularity, where I get all the published articles with their count from both pivot tables, where each vote from the table votes counts as 3, and each count from the table views counts as 1. Not sure how to do this, I previously had a query where I was getting published articles with their count of only votes, but I was only getting the articles that had some votes, and not the ones that were published but had no votes. And in the new query I need to add views as well, and change the logic for counting as well as including the articles that are published and not views and order them by their count from these two pivot tables.

So, the wanted output would something like this:

[{"id":117,"popularityCount":17},{"id":118,"popularityCount":15},{"id":121,"popularityCount":13}]

This was the query, but that is not what I need anymore:

$articles = DB::table('articles')
        ->select([DB::raw('articles.id'), DB::raw('COUNT(*) as "votes"')])
        ->join('votes', 'article_id', '=', 'articles.id')
        ->where('articles.publish', 1)
        ->groupBy('votes.article_id')
        ->orderBy('votes', 'desc')
        ->get();

        return $articles;

Upvotes: 1

Views: 131

Answers (1)

Ludwig
Ludwig

Reputation: 1811

This is what worked for me in the end if anyone will need it in the future!

$result = Article::select('articles.*', 'v.vote_count', 'vw.view_count')
            ->where('publish', 1)
            ->leftJoin(DB::raw('(SELECT votes.article_id, COUNT(*) AS vote_count FROM votes GROUP BY votes.article_id) v'), 'v.article_id', '=', 'articles.id')
            ->leftJoin(DB::raw('(SELECT views.article_id, COUNT(*) AS view_count FROM views GROUP BY views.article_id) vw'), 'vw.article_id', '=', 'articles.id')
            ->orderByRaw('(v.vote_count * 3) + vw.view_count DESC')
            ->where('created_at', '>', $date->format('Y-m-d') . ' 00:00:00')
            ->get();

Upvotes: 0

Related Questions