Multi-Table Sorting

Question

I'd like to be able to sort on a field that is in a linked table, Artist Name then by Release Title when viewing the albums in a category.

Database:

In the Categories table, artists are linked by ID

In the Category#Show, I have @releases = Shelf.joins(:artists).find(@shelf.shelf_discogs_id).releases.order('release_title ASC').page(params[:page])

The above code works to sort by the Release Title, however, I can’t find a way to make it sort by the Artist Name, which is part of the Artists table.

The joins(:artists) does add the correct line in the console to link correctly (from what I can tell)

SELECT  "shelves".*
FROM "shelves"
  INNER JOIN "releases"
    ON "releases"."shelf_id" = "shelves"."shelf_discogs_id"
  INNER JOIN "artists"
    ON "artists"."artist_discogs_id" = "releases"."artist_id"
WHERE "shelves"."shelf_discogs_id" = $1
LIMIT $2

Model for Releases

class Release < ApplicationRecord

  belongs_to :shelf
  belongs_to :artist
  belongs_to :record_label
  belongs_to :record_location
  has_many :tracks

end

Model for Artists

class Artist < ApplicationRecord

  has_many :releases
  has_many :tracks
  has_many :shelves, through: :releases

end

Any ideas how I can achieve what I’m looking to do? Thanks

Answer 1

You can do it specifying the name column is in the artists table:

Shelf
  .find(@shelf.shelf_discogs_id)
  .releases
  .joins(:artist)
  .order('releases.release_title ASC, artists.artist_name ASC')
  .page(params[:page])