On 19 fév, 19:32, Jeremy Evans <[email protected]> wrote:
> On Feb 19, 10:05 am, Simon Arnaud <[email protected]> wrote:
>
>
>
> > Hi
>
> > I may be missing something, but I can't find out how to create a
> > subset with conditions on an associated model.
>
> > Let's take a simple example, with people who owns shoes.
>
> > ### subset.rb ###
> > require 'sequel'
> > require 'logger'
>
> > DB = Sequel.sqlite
>
> > DB.create_table! :people do
> > primary_key :id
> > String :name
> > end
>
> > DB.create_table! :shoes do
> > primary_key :id
> > String :color
> > Integer :person_id
> > end
>
> > class Person < Sequel::Model
> > one_to_many :shoes
> > subset(:with_red_shoes) do |p|
> > p.shoes.color == 'red'
> > end
> > end
>
> > class Shoe < Sequel::Model
> > many_to_one :person
> > end
>
> > p Person.with_red_shoes
>
> > ### subset.rb ###
>
> > $ ruby -rubygems -Ku subset.rb
> > subset.rb:20: undefined method `color' for #<Sequel::SQL::Identifier:
> > 0x7f8a4da07f88 @value=:shoes> (NoMethodError)
> > from /var/lib/gems/1.8/gems/sequel-2.10.0/lib/sequel_core/dataset/
> > sql.rb:803:in `call'
> > from /var/lib/gems/1.8/gems/sequel-2.10.0/lib/sequel_core/dataset/
> > sql.rb:803:in `filter_expr'
> > from /var/lib/gems/1.8/gems/sequel-2.10.0/lib/sequel_core/dataset/
> > sql.rb:180:in `filter'
> > from /var/lib/gems/1.8/gems/sequel-2.10.0/lib/sequel_model/base.rb:
> > 416:in `with_red_shoes'
> > from /var/lib/gems/1.8/gems/sequel-2.10.0/lib/sequel_model/base.rb:
> > 156:in `with_red_shoes'
> > from subset.rb:28
>
> > I've tried different strategies, but to no avail
> > def_dataset_method
> > def self.with_red_shoes
> > one_to_many :with_red_shoes, :class => :Shoe, :graph_condition =>
> > { :color => 'red' }
>
> > Is there a way to do it ?
>
> You almost had it. Because you are filtering based on criteria in an
> associated table, you need to use eager_graph, and
> the :graph_join_type has to be inner to exclude people that don't have
> red shoes.
>
> class Person < Sequel::Model
> one_to_many :shoes
>
> one_to_many :red_shoes, :class=>:Shoe, :graph_join_type=>:inner, :conditions=>
> {:color=>'red'}
> def_dataset_method(:with_red_shoes){eager_graph(:red_shoes)}
> end
>
> The above code requires the master branch, if you are using 2.10.0,
> you need to change :conditions to :graph_conditions.
>
> Jeremy
Thanks for your reply, it works great.
Now, let's get a bit more involved. Those shoes are created in a
country.
So we have a country table.
Now I would like to do things like :
1) Shoe.from_france
2) Person.with_shoes_from_france
3) Person.with_red_shoes.from_france
The first one is easy, I'm just wondering if the :graph_join_type is
also :inner, or if it should :outer, :natural, or whatever. I'm not an
expert in SQL, moreso in the different types of JOINs.
The second one seems tricky, and surely can't be done with only a
one_to_many, or many_to_one. It looks like a many_to_many, and using a
mix of the :graph_* and :eager_* methods. I looked at the
has_many :through example, but I can't figure out how to apply it on
this example.
The third one looks like it should work, but since the first method
returns a Dataset, and not a Shoe, it does not. And I don't see how
it could be handled.
Now, I'm wondering if I'm taking the good approach on this. I'm not
interested in the associated objects, I just want to filter the Person
based on associated objects, and it seems quite complicated, when it's
quite simple in SQL.
Is there any simpler way to tackle this ?
Something that would look like :
Person.filter( :shoes => { :color => 'red' }).filter( :shoes =>
{ :country => { :name => 'france' } } )
regards
Simon
and here is my code
require 'sequel'
require 'logger'
DB = Sequel.sqlite
DB.create_table! :people do
primary_key :id
String :name
end
DB.create_table! :shoes do
primary_key :id
String :color
Integer :person_id
Integer :country_id
end
DB.create_table! :countries do
primary_key :id
String :name
end
class Person < Sequel::Model
one_to_many :shoes
one_to_many :red_shoes, :class => :Shoe, :graph_join_type
=> :inner, :graph_conditions => { :color => 'red' }
# many_to_many :shoes_from_france, :class => :Country, :join_table
=> :shoes,
def_dataset_method(:with_red_shoes) { eager_graph(:red_shoes) }
def_dataset_method(:with_shoes_from_france) { eager_graph
(:shoes_from_france) }
end
class Country < Sequel::Model
one_to_many :shoes
end
class Shoe < Sequel::Model
many_to_one :person
many_to_one :country
many_to_one :france, :class => :Country, :graph_join_type
=> :inner, :graph_conditions => { :country => 'france' }
def_dataset_method(:from_france) { eager_graph(:france) }
end
p Person.with_red_shoes
p Shoe.from_france
p Person.with_red_shoes.from_france
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
