Activerecord Rails 4 perform something like a join that still returns rows that don't have the association

Question

If after reading my question you have a suggestion for a better title, please add a comment. I was having trouble succinctly saying what I wanted. I have a situation like this.

class Artist < ActiveRecord::Base
  has_many :album_artists
  has_many :albums, :through => :album_artists
end
class Album < ActiveRecord::Base
  has_many :album_artists
  has_many :artists, :through => :album_artists
end
class AlbumArist < ActiveRecord::Base
  belongs_to :album
  belongs_to :artist
end

I want a query on artists to return a result if either the artist's name or an album title that the artist is associated with match the query. I can achieve this with a join.

Artist.joins(:albums).where("artists.name like ? or albums.title like ?", query, query).uniq

What I would like to know is how to also return artists whose name matches the query but do not happen to have any albums associated with them. My goal is to do this all in a single query, I would prefer not to perform two sequential queries.

Please ask for more clarification if you need it.

Answer 1

It seems that LEFT OUTER JOIN is what I am looking for. I created scopes in the models like this:

class Artist < ActiveRecord::Base
  has_many :album_artists
  has_many :albums, :through => :album_artists

  scope :joins_albums, -> {joins('LEFT OUTER JOIN "album_artists" ON "album_artists"."artist_id" = "artists"."id" LEFT OUTER JOIN "albums" ON "albums"."id" = "album_artists"."album_id"')}
end

I use the scope in the query:

Artist.joins_albums.where("artists.name like ? or albums.title like ?", query, query).uniq

Now the results include artists whose names match the query even if they do not have any albums associated with them.