Bryan HooperTrying to use Arel to query and count. For example, if I have posts in a blog that have categories, related through a post_categories table...how could I get all posts with four categories?? #help
Matt Griffin 
@Hooper posts with any of the four categories or only posts with all four?
@Hooper or all posts that have any categories totaling a quantity of four?
@betamatt The last one - all posts that have a quantiy of four categories - so I have to count the quanity on the related table....make sense?
@Hooper so this sql:
with category_counts as (
select posts.id
from posts
inner join post_categories pc on posts.id = pc.post_id
inner join categories c on pc.category_id = c.id
group by posts.id
having count(*) = 4
)
select posts.* from posts
inner join category_counts
on category_counts.id = posts.id
with category_counts as (
select posts.id
from posts
inner join post_categories pc on posts.id = pc.post_id
inner join categories c on pc.category_id = c.id
group by posts.id
having count(*) = 4
)
select posts.* from posts
inner join category_counts
on category_counts.id = posts.id
@betamatt I'll give that a try - thanks. Wish I could just whip this stuff up like that!
@Hooper Here it is in arel. My version doesn't support WITH so I did a subquery. (Models are users/roles but the relationship is the same.)
user_t = User.arel_table
user_role_t = Arel::Table.new(:roles_users)
role_t = Role.arel_table
subquery = user_t.
join(user_role_t).on(user_role_t[:user_id].eq(user_t[:id])).
join(role_t).on(user_role_t[:role_id].eq(role_t[:id])).
group(user_t[:id]).
having("count(*) > 2").
project(user_t[:id])
User.where(user_t[:id].in(subquery))