Some "simple" questions

[criegod]

My tables were created with:

DB.create_table :artists do
primary_key :id
String :name, null: false, unique: true
end

DB.create_table :albums do
primary_key :id
foreign_key :artist_id, :artists, null: false
varchar :name, null: false
varchar :path
Time :bdate
Integer :year
end

And my models are (very simple):

class Album < Sequel::Model
# many_to_one :artists, key: :artist_id
many_to_one :artists
def to_s
"#{bdate.strftime("%Y-%m-%d %H:%M")}: #{artist_name}/#{name} [#{year}]"
end
end

class Artist < Sequel::Model
# one_to_many :albums, key: :name
one_to_many :albums, primary_key: :id
end

The first (miscellaneous) question is wrt the Artist model: the commented out line was to remind me that maybe the "name" column should be a key. I thought this might make lookup on this column faster so, not a bad thing. The syntax seems wrong to me, though. And if i wanted it to be a key, i would think that should be specified in the create_table method. Is it valid to put something like this in the model? Secondly, the uncommented line specifies 'id' as the primary key. I think i copied this from somewhere but, again, is this valid? My code works ok with that there but i'm wondering if it's completely superfluous and, if not, what it does? Thirdly, after i've added all the albums to the album table, i want to check to see if there are any artists that don't have any albums (if i deleted the album from my hard drive). What i had previously (data_mapper) was:

emptyArtists = Artist.all(:albums => nil)

This gives the error:
wrong number of arguments (given 1, expected 0) (ArgumentError)

I changed it to:

emptyArtists = Artist.where(:albums => nil).all

but this gives the same error. I cannot think of how to ask for all the artists where there are no albums with that artist_id. In other words (perhaps) where Artist.albums.count == 0. But how to get those artists?

[jeremyevans]

Here's a self contained example showing how to do what you want:

DB.create_table :artists do
  primary_key :id
  String :name, null: false, unique: true
end

DB.create_table :albums do
  primary_key :id
  foreign_key :artist_id, :artists, null: false
  varchar :name, null: false
  varchar :path
  Time :bdate
  Integer :year
end

class Album < Sequel::Model
  many_to_one :artist

  def to_s
    "#{bdate.strftime("%Y-%m-%d %H:%M")}: #{artist_name}/#{name} [#{year}]"
  end
end

class Artist < Sequel::Model
  one_to_many :albums
end

Artist.create(name: 'A').add_album(name: 'C')
Artist.create(name: 'B')
Artist.exclude(:albums=>Album.dataset).all
# => [#<Artist @values={:id=>2, :name=>"B"}>]

[criegod]

Thanks so much for that. I solved my other issues (many_to_one :artist instead of :artists was not a typo in the documentation) and i needed to specify the 'key' part in:

many_to_one :artist, key: :artist_id

to get certain association lookups to work. But i was working on this one with no success. When i saw the answer... i could barely wrap my head around it (and still). I would never have come with it on my own. So thanks, again. I have 2 of my 3 main programs fully ported now. I should start a new discussion, but right now i'm looking for a Sequel implementation of postgresql's 'similar to' comparison feature.

[jeremyevans]

Sequel currently doesn't have native support for SIMILAR TO. It supports LIKE and regular expressions: https://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html#label-String+Operators+-28-7C-7C-2C+LIKE-2C+Regexp-29

You'll need to use something similar to Sequel.lit('? SIMILAR TO ?', :column, 'pattern') for SIMILAR TO.