Trying to use Arel to query and count. For example, if I have posts in a blog that have categories, related through a post_categories could I get all posts with four categories??

@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 (
from posts
inner join post_categories pc on = pc.post_id
inner join categories c on pc.category_id =
group by
having count(*) = 4
select posts.* from posts
inner join category_counts
on =

@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 =
role_t = Role.arel_table

subquery = user_t.
having("count(*) > 2").


Hi @Hooper. I might be able to help. Can you please post a link to the code?

@coloradobum Well, this is all I have at the moment, and it's not close to working:

It's an example from Flatiron - the idea is that there are captains and there are boats and the boats have multiple classifications (like tags). So I'm trying to return all boats wtih exactlty three classifications....

I appreciate any and all help you can give me. The other methods work and fulfill other requirements.

@Hooper I work a lot with rails and ActiveRecord is basically a library that wraps AREL. I've only had to drop down to AREL a few times, but I'll check it out.

So all is working except the `with_three_classifications` method?

@coloradobum Yeah...thuogh I don't have to drop in to arel if it can be done wihtout...I just couldn't figure it out after the others went fast.....and the documentation wasn't helpful - at least what I could find.

Sign in to participate in the conversation

A Mastodon instance for Rubyists & friends