I see that named scopes can be used to inject additional AND
conditions into the WHERE component of a query, however is there any
way to inject an OR condition?
Say for example I have a Company and Office model, the Company
has_many Offices and both the Company and Office own many child
models.
The Office should be able to see the child models that he specifically
owns, but also those owned by his parent company that have been
designated to be shared throughout the company.
So what I really want to do is something like:
@items = current_office.items.including_shared
where the including_shared scope would have to inject a condition like
'OR (company_id = x AND shared = 1)' into the query.
However I can't see a way to do this.
However I don't like that the first one requires two db calls, and for
the second one I prefer to start all fetches with either
current_company/office so that I can easily tell that they are
properly scoped to the current application instance.
Anyway I'm probably being a bit anal, but just wondered if anyone has
any ideas.
I see that named scopes can be used to inject additional AND
conditions into the WHERE component of a query, however is there any
way to inject an OR condition?
Say for example I have a Company and Office model, the Company
has_many Offices and both the Company and Office own many child
models.
The Office should be able to see the child models that he specifically
owns, but also those owned by his parent company that have been
designated to be shared throughout the company.
So what I really want to do is something like:
@items = current_office.items.including_shared
where the including_shared scope would have to inject a condition like
'OR (company_id = x AND shared = 1)' into the query.
However I can't see a way to do this.
Just put the OR in the :conditions parameter of the named scope just
as you would for a normal find with OR conditions.
Thanks for the reply, however I don't think that will work.
I already have the current_office in the scope and so it will generate
something like:
SELECT * WHERE (office_id = x) *AND* (company_id = x AND shared = 1)
By specifying the :conditions in the named scope I can stick any OR I
want in the second half of the conditions, however it is the *AND* in
the middle that is still the problem and I want to turn it into an OR.
Thanks for the reply, however I don't think that will work.
I already have the current_office in the scope and so it will generate
something like:
SELECT * WHERE (office_id = x) *AND* (company_id = x AND shared = 1)
By specifying the :conditions in the named scope I can stick any OR I
want in the second half of the conditions, however it is the *AND* in
the middle that is still the problem and I want to turn it into an OR.
I think I understand now, you mean you want to chain named scopes such
that the second one *adds* additional items into the first one. So if
you have
@items = scope_1
then
@items = scope_1.scope_2
will have more items than the first statement.
I don't think that is possible, when chaining scopes I believe that
the results of one is effectively passed to the next so it can only
filter some out, not add more. I may be wrong though.
You could do it by providing a method of Office called, for example
items_including_shared that runs the two queries and combines them,
then you can say
@items = current_office.items_including_shared
which is pretty close to what you wanted.
Thanks guys, that got me thinking about it in a different way.
In reality I have a lot of models with this type of ownership and I
ended up going with this in the office model:
# This implements things like:
current_office.holidays_including_shared
def method_missing(method_id)
if method_id.to_s =~ /(.+)_including_shared/
$1.singularize.camelize.constantize.all(:conditions =>
["office_id = ? OR (company_id = ? AND shared = true)", self.id,
self.company_id])
else
super
end
end
So this gets me exactly what I wanted with my controller call down to
a clean single line where it is easy to see the scope and the single
DB query.
It worked like a dream in my unit test but failed when attempted in a
controller.
It seems that within the controller environment rails does some
trickery to implement the has_xxxx relationships, and while
current_office.class tells me it is an Office, it is actually an
ActiveRecord AssociationProxy, sneaky.
So if anyone is ever interested in implementing something similar then
note the following...
The AssociationProxy gives the target model (the Office in this case)
the first attempt at fulfilling a method call by checking if it has
the method via respond_to?
In this case the Office does not have a method called
'items_including_shared' and so it returns false and then the call
falls through to the AssociationProxy#method_missing which has no idea
what to do with it.
So to make this work you need to override the respond_to? method to
account for the fact that the Office model can actually handle these
additional methods that are not explicitly declared.
Here is my final code:
# This implements things like
current_office.holidays_including_shared
def method_missing(method_id, *arguments)
if item = method_contains_including_shared(method_id)
item.singularize.camelize.constantize.all(:conditions =>
["office_id = ? OR (company_id = ? AND shared = true)",
self.id, self.company_id])
else
super
end
end
# Returns true for any explicitly declared methods or anything
ending in _including_shared
def respond_to?(method)
!!method_contains_including_shared(method) || super
end
protected
def method_contains_including_shared(method)
method.to_s =~ /(.+)_including_shared/ ? $1 : false
end
I'm sure you are overcomplicating matters. Let me give you another
idea, and see if you like it.
The missing_method is known to be relatively slow and I wouldn't rely
on it in a high traffic situation. What you are trying to do can
usually be done by adding a custom finder to the Item class, or if you
foresee more parameters to be added, by using a named scope. So
instead of doing "_including_shared" magic, you could simply do this
with named scopes:
class Item < ActiveRecord::Base
named_scope :shared_by, lambda { |office| { :conditions =>
[ "office_id = ? OR (company_id = ? AND shared = true)", office.id,
office.company_id] }}
end
If you want to reuse the named_scope definition across multiple
models, you can move it into a separate module and include / extend it
in your models to stay DRY.
I think it just comes down to preference really and I prefer the idea
of a having a common rule that I start all finders with current_office
and then I instantly know that I have safely scoped it to the current
application instance.
Of course you way would work to, I just think that mine looks nicer!
I think it just comes down to preference really and I prefer the idea
of a having a common rule that I start all finders with current_office
and then I instantly know that I have safely scoped it to the current
application instance.
Of course you way would work to, I just think that mine looks nicer!
But if named_scopes could do this, then starting with current_office
would be meaningless because the records would no longer be scoped to
current_office. I think you're trying to implement a Bad Idea.
