Eloquent - calculate two field values and order according to result.

I am building a query calling a collection of track to build up a track list. There’s a bunch of relationships that go with this.

A track has a song (one to one)
A track has votes (one to many)
A song has artists (many to many)

I can call all this with

Track::where('tracks.listing_id', $id)
    ->with('song', 'song.artists', 'votes')
    ->get();

I want to order the track by the amount of votes they have. Seems fairly simple BUT a vote can be positive (up vote) or negative (down vote). The votes records have a “vote” field in which the value can be “1” or “-1”. This means I can’t just count the number of records in the votes table for one given track.

So, how can I calculate the up and down votes to get an actual votes count and order my query results on that? Is it even possible

I played around with withCount() which allows me to count only the up-votes or down-votes but I could really use some help here. Maybe what I’m after isn’t even possible.

rsvb

8 years ago

Level 2

SUM(ABS(vote))

matthew_inamdar

8 years ago

Best Answer

Level 4

This should do the trick (not had chance to test it!).

Either via a JOIN:

Track::with('song', 'song.artists', 'votes')
    ->select('tracks.*', DB::raw('SUM(votes.vote) AS vote_count'))
    ->leftJoin('votes', 'tracks.id', '=', 'votes.track_id')
    ->where('tracks.listing_id', $id)
    ->groupBy('tracks.id')
    ->orderBy('vote_count')
    ->get();

Or alternatively, via a SUBQUERY:

Track::with('song', 'song.artists', 'votes')
    ->select('tracks.*', DB::raw('(SELECT SUM(votes.vote) FROM votes WHERE tracks.id = votes.track_id) AS vote_count'))
    ->where('tracks.listing_id', $id)
    ->orderBy('vote_count')
    ->get();

It's worth mentioning, left joins are supposed to be more performant (not sure how much more) but subqueries are arguably more easier to read. Up to you which you choose... as long as it's not the red pill ;)

Kimmer

8 years ago

Level 4

Thanks so very much! Did a quick test and your solution with the the leftJoin seems to work very nicely @matthew_inamdar .

angeleveritt

1 year ago

Level 1

Hey Laracast Community,

Thank you for sharing your experience and the details about your implementation! It's great to see how you're leveraging Eloquent's relationships to handle complex data structures effectively.

Using relationships like hasOne, hasMany, and belongsToMany is indeed a powerful feature of Eloquent, and it’s impressive that you’ve integrated them seamlessly to build a comprehensive track list.

If you have specific challenges, such as calculating two field values or ordering the results dynamically, you might find Eloquent's query builder and accessor methods particularly useful. For example, you can use selectRaw for custom calculations or implement an accessor in your model to simplify calculations.

Should you encounter any obstacles or have further insights to share, please don’t hesitate to discuss them. Your approach might inspire others working on similar projects...just visit the website and explore the world of entertainment... www.magistvhub.com

Great Job, and Happy Coding!!

Please or to participate in this conversation.