Follow

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 table...how 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 (
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))

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:

gist.github.com/bthooper/82d08

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

A Mastodon instance for Rubyists & friends