CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
Aayush Kothari
Aayush Kothari

Reputation: 504

Rails count of distinct values of a column of associated model in SQL

I have in my Rails (4.1.8) app the following models: Event, User, Box, EventBoxMapping and following associations relevant to the question among them: 

User has_many events; Event belogs_to User
Event has_many boxes through event_box_mappings

I'm trying to achieve faster (and hopefully more memory efficient) CSV generation through ActiveAdmin by using PostgreSQL's COPY functionality to stream output of a raw SQL directly into a CSV export. To achieve this, however, I need to pass a raw SQL string, which I'm having some trouble creating for all bits of information generated to populate columns of our Events CSV. In particular, I'd like to pick out counts of distinct values of box_property_id column of the boxes of events.

Now, so far, I have the following SQL that runs perfectly to maps some values of Event and User models: 

SELECT 
    events.id, 
    events.user_id, 
    events.event_type, 
    events.promo_code, 
    events.created_at, 
    events.transport_fee, 
    events.boxes_count, 
    users.email, 
    users.gender, 
    users.first_name, 
    users.last_name 
FROM events 
LEFT JOIN users ON users.id = events.user_id`

I'm stuck at the part I mentioned above - to include counts of "each kind of box" represented by the box_property_id field in boxes of an Event in the table returned by the above SQL.

I come from a NoSQL background from past experience and fairly new to this field, and so I apologise if my query is ambiguous/incomplete in some form.

Upvotes: 0

Views: 759

Answers (1)

Nitin Srivastava
Nitin Srivastava

Reputation: 1424

As understood, you need you wanna get distinct count of box_property_id with other columns.

Event.joins(:user, event_box_mappings: :box)
     .select("events.id, events.user_id, events.event_type, events.promo_code,
             events.created_at, events.transport_fee, events.boxes_count, 
             users.email, users.gender, users.first_name, users.last_name,
             COUNT(distinct boxes.box_property_id) AS total")

total will return the count of distinct box_property_id.

Hope this would be helpful

Upvotes: 1

Related Questions