Hello everyone!
I've looked at the Sequel docs (Associations and Advanced Associations) and
also at other assorted sources of help on the web but this problem eludes
me.
I'm trying to model the following situation in a standalone Ruby
application using Sequel (both are new to me) –
There are *customers*, who receive *services*. There are various fields
associated with the *service provision*, like actual and estimated costs
and so on. It makes sense to join the *customers *and *services* tables
through *service_provisions* and to keep these values in the
service_provisions table.
My problem is that I would like to access the 'complete' service provision
information from one of my models easily, that is the one that includes
information from both the *services* and *service_provisions* tables.
For example I currently have:
Customer.first.services.first
=> #<Service @values={:id=>4, :service_id=>"window_cleaning",
:service_name=>"Communal Window Cleaning", :display_order=>400,
:category=>"block", :current=>true}>
Customer.first.service_provisions.first
#<ServiceProvision @values={:id=>2, :customer_id=>1, :service_id=>4,
:actual=>10.0, :last_year_estimate=>9.0, :estimate=>11.0}>
And I don't know how to set it up so I could do something like
Customer.first.service_provisions.first.service_name
Customer.first.service_provisions.first.last_year_estimate
or
Customer.first.services.first.service_name
Customer.first.services.first.last_year_estimate
whichever makes more sense (I like the …provision option more).
It is as if I want to join the *Service* and *Service Provision *data
together in some way?
My models are:
class Customer < Sequel::Model
one_to_many :service_provisions
many_to_many :services, :join_table => :service_provisions
end
class Service < Sequel::Model
one_to_many :service_provisions
end
class ServiceProvision < Sequel::Model
many_to_one :customers
many_to_one :services
end
The database structure is:
Sequel.migration do
up do
create_table(:customers) do
primary_key :id
String :account_number, :null => false, :unique => true
String :customer_name
String :flat_number
String :house_number
String :address1
String :address2
String :address3
String :address4
String :post_code
end
create_table(:services) do
primary_key :id
String :service_id, :null => false, :unique => true
String :service_name, :null => false
Integer :display_order, :null => false
String :category, :null => false
Boolean :current, :null => false, :default => true
end
# Foreign keys in SQLite -- http://sqlite.org/foreignkeys.html
create_table(:service_provisions) do
primary_key :id
foreign_key :customer_id, :customers
foreign_key :service_id, :services
Float :actual
Float :last_year_estimate
Float :estimate
end
…
end
Then I load some test data for each model.
*Customers:*
account_number,customer_name, …
978,Small Gruffalo, …
1073,Large Gruffalo, …
2151,Furry Gruffalo, …
*Services:*
service_id,service_name,display_order,category,current
caretaking,Caretaking Service,100,block,true
stairwell_cleaning,Stairwell Cleaning,200,block,true
laundries,Laundries,300,block,true
…
*Service Provisions:*
account_number,service_id,actual,last_year_estimate,estimate
978,window_cleaning,10,9,11
978,bulk_bins,15,16,16
...
Regards,
Ollie
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/2jidoFkRipEJ.
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.
