Get the complicated structure (a three-level hierarchy)

[kavkaz]

Get the complicated structure (a three-level hierarchy)

We have the following structure

artists
  :id   <--,
  :name    |
           |   albums_artists
           `---> artist_id
           ,---> album_id
           |
albums     |
  :id  <---|
  :label   |
           |   albums_tracks
           `---> album_id
           ,---> track_id
tracks     |
  :id  <---`
  :length


One artist, two albums. Each of the album on two tracks.
Need to get json with the following structure.

[
  {
    "json_class": "Artist",
    "id": 1,
    "name": "Bob",
    "albums": [
      {
        "json_class": "Album",
        "id": 1,
        "label": "Summer",
        "tracks": [
          {
            "json_class": "Track",
            "id": 1,
            "length": 10
          },
          {
            "json_class": "Track",
            "id": 2,
            "length": 20
          }
        ]
      },
      {
        "json_class": "Album",
        "id": 2,
        "label": "Winter",
        "tracks": [
          {
            "json_class": "Track",
            "id": 3,
            "length": 30
          },
          {
            "json_class": "Track",
            "id": 4,
            "length": 40
          }
        ]
      }
    ]
  }
]

How to get artists with albums or albums with tracks - it is clear (as
shown in the script).
You can tell by what it is to implement without making some "seleсt",
if possible, or at least with them.


### script.rb

#!/usr/bin/env ruby

require 'rubygems'
require 'mysql2'
require 'sequel'
require 'logger'
require 'pp'


DB = Sequel.connect( "mysql2://root@localhost/test_3" )
DB.loggers << Logger.new($stdout)

DB << "drop database test_3"
DB << "create database test_3"
DB << "use test_3"



DB.create_table :artists do
  primary_key :id
  column :name, String
end


DB.create_table :albums do
  primary_key :id
  column :label, String
end


DB.create_table :tracks do
  primary_key :id
  column :length, :integer
end


DB.create_table :albums_artists do
  foreign_key :album_id, :albums
  foreign_key :artist_id, :artists
end


DB.create_table :albums_tracks do
  foreign_key :album_id, :albums
  foreign_key :track_id, :tracks
end


Sequel::Model.plugin :json_serializer

class Artist < Sequel::Model
  many_to_many :albums
end


class Album < Sequel::Model
  many_to_many :artists
  many_to_many :tracks
end


class Track < Sequel::Model
  many_to_many :albums
end


artist = Artist.create(:name => 'Bob')

album1 = Album.create(:label => 'Summer')
album2 = Album.create(:label => 'Winter')

artist.add_album(album1)
artist.add_album(album2)

track1 = Track.create(:length => 10)
track2 = Track.create(:length => 20)
track3 = Track.create(:length => 30)
track4 = Track.create(:length => 40)

album1.add_track(track1)
album1.add_track(track2)
album2.add_track(track3)
album2.add_track(track4)


ds_artist = Artist.eager_graph(:albums)
puts ds_artist.to_json(:include => :albums)

ds_albums = Album.eager_graph(:tracks)
puts ds_albums.to_json(:include => :tracks)

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To post to this group, send email to sequel-talk@googlegroups.com.
To unsubscribe from this group, send email to 
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sequel-talk?hl=en.