CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordrails-activerecord
Rajdeep Singh
Rajdeep Singh

Reputation: 17834

Fetch records from a table based on the condition in associated table

I have two models School and Expense where school has many expenses, Expense has an attribute named cost

I'm trying to fetch only those schools with their expense cost greater than a certain value, let's say 1000, I tried this query

School.includes(:expenses).where("lower(schools.name) LIKE '%a%' and expenses.cost >= 1000").select('*').from('schools, expenses')

The problem with this query is its returning duplicate values if a school have multiple expenses with cost greater than 1000, what I want is only unique schools based on the condition.

Please help!

Upvotes: 0

Views: 57

Answers (1)

SHS
SHS

Reputation: 7744

School.
joins(:expenses).
where("lower(schools.name) LIKE ?", "%a%"). # part of the code you shared
where("expenses.cost > ?", 1_000).
group("schools.id")

Upvotes: 1

Related Questions