Hi all
We recently started a large project on ruby on rails. This project
contains complex business logic and has a complex data model. We
decided to go with datamapper instead of activerecord, since I like
datamapper's features, especially properties and identity map support.
I am now working on the first query that is a little more complex. The
models look like this (this is a simplified version of our models with
generic model names and only those properties that are of interest
here):
class Container
include DataMapper::Resource
property :id, Serial
property :valid_from, Date, :required => true, :index => true
property :valid_through, Date, :index => true
has n, :sub_containers
end
class SubContainer
include DataMapper::Resource
property :id, Serial
property :valid_from, Date, :index => true
property :valid_through, Date, :index => true
belongs_to :container
has n, :units
end
class SubContainer
include DataMapper::Resource
property :id, Serial
property :valid_from, Date, :index => true
property :valid_through, Date, :index => true
belongs_to :sub_container
end
As you can see, all three classes have properties that describe an
optional data range where the object ist valid. Now, I want to get all
units that are valid within a given date range. So I implemented
scopes on these three classes. valid_though can be nil which means
that the object is valid infinitely. Each object has to respect the
valid_through date range of its parent. Here's the code:
class Container
def self.valid_within(daterange)
all(:valid_from.lte => daterange.last) & (all(:valid_through.gte
=> daterange.first) | all(:valid_through => nil))
end
end
class SubContainer
def self.valid_within(daterange)
(all(:valid_from => nil) | all(:valid_from.lte =>
daterange.last)) &
(all(:valid_through.gte => daterange.first) | all(:valid_through
=> nil)) &
all(:container => Container.valid_through(daterange))
end
end
class SubContainer
def self.valid_within(daterange)
(all(:valid_from => nil) | all(:valid_from.lte =>
daterange.last)) &
(all(:valid_through.gte => daterange.first) | all(:valid_through
=> nil)) &
all(:haus => Haus.valid_through(daterange))
end
end
Now I can query my units like this:
Unit.valid_through(Date.new(2010,1,1)...Date.new(2010,1,31))
This query works, but it generates SQL that hits the database hard. In
real life we will have about 150'000 units, 5'000 subcontainers and
1'000 containers. The SQL I was expecting to see would look like this:
select distinct u.* from units u
inner join sub_containers sc on
u.sub_container_id
= sc.id
inner join containers c on sc.container_id =
c.id
where
c.valid_from <= '20100131'
and(sc.valid_from is null or sc.valid_from >=
'20100131')
and(u.valid_from is null or u.valid_from >=
'20100131')
-- Verwaltungsende darf nicht vor der
Verarbeitungsperiode liegen
and(c.valid_through is null or c.valid_through
>= '20100101')
and(sc.valid_through is null or sc.valid_through
>= '20100101')
and(u.valid_through is null or u.valid_through
>= '20100101')
Instead, datamapper generates a query with unnecessary group by
clauses and sub selects (in) instead of using inner joins. The
datamapper query is way slower than my handwritten SQL statement. I
already found out how to get rid of the group by clauses. And I don't
want to have pure SQL in our code, we want to stay database
independent. So here are my questions, finally:
- Is there a way to set the default for group by's (:unique => ?) to
"false"? I knwo what I am doing and I would like to turn it on only
when I really need it.
- Am I doing this all wrong or is there a way to force datamapper to
use inner joins?
Thank you very much
Gernot Kogler
--
You received this message because you are subscribed to the Google Groups
"DataMapper" 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/datamapper?hl=en.
