Thanks Jeremy, it works great now!
On Friday, 30 March 2012 13:53:06 UTC+1, Gruffalo wrote:
>
> 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/-/ttqHlmUQ2CkJ.
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.
