CODEWITHSUNDEEP
laraveleloquent
Aristona
Aristona

Reputation: 9361

How can I use join and sum with Eloquent?

I need to select most voted articles on my website. Right now, I have this feature working with minor bugs but I wrote it as a raw SQL query. Bugs are like, I get most voted articles with raw sql query, then use Eloquent's whereIn method with id's, but seems like it returns the response in a random order. Also, it would break Eloquent's ->isEmpty() method which I'm using heavily in my views.

Long story short, I got sick of writing trying to write raw queries and solve numerous bugs (or side effects) on this little script. I want to carry it to Eloquent (with relationships) and be done with it.

My current database schema:

articles
--id

votes
--id
--user
--article_id
--vote
  1. Articles.id and votes.article_id is related with hasMany (An article may have many votes)
  2. vote column may contain 1 and -1 values only.
  3. I need to run a query that selects top 10 most positive voted articles. In raw sql, it is group by votes.article_id order by sum(votes.vote) (We don't select just positive votes, rather SUM of votes.vote column.)

For example:

articles.id
1
2

votes.id      votes.user       votes.article_id      votes_vote
1             User1            1                     1
2             User2            1                     1
3             User3            1                     -1 (this is negative vote)
4             User1            2                     1
5             User2            2                     1

Output should be:

Article 2 has (2) vote rating. // 1 + 1
Article 1 has (1) vote rating  // 1 + 1 - 1

How can I do this?

Ps. You can use Eloquent's DB::raw method if you want, as long as it uses Eloquent.

Upvotes: 2

Views: 3695

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

voila

Article::leftJoin(
    DB::raw('(SELECT article_id, SUM(vote) AS votes FROM votes GROUP BY post_id) as v'),
    'v.post_id', '=', 'posts.id'
)->orderBy('votes', 'desc')->take(10)->get();

Upvotes: 5

Related Questions