On 14 мар, 19:09, Jeremy Evans <[email protected]> wrote:
> On Wednesday, March 14, 2012 3:48:53 AM UTC-7, kavkaz wrote:
>
> > 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)
>
> From reading the module RDoc, I would expect this to do what you want:
>
> ds_artist.to_json(:include=>{:albums=>{:include=>:tracks}})
>
> The specs have similar code, so I'm pretty sure this works.
>
> Thanks,
> Jeremy
Thank you to serialize it works.
Missed it because the focus on getting such a structure is for the
dataset (and the program to work).
To the following code worked without any extra queries:
els = Artist.all
els.each do |art|
puts art.name
art.albums.each do |alb|
puts alb.label
alb.tracks.each do |tr|
puts tr.length
end
end
end
By analogy with the serialization, it was necessary to do so:
els = Artist.eager_graph(:albums => :tracks).all
It worked perfectly
SELECT `artists`.`id`, `artists`.`name`, `albums`.`id` AS
`albums_id`, `albums`.`label`, `tracks`.`id` AS `tracks_id`,
`tracks`.`length` FROM `artists` LEFT OUTER JOIN `albums_artists` ON
(`albums_artists`.`artist_id` = `artists`.`id`) LEFT OUTER JOIN
`albums` ON (`albums`.`id` = `albums_artists`.`album_id`) LEFT OUTER
JOIN `albums_tracks` ON (`albums_tracks`.`album_id` = `albums`.`id`)
LEFT OUTER JOIN `tracks` ON (`tracks`.`id` =
`albums_tracks`.`track_id`)
Thank you.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
