Retrieving nested records in Sequel

Question

I'm trying to retrieve data in a nested form from the following two tables (in SQLite)

DB = Sequel.sqlite('database.sqlite')

DB.create_table? :artists do
    primary_key     :id
    String          :name
end

DB.create_table? :albums do
    primary_key     :id
    String          :title
    foreign_key     :artist_id,
                    :artists,
                    :key => :id
end

artists = DB[:artists]
albums = DB[:albums]

id1 = artists.insert(:name => 'Mike')
id2 = artists.insert(:name => 'John')

albums.insert(:title => 'Only You', :artist_id => id1 )
albums.insert(:title => 'Only Us', :artist_id => id1 )
albums.insert(:title => 'Only Me', :artist_id => id2 )

The output I'm trying to get -

[
  {
    :id => 1,
    :name => 'Mike'
    :albums => [
      {
        :id => 1,
        :title => 'Only You'
      },
      {
        :id => 2,
        :title => 'Only Us'
      }
    ]
  },
  {
    :id => 2,
    :name => 'John'
    :albums => [
      {
        :id => 3,
        :title => 'Only Me'
      }
    ]
  }
]

I've tried 'eager' loading -

class Artist < Sequel::Model(:artists)
  one_to_many  :db[:albums], :key => :artist_id
end

class Album < Sequel::Model(:albums)
  many_to_one  :artist, :key => :artist_id
end


Artist.eager(:albums).all{ |a| p a }

But that didn't work.

Can anyone point me in the right direction?

Answer 1

Artist.eager(:albums).all does eagerly load the albums, but {|a| p a} is not going to show the albums (as Sequel::Model#inspect only shows values for the current model, not any associated objects). Use {|a| p [a, a.albums]} to see that the albums are already loaded.

If you want to produce the hash you described:

Artist.eager(:albums).all.map do |a|
  a.values.merge(:albums=>a.albums.map{|al| al.values})
end

Answer 2

You can add a method to Artist to output it the way you want it

class Artist < Sequel::Model(:artists)
  one_to_many  :albums, :key => :artist_id

  def my_hash
    to_hash.merge!(
        {
            :albums => albums.map{|a| 
                a.to_hash.reject!{ |k,v| 
                    k==:artist_id
                }
            }
        }
    )
  end
end

class Album < Sequel::Model(:albums)
  many_to_one  :artist, :key => :artist_id
end

records = Artist.all.map{ |a| a.my_hash }
p records

Instead of using reject! it would be cleaner to add a my_hash method the Album to return a hash without the :artist_id, but you get the idea. This outputs:

[
  {
    :albums=>[
      {
        :title=>"Only You", 
        :id=>1
      }, 
      {
        :title=>"Only Us", 
        :id=>2
      }
    ],
    :name=>"Mike",
    :id=>1
  },
  {
    :albums=>[
      {
        :title=>"Only Me", 
        :id=>3
      }
    ], 
    :name=>"John", 
    :id=>2
  }
]