Associations in conditions hash

[RKushnir]

Hi,
I have Photo and Group model. Photo has_and_belongs_to_many :groups.

I need to allow user view photos which do not belong to any group, so I thought I would write can :read, Photo, :groups => {:id => nil}, but it uses joins on association. Therefore, resulting SQL looks like

SELECT * FROM photos INNER JOIN groups_photos INNER JOIN groups

which obviously always returns empty set.

What I actually need is a scope Photo.includes(:groups).where(:groups => {:id => nil}), which produces

photos LEFT OUTER JOIN groups_photos LEFT OUTER JOIN groups

and returns what expected. Is there a clean way to define such ability with cancan?

[ryanb]

You can pass in a scope but you'll need to define the behavior in Ruby as well.

can :read, Photo, Photo.includes(:groups).where(:groups => {:id => nil}) do |photo|
  photo.groups.empty?
end

It may not be the cleanest, but does that work for you?

[RKushnir]

Yes! Thank you.
I had to merge some other conditions manually, as it doesn't allow rule merging now. But it works, and without making unnecessary queries on each page load(as I was trying to have it done).

BTW, are there any reasons preventing from using includes instead of joins in model_adapter? IMHO, with includes it would be more intuitive. Currently, rules with joins unwittingly restrict scope when combined with other rules.

[ryanb]

The includes option does eager loading (loading in the associated record's information) and this is often not necessary if you aren't displaying that information. CanCan's accessible_by isn't intended to do eager loading and one can always add the includes scope if they need eager loading.

It is unfortunate that includes and joins do a different join. This may be considered a bug in Active Record? I would need to do some research.

[ahawkins]

Upvote for outer joins! Very annoying to work around.

[clyfe]

FYI https://gist.github.com/1523940

[the8472]

And a more lightweight hack (which also requires squeel): https://gist.github.com/2312198

For Rails >= 3.1 only another approach is also possible: Generate the query as usual, extract the arel ast for the joins, convert them from inner to outer joins and then merge the joins back in. That wouldn't require squeel.

[derekprior]

Marking this a documentation bug. The workaround Ryan originally offered should be documented with the Scope moved to the class that it queries. I think Ryan's reasoning for not doing includes by default is sound.

[derekprior]

Documentation added (see Hash of Abilities):
https://github.com/ryanb/cancan/wiki/Defining-Abilities

[pcreux]

I ended up monkey patching cancan to run conditions in subqueries instead of building outer joins.

https://gist.github.com/pcreux/6064275