CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
corneljr
corneljr

Reputation: 85

Complex rails SQL query

First of all, a user has many age_demographics. An AgeDemographic object looks like this:

#<AgeDemographic id: 4384, user_id: 799, range: "35 - 49", percentage: 3.2, created_at: "2015-05-22 04:17:10", updated_at: "2015-05-22 04:17:10">

I'm building a user search tool where someone will select multiple age ranges that they want to target ("12 - 17" and "18 - 24" for example). I need to select users that have a collection of age demographic objects with a total percentage greater than 50%.

This is what I've started with:

 User.joins(:age_demographics).where("age_demographics.range IN (?)", ["12 - 17", "18 - 24", "25 - 34"])

But I can't figure out how to tie in the sum of the percentages of those age_demographics into that where clause.

Let me know if this makes absolutely no sense.

Upvotes: 3

Views: 46

Answers (1)

potashin
potashin

Reputation: 44581

You can use having and group methods for this:

User.joins(:age_demographics)
    .where("age_demographics.range IN (?)", ["12 - 17", "18 - 24", "25 - 34"])
    .group("users.id")
    .having("sum(percentage) >= 50")

Upvotes: 2

Related Questions