How to generate OR conditions in a scope

[Ginty]

Hi All,

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.

I can get the results I want from either:

@items = current_office.items
@items << current_company.items.shared

or using a single scope on the Item class:

Item.for_current_office_and_shared

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.

Many thanks!

[Colin Law]

2010/1/21 Ginty <stephen....@gmail.com>:

> Hi All,
>
> 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.

Colin

>
> I can get the results I want from either:
>
> @items = current_office.items
> @items << current_company.items.shared
>
> or using a single scope on the Item class:
>
> Item.for_current_office_and_shared
>
> 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.
>
> Many thanks!
>

> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
>
>

[Ginty]

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.

On Jan 21, 7:36 am, Colin Law <clan...@googlemail.com> wrote:
> 2010/1/21 Ginty <stephen.f.mcgi...@gmail.com>:

[Colin Law]

2010/1/21 Ginty <stephen....@gmail.com>:

> 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.

Colin

[Aleksey Gureiev]

Hi Ginty,

I think it's as simple as this:

@items = Item.all(:conditions => [ "office_id = ? OR (company_id = ?
AND shared = 1)", current_office.id, current_company.id ])

- Aleksey

[Ginty]

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.

Cheers!

[Ginty]

Except it doesn't bloody work at all!

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

[blog.noizeramp.com/2009/12/30/our-toyota/]

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

After this, you could do this kind of queries:

@items = Item.shared_by(current_office).all

or

@items = Item.shared_by(current_office).all(:limit => 5, :conditions
=> { :visible => true })

or... you get the idea.

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.

- Aleksey

[Ginty]

Hi Aleksey,

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!

current_office.items
current_office.items_including_shared

looks nicer and is more intuitive than...

current_office.items
Item.shared_by(current_office).all

I don't think concerns about using method_missing are valid at this
stage in the game, that's how all rails' dynamic finders work after
all.

Thanks again!

On Jan 22, 5:19 am, "blog.noizeramp.com/2009/12/30/our-toyota/"

[Marnen Laibow-Koser]

Ginty wrote:
> Hi Aleksey,
>
> 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!
>
> current_office.items
> current_office.items_including_shared

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.

>
> looks nicer and is more intuitive than...
>
> current_office.items
> Item.shared_by(current_office).all

But the latter is more intention-revealing, and will be clearer to read.

>
> I don't think concerns about using method_missing are valid at this
> stage in the game,

Probably true.

> that's how all rails' dynamic finders work after
> all.

Sort of. Rails' dynamic finders actually memoize the method so
method_missing is only called the first time around.

>
> Thanks again!
>
> On Jan 22, 5:19�am, "blog.noizeramp.com/2009/12/30/our-toyota/"

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
mar...@marnen.org
--
Posted via http://www.ruby-forum.com/.